VBA Another problem

[Kenja824]

I have this piece of code. It adds a column at the end of the table and places a formula into it. The formulas are functions we created in the code file. They simply extract certain info from the text string. The problem I am having, is there are two different columns this info could be in and if its not in the one column it needs to extract it from the second column. The formula not commented out (in red) below works if I enter it into the cell manually, but for some reason it gets an error when I run my file.





With ActiveSheet
If Not bHasListObject Then
'make active sheet table a Structured Table
' .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "Table" & iCnt + 1
With .Cells(1, 1).End(xlToRight)
'put formula in a new table column to the far right
.Offset(0, 1).Value = "RO#"
' .Offset(2, 1).Formula = "=if(XtractR01([@Number])="",XtractR01([@[Rob '#]]),XtractR01([@Number]))"
.Offset(2, 1).Formula = "=IF(XtractR01([@Number])="",XtractR01([@[ROB '#]]),XtractR01([@Number]))"
End With
End If
End With


I will check tomorrow for answers. Thanks

 

[SkipVought]

Ken,

'
    With ActiveSheet
        If Not bHasListObject Then
            'make active sheet table a Structured Table
            ' .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "Table" & iCnt + 1
            With .Cells(1, 1).End(xlToRight)
                'put formula in a new table column to the far right
                .Offset(0, 1).Value = "RO#"
                
                .Offset(1, 1).Formula = _
                "=IF(XtractR01[@Number]="""",XtractR01[@ROB '#],XtractR01[@Number])"
            End With
        End If
    End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

Hey Skip

For some reason I seemt o keep getting an error on this code too. It hi-lights this portion in yellow...

.Offset(1, 1).Formula = _
"=IF(XtractR01[@Number]="""",XtractR01[@ROB '#],XtractR01[@Number])"

And when I hite Debug, it gives a......

Run-Time Error '1004':
Application-defined or object-definded error.

If I hit reset and run it again, it runs through the entire code but never adds the column header R0# and does not place any codes in the cells. It also does not continue to add the next column with code in it either.

However, if I change the formula line to ......

.Offset(2, 1).Formula = "=XtractR01([@Number])"

It will not error out and it will add code and new columns in. It just wont look in the secondary column if the needed info is not available in the primary column.

I cant place the real file on here due to GM privacy. Would it help if I sent it to your email?

 

[SkipVought]

The devil is in the detail of the XtractR01 table headers. I suspect ROB #.

You can eMsil. I’ll look when I get home.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

Ha, I just got it! lol I tried a mixture of my original and the code you gave me and it worked.
In your code, you added the extra """" where I only had "". But the original code had parenthesis around portions of the formulas that you removed. So I left the original code and just added the extra """" to it and it ran correctly.

Thank you once again for the help. I do so much appreciate it.

PS
yes I played with ROB # quite a bit in the past trying to figure out if that was the problem. lol

 

[SkipVought]

Glad you got the formula to be error free.

It seems the order of the rows in the XtractR01 table and Tablen[/n] that you’re building, need to be of the same size and sort order and are related. Is this the case?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}