VBA - object required

[p4nny]

Hi

Please could you help, I'm getting an "object required error" on this following code:

Would appreciate any guidance. Thank you

Sub PopulateColumns()




  Dim ws As Worksheet
  Dim inputmonth As String
  
  inputmonth = InputBox("Enter CRA Reporting Month")
     
    For Each ws In ActiveWorkbook.Sheets
        ws.Activate
    
    ws.Range("k6") = "BIM Area"
    ws.Range("K7:k14") = Application.WorksheetFunction.VLookup(ActiveSheet.Name, Lookup.Range("a$1$:B$200$"), 2, 0)
    ws.Range("L6") = "Date"
    ws.Range("l7:l14") = inputmonth
    Next ws


End Sub

 

[Joe4]

What exactly is the "Lookup" referenced here?

ws.Range("K7:k14") = Application.WorksheetFunction.VLookup(ActiveSheet.Name, [COLOR=#ff0000][B]Lookup[/B][/COLOR].Range("a$1$:B$200$"), 2, 0)

It looks like it is set-up to be an object (maybe a worksheet object), but I do not see it being defined anywhere.

 

[p4nny]

Hi Joe
Lookup is a sheet within the workbook itself.

Thank you

 

[Joe4]

OK, but you are not using it that way in your code. Currently, it is an unassigned object.
You either need to define a worksheet object and set it equal to "LOOKUP" to use it like that, or you need to do something like:

Sheets("LOOKUP").[COLOR=#333333]Range("A$1$:B$200$")[/COLOR]

 

[Fluff]

You have the $ signs in the wrong place. If "Lookup" is the sheet codename try

Lookup.Range("$a$1:$B$200")