Last row issues

Question

I'm having some issues with last row expression and autofill. I'm not sure what I'm missing here as it looks correct but it doesn't seem to be starting the vlookup in the proper cell (N2, it starts it in N1) and it won't autofill to the last row of M. Any push in the right direction would be greatly appreciated. I'm thinking it's something small I'm overlooking.

 Sub Nightly()
 '
 ' Nightly Macro
 '
Dim PackSpec As Workbook
Dim FullBook As Workbook
Dim DebFile As Workbook
Dim lrow As Long

lrow = Cells(Rows.Count, 1).End(xlUp).Row


'Open the nightly pack spec file, cut and insert the year row into column D
Set PackSpec = Workbooks.Open("S:\Accounting\Apps\Packspec\CIDExport\Archive\" & Year(Date) & "\" & Month(Date) & "\" & Day(Date) - 1 & "\*.csv")
Columns("A:A").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select

'Open Fullbook master and insert columns after N then VLookup between Pack Spec and Fullbook
Set FullBook = Workbooks.Open("S:\Corporate\Groups\Comosoft\Downloads\FullBook\fullbook_Master.csv")
Columns("N:U").Select
Selection.Insert Shift:=xlToRight
Range("N2").Select

'Actvate Fullbook and enter Vlookup for dates
Windows("fullbook_Master.csv").Activate
With ActiveSheet.Range("N2")
.FormulaR1C1 = "=VLOOKUP(RC[-1],'[15.50.1.CID.csv]15.50.1.CID'!C[-13]:C[-11],3,0)"
.AutoFill Destination:=Range("N2:N" & lrow) 'issue not autofilling to end
Windows("fullBook_Master.csv").Activate
End With

 End Sub

Put a break in the code and see what the value of `lrow` is, My guess is that since you are specifically stating the parent sheet that where it is looking Column A is empty and it returning `1` to `lrow`. — Scott Craner, Nov 02 '18 at 18:10
okay that helped. It's trying to pull the last row from a different spreadsheet when it's doing the autofill. Would it be better to define a lrow for each of the spreadsheets my code is using or should is there maybe an easier way to get it to work where it is using the active workbook? — Deke, Nov 02 '18 at 18:21
Is there a reason you're using `Activate` and `ActiveWorkbook`/`ActiveSheet` instead of qualified references? As far as I can see, the Workbook that's Activated ("fullbook_Master") already has a reference to it `FullBook` which could be used along with either a sheet's name or codename to full qualify the ranges; i.e. `FullBook.Worksheet("SheetName").Range("N2")`. (As for the lrow variable, I would just redefine it for each sheet it's used on) — Mistella, Nov 02 '18 at 19:39
Inexperience would probably be my best explanation as to why i'm using activate and active workbook/worksheet. I'm self taught and still learning. I'll see about setting up the lrow I'll give it a shot with separate references to each of the workbooks and see how that turns out. Thanks for the suggestions! — Deke, Nov 02 '18 at 19:45
Just a reference for avoiding `Activate` and `Select` https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Marcucciboy2, Nov 02 '18 at 20:40

score 2 · Accepted Answer · answered Nov 04 '18 at 13:11

2

Just move the

lrow = Cells(Rows.Count, 1).End(xlUp).Row

after

Windows("fullbook_Master.csv").Activate

answered Nov 04 '18 at 13:11

usmanhaq

1,527
1
6
11

Lambik · Answer 2 · 2018-11-05T18:53:57.107

You should:

Link lrow to a sheet to avoid mistakes. Avoid all the selects.

 Sub Nightly()
 '
 ' Nightly Macro
 '
Dim PackSpec As Workbook
Dim FullBook As Workbook
Dim DebFile As Workbook
Dim lrow As Long
Dim ws As Worksheet, wsPackSpec As Worksheet

Set ws = Activesheet
'Or set ws = Sheets("Sheet1") - better


'Open the nightly pack spec file, cut and insert the year row into column D
Set PackSpec = Workbooks.Open("S:\Accounting\Apps\Packspec\CIDExport\Archive\" & 
Year(Date) & "\" & Month(Date) & "\" & Day(Date) - 1 & "\*.csv")
Set wsPackSpec = PackSpec.Sheets(1)
wsPackSpec.Columns(1).Value = wsPackSpec.Columns(4).Value
wsPackSpec.Columns(1).EntireColumn.Delete

'Open Fullbook master and insert columns after N then VLookup between Pack Spec and Fullbook
'Apply same principle as above here
Set FullBook = Workbooks.Open("S:\Corporate\Groups\Comosoft\Downloads\FullBook\fullbook_Master.csv")
Columns("N:U").Select
Selection.Insert Shift:=xlToRight
Range("N2").Select

'Actvate Fullbook and enter Vlookup for dates
Windows("fullbook_Master.csv").Activate
Set ws = ActiveSheet 'Set like this, but you should designate the correct worksheet in the fullbook part above
lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

With ws
    .Cells(2, 14).FormulaR1C1 =   "=VLOOKUP(RC[-1],'[15.50.1.CID.csv]15.50.1.CID'!C[-13]:C[-11],3,0)"
    .Cells(2, 14).AutoFill Destination:=.Range(.Cells(2, 14), .Cells(lrow, 14))
End With    

 End Sub

Something like this, couldn't test in detail because I'm missing the overview of how the books are set up.

I've tried to use your code, it's got the same issue but I only plugged it in. I'll start going through it though and see if I can't make it work and report back. Thanks so much for your help and suggestions!!!!! — Deke, Nov 05 '18 at 15:40
correct, lrow was in the wrong place, corrected in the edited answer, sorry 'bout that — Lambik, Nov 05 '18 at 18:54

Last row issues

2 Answers2