Set Last Row Error as Object Required

[JohnA7]

Set Last Row Error as Object Required


Hi all. I have this down at work and now I'm on my home laptop and it's not working.

Laptop is using Excel 2016. I'm trying to set the last row starting from A7 (there's random stuff that changes between A1-A6, but code starts at A7), worksheet name is "State Info".

I'm getting Compile Error: Object Required on "LRowStateInfo = StateInfo.Range(StateInfo.Rows.Count,"A").End(xlUp).Row"

There also has to be an easier way to do the piece inside the With no? Maybe a for each or do loop?
For color, "Data Table" is going to be a large table, where states can be added as rows, and information can be added as columns. "State Info" is a page where you type in the state and get all the information in the data table.

I'm really sorry if this has been addressed or answered before. I've searched for 2 hours now and have tried a bunch of different fixes, but no luck. I wouldn't open a new thread if I wasn't completely stumped. I'm probably missing something stupid. I have something exactly like this on my work computer (Excel 2010) and it works just fine.

Sub KUDT_StateInfo()


Dim Wkbk As Workbook
Dim DataTable As Worksheet
Dim StateInfo As Worksheet
Dim FinalPayRules As Worksheet
Dim LRowStateInfo As Long
Dim LColStateInfo As Long
Dim LRowDataTable As Long
Dim LColDataTable As Long
Dim LRowFinalPay As Long
Dim LColFinalPay As Long




Set Wkbk = ActiveWorkbook
Set DataTable = Wkbk.Sheets("Data Table")
Set StateInfo = Wkbk.Sheets("State Info")
Set FinalPayRules = Wkbk.Sheets("Final Pay Rules")
Set LRowStateInfo = StateInfo.Range(StateInfo.Rows.Count, "A").End(xlUp).Row




With StateInfo
'Fills Row Labels
    Range("A7").Value = "Topic"
    Range("A8").Value = DataTable.Range("B1")
    Range("A9").Value = DataTable.Range("C1")
    Range("A10").Value = DataTable.Range("D1")
    Range("A11").Value = DataTable.Range("E1")
    Range("A12").Value = DataTable.Range("F1")
    Range("A13").Value = DataTable.Range("G1")
    Range("A14").Value = DataTable.Range("H1")
    Range("A15").Value = DataTable.Range("I1")
    Range("A16").Value = DataTable.Range("J1")
    Range("A17").Value = DataTable.Range("K1")
    Range("A18").Value = DataTable.Range("L1")
    Range("A19").Value = DataTable.Range("M1")
    Range("A20").Value = DataTable.Range("N1")
    Range("A21").Value = DataTable.Range("O1")
    Range("A22").Value = DataTable.Range("P1")
    
'Lookup Result based on State
    Range("B8").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,2,0)"
    Range("B9").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,3,0)"
    Range("b10").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,4,0)"
    Range("b11").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,5,0)"
    Range("b12").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,6,0)"
    Range("b13").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,7,0)"
    Range("b14").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,8,0)"
    Range("b15").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,9,0)"
    Range("b16").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,10,0)"
    Range("b17").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,11,0)"
    Range("b18").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,12,0)"
    Range("b19").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,13,0)"
    Range("b20").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,14,0)"
    Range("b21").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,15,0)"
    Range("b22").Formula = "=VLOOKUP(B3,'Data Table'!A:Z,16,0)"
    
    Range("A7:A" & LRowStateInfo).ColumnWidth.AutoFit
    
End With
End Sub

 

[mikerickson]

LRowStateInfo is data type Long. Its not an object, so the Set keyword should not be used. Change the line to

LRowStateInfo = StateInfo.Range(StateInfo.Rows.Count, "A").End(xlUp).Row

 

[JohnA7]

Thank you. Tried that but then it draws error down to when I try to autofit columns.

Now I'm getting "Object Required" error here.. I thought my object was "With State Info". Maybe I don't understand this stuff as well as I thought.

removing the code in between:

Dim Wkbk As Workbook
Dim DataTable As Worksheet
Dim StateInfo As Worksheet
Dim FinalPayRules As Worksheet
Dim LRowStateInfo As Long\

Set Wkbk = ActiveWorkbook
Set DataTable = Wkbk.Sheets("Data Table")
Set StateInfo = Wkbk.Sheets("State Info")
Set FinalPayRules = Wkbk.Sheets("Final Pay Rules")
LRowStateInfo = StateInfo.Range(StateInfo.Rows.Count, "A").End(xlUp).Row


With StateInfo
 Range("A7:A" & LRowStateInfo).Columns.ColumnWidth.AutoFit

End With
End Sub

 

[JohnA7]

Got it sorry, long day. Thank you!

Sub KUDT_StateInfo()


Dim Wkbk As Workbook
Dim DataTable As Worksheet
Dim StateInfo As Worksheet
Dim FinalPayRules As Worksheet
Dim LRowStateInfo As Long
Dim LColStateInfo As Long
Dim LRowDataTable As Long
Dim LColDataTable As Long
Dim LRowFinalPay As Long
Dim LColFinalPay As Long

Set Wkbk = ActiveWorkbook
Set DataTable = Wkbk.Sheets("Data Table")
Set StateInfo = Wkbk.Sheets("State Info")
Set FinalPayRules = Wkbk.Sheets("Final Pay Rules")
LRowStateInfo = StateInfo.Cells(Rows.Count, 1).End(xlUp).Row


With StateInfo
 Range("A7:A" & LRowStateInfo).Columns.AutoFit
End With

End Sub