VBA for Loop for Creating Tables on Mutiple Sheet partially works

[smd747]

After I import multiply files to different sheets. I import anywhere from 4 to 20 sheets
I run this loop to create formatted tables
The loop works great to format tables but Columns H & I text numbers only get converted on the last sheet even though I added it in the loop.


Here is my loop

Sub CreateAllSheetTables()


Dim lo As ListObject
Dim lRow As Long
Dim vTable As Variant


 On Error GoTo CreateAllSheetTables_Error
  
' Loop to create Tables on all sheets Sheet Name is Table Name
 For lSht = 2 To ActiveWorkbook.Worksheets.Count
        With Worksheets(lSht)


            .ListObjects.Add(xlSrcRange, .Range("$A$1").CurrentRegion, , xlYes).Name = "tbl" & .Name
'            .ListColumns("extncost").DataBodyRange(lRow).Value = FormulaR1C1 = " =IF(ISNUMBER(SEARCH(""BOM"",A2)),0,H2*G2)" does not work
        End With
      
'****************************************************************************************************
'        Convert columns from text to numbers      481.400000 to $481.40
'        This works only on last sheet table


    Columns("H:H").Select
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.Style = "Currency"
    Range("H1").Select



    Columns("I:I").Select
    Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.Style = "Currency"
    Range("I1").Select
 Next lSht
   Exit Sub


   On Error GoTo 0
CreateAllSheetTables_Error:


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateAllSheetTables of Sub m_ImportReportGenerator"


End Sub

This line also does nothing

.ListColumns("extncost").DataBodyRange(lRow).Value = FormulaR1C1 = " =IF(ISNUMBER(SEARCH(""BOM"",A2)),0,H2*G2)" does not work

Any direction would be appreciated, thanks

 

[JoeMo]

On the line that does nothing, try changing the first part of that line to this:

.ListColumns("extncost").DataBodyRange(lRow).Formula = .......

 

[smd747]

Joemo

I get a error 438 (object doesn't support this property or method) ny using .formula

 

[JoeMo]

Joemo

I get a error 438 (object doesn't support this property or method) ny using .formula

I think the syntax is not right. Are you trying to put the formula in the cell at the intersection of the column with header "extncost" and row lRow?

 

[smd747]

JoeMo. [/CODE]

I used your example and looped when I import the data before I loop to create the tables. I am thinking it was before the text to number conversion was the problem.

here is the loop that works:

Sub FormulaLoop() 
Dim FinalRow As Long
Dim i As Long
 
   On Error GoTo FormulaLoop_Error
 
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
 
For i = 2 To FinalRow
 
Cells(i, 9).Value = "=IF(ISNUMBER(SEARCH(""BOM"",RC[-8])),0,RC[-1]*RC[-2])"
 
Next i
 
   On Error GoTo 0
   Exit Sub
 
FormulaLoop_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FormulaLoop of Sub Module1"
 
End Sub

Thanks for the quick response and your direction, much appreciated