VBA Run Time Error 424 Object Required

[drew27c]

I have am having trouble with this line in my macro below.

Range("J2:J" & LastRow).Value = "=IFERROR(VLOOKUP(H2," & LCase(sh.Name) & "_rate,3,FALSE),"""")"

The code is supposed to change the named range in the formula based on the sheet name. I can't figure out why I am getting this error.

Sub macro()
    Dim sh As Variant
    For Each sh In Array("vr1", "vr2", "vr3", "ht1", "ht2", "ht3")
        Sheets(sh).Columns.AutoFit
        Sheets(sh).Columns.HorizontalAlignment = xlLeft
        Sheets(sh).Columns("L:M").ColumnWidth = 10
        Sheets(sh).Columns("N:P").ColumnWidth = 4
        Sheets(sh).Columns("R:T").ColumnWidth = 30
        Sheets(sh).Columns("H:I").HorizontalAlignment = xlCenter
        Sheets(sh).Columns("N:P").HorizontalAlignment = xlCenter
        Dim LastRow As Long
        LastRow = Range("B" & Rows.Count).End(xlUp).Row 'find last row in dataset
        Range("N2:N" & LastRow).Value = "=IFERROR(VLOOKUP(B2,cam_pivot,2,FALSE),"""")" 
        Range("O2:O" & LastRow).Value = "=IFERROR(VLOOKUP(B2,auto_pivot,2,FALSE),"""")" 
        Range("P2:P" & LastRow).Value = "=IFERROR(VLOOKUP(B2,per_pivot,2,FALSE),"""")" 
        Range("J2:J" & LastRow).Value = "=IFERROR(VLOOKUP(H2," & LCase(sh.Name) & "_rate,3,FALSE),"""")"
        Range("K2:K" & LastRow).Value = "=PRODUCT(I2,J2)" 'tally rate * qty
        Next sh
        Worksheets("VR1").Activate
        Range("A2").Select
End Sub

 

[Fluff]

Try removing the .Name so that it's just LCase(sh), although as all the values in the array are lowercase you could get rid of the LCase as well

 

[drew27c]

Try removing the .Name so that it's just LCase(sh), although as all the values in the array are lowercase you could get rid of the LCase as well

OK! so that removes the error, but I am getting the wrong range in the formula per sheet, or no formula at all.

 

[Fluff]

Do you have named ranges such as vr1_rate?

 

[drew27c]

Do you have named ranges such as vr1_rate?

yep
vr1_rate
vr2_rate
vr3_rate
ht1_rate
ht2_rate
ht3_rate

 

[Fluff]

Assuming that you are trying to put the formula onto all those sheets try

Sub macro()
   Dim sh As Variant
   For Each sh In Array("vr1", "vr2", "vr3", "ht1", "ht2", "ht3")
      With Sheets(sh)
         .Columns.AutoFit
         .Columns.HorizontalAlignment = xlLeft
         .Columns("L:M").ColumnWidth = 10
         .Columns("N:P").ColumnWidth = 4
         .Columns("R:T").ColumnWidth = 30
         .Columns("H:I").HorizontalAlignment = xlCenter
         .Columns("N:P").HorizontalAlignment = xlCenter
         Dim LastRow As Long
         LastRow = .Range("B" & Rows.Count).End(xlUp).Row 'find last row in dataset
         .Range("N2:N" & LastRow).Value = "=IFERROR(VLOOKUP(B2,cam_pivot,2,FALSE),"""")"
         .Range("O2:O" & LastRow).Value = "=IFERROR(VLOOKUP(B2,auto_pivot,2,FALSE),"""")"
         .Range("P2:P" & LastRow).Value = "=IFERROR(VLOOKUP(B2,per_pivot,2,FALSE),"""")"
         .Range("J2:J" & LastRow).Value = "=IFERROR(VLOOKUP(H2," & LCase(sh.Name) & "_rate,3,FALSE),"""")"
         .Range("K2:K" & LastRow).Value = "=PRODUCT(I2,J2)" 'tally rate * qty
      End With
   Next sh
   Worksheets("VR1").Activate
   Range("A2").Select
End Sub

 

[drew27c]

I have am having trouble with this line in my macro below.

Range("J2:J" & LastRow).Value = "=IFERROR(VLOOKUP(H2," & LCase(sh.Name) & "_rate,3,FALSE),"""")"

The code is supposed to change the named range in the formula based on the sheet name. I can't figure out why I am getting this error.

Sub macro()
    Dim sh As Variant
    For Each sh In Array("vr1", "vr2", "vr3", "ht1", "ht2", "ht3")
        Sheets(sh).Columns.AutoFit
        Sheets(sh).Columns.HorizontalAlignment = xlLeft
        Sheets(sh).Columns("L:M").ColumnWidth = 10
        Sheets(sh).Columns("N:P").ColumnWidth = 4
        Sheets(sh).Columns("R:T").ColumnWidth = 30
        Sheets(sh).Columns("H:I").HorizontalAlignment = xlCenter
        Sheets(sh).Columns("N:P").HorizontalAlignment = xlCenter
        Dim LastRow As Long
        LastRow = Range("B" & Rows.Count).End(xlUp).Row 'find last row in dataset
        Range("N2:N" & LastRow).Value = "=IFERROR(VLOOKUP(B2,cam_pivot,2,FALSE),"""")" 
        Range("O2:O" & LastRow).Value = "=IFERROR(VLOOKUP(B2,auto_pivot,2,FALSE),"""")" 
        Range("P2:P" & LastRow).Value = "=IFERROR(VLOOKUP(B2,per_pivot,2,FALSE),"""")" 
        Range("J2:J" & LastRow).Value = "=IFERROR(VLOOKUP(H2," & LCase(sh.Name) & "_rate,3,FALSE),"""")"
        Range("K2:K" & LastRow).Value = "=PRODUCT(I2,J2)" 'tally rate * qty
        Next sh
        Worksheets("VR1").Activate
        Range("A2").Select
End Sub

It worked perfectly once I removed .name (all my ranges and sheets are lowercase now).

Thank you so very much.

 

[Fluff]

You're welcome & thanks for the feedback