VBA Another problem
VBA Another problem
(OP)
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
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





RE: VBA Another problem
CODE
' 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 WithSkip,
for a NUance!
RE: VBA Another problem
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?
RE: VBA Another problem
You can eMsil. I’ll look when I get home.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Another problem
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
RE: VBA Another problem
It seems the order of the rows in the XtractR01 table and Table[i]n[/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!