How to Find Next Row In Excel

[Robert Wyatt]

O.K. this might be a long shot, but I trying to put together a year to date where It will load the information from my check stub to the year to date in excel I have Time sheet, Paystub, and Year To Date in three different sheets. I have a formula in VBA that will place everything from my paystub to the year to date page, but I can seem to get it to find the next empty row. I'm past the formula here so yawl can see what I'm talking about. It's the only formula I'm having trouble with I have one of the same thing for my Paid Time Off Register and it works just fine. I would have put the whole thing here, but not sure how to do it with XL2BB.

Sub POSTTOYEARTODATE()
Dim WS1 As Worksheet
Dim WS13 As Worksheet
Dim WS14 As Worksheet

Set WS1 = Worksheets("TimeSheet")
Set WS13 = Worksheets("PayStub")
Set WS14 = Worksheets("YearToDate")

' Figure out which row is the next row
NextRow = WS14.Cells(Rows.Count, 1).End(x1Up).Row + 1


' Copy formats\formulas from Row above
If NextColumn > 2 Then
WS14.Rows(NextRow - 1).AutoFill Destination:=WS14.Rows(NextRow - 1).Resize(2)
End If

' Write the important values to Register
WS14.Cells(NextRow, 1).Resize(1, 29).Value = Array(WS14.Range("D15"), WS14.Range("D16"), _
WS14.Range("D17"), WS14.Range("D18"), WS14.Range("D19"), WS14.Range("D20"), WS14.Range("D21"), _
WS14.Range("D22"), WS14.Range("D26"), WS14.Range("D27"), WS14.Range("D28"), WS14.Range("D32"), _
WS14.Range("D33"), WS14.Range("D34"), WS14.Range("D35"), WS14.Range("D39"), WS14.Range("D40"), _
WS14.Range("D41"), WS14.Range("D45"), WS14.Range("D46"), WS14.Range("D47"), WS14.Range("D48"), _
WS14.Range("D49"), WS14.Range("D50"), WS14.Range("D51"), WS14.Range("D52"))
End

 

[6StringJazzer]

The constant is xlUp, not x1Up. (lowercase letter l, not numeral 1)

' Figure out which row is the next row
NextRow = WS14.Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[Robert Wyatt]

The constant is xlUp, not x1Up. (lowercase letter l, not numeral 1)

' Figure out which row is the next row
NextRow = WS14.Cells(Rows.Count, 1).End(xlUp).Row + 1

Every time I try to type the l it still comes up as 1 but only in the VBA not anywhere else. I can type l normally here or word but not in Excel. Why?

 

[6StringJazzer]

The default font for VBA code makes it look like a "1". (Personally I have changed mine to a different font.) If you type an "l", it is an "l".

 

[Robert Wyatt]

Every time I try to type the l it still comes up as 1 but only in the VBA not anywhere else. I can type l normally here or word but not in Excel. Why?

The default font for VBA code makes it look like a "1". (Personally I have changed mine to a different font.) If you type an "l", it is an "l".

How do I change default font in VBA ?

 

[MARK858]

 

[Robert Wyatt]

If I wanted to post my file so you could see everything up to the VBA how would I do that? even after changing the font I'm still having trouble with it.

View attachment 85264

 

[MARK858]

Sorry, I'm not understanding your question.
If you mean how do you post all your code, then you just just copy it, paste it in the thread, select the code you have pasted, click the

icon at the top of the reply window and click Post reply.

If you mean something else then post back

 

[Robert Wyatt]

Sorry, I'm not understanding your question.
If you mean how do you post all your code, then you just just copy it, paste it in the thread, select the code you have pasted, click the View attachment 85267 icon at the top of the reply window and click Post reply.

If you mean something else then post back

Posting just the VBA code wouldn't show you the year to date sheet I need to post all of it so you can see everything I have made up to see where I have gone wrong in writing the formula.

 

[Alex Blakenburg]

Where in your code are assigning a value to NextColumn ?