VBA next row

[Cityroller]

I'm very new to VBA. Coming here is the next step, as I've been working on this problem for a few days. Agonizingly, I had something very similar working before, it works on the other worksheet(sort of) and I don't know how to get back to where it was working.

I've looked around the web at different code examples. I need a macro to get to the next empty row, and have found many.

The first problem began last week when it started adding spaces between the rows, from double-spaced to quadruple, all with the same macro.

Public Sub rowFinder(txt1 As Integer, txt2 As Double, txt3 As String, oB1 As String, oB2 As String)


    Dim ws As Worksheet, col As Integer, lastRow As Integer
    Dim cOver As Integer, tbl As String, rng As String, rng2 As String, AC As String
    Dim ACI As Integer
    
    Set ws = Sheets("Balance")
    
    AC = Application.Caller
    ACI = CInt(Right(AC, 2))
    
    If ACI = 36 Then
        
        rng = "b31"
        rng2 = "f31"
        tbl = "Table2"
        If oB1 = True Then
            cOver = 2
                Else: cOver = 3
        End If
    End If
    
    lastRow = Sheets("Balance").ListObjects(tbl).Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
         
     
    Sheets("Balance").ListObjects(tbl).Range(lastRow, 1) = txt1
    Sheets("Balance").ListObjects(tbl).Range(lastRow, cOver) = txt2
    Sheets("Balance").ListObjects(tbl).Range(lastRow, 5) = txt3
    
    Debug.Print lastRow, cOver, tbl
End Sub

The most recent problem seems to be that any formula I use in the macro returns the row where the active cell is located, seeming to ignore the formula at all.

The spreadsheet is divided into two sheets: "Balance" and "Debits." The macro that posts the transactions to the debits sheet works. Almost flawlessly. I have to subtract 2 at the end because it wants to add extra spaces. Other than that, it does what I want.

lastRow = Sheets("Debits").ListObjects(tbl).Range.Columns(bCol).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - 2

The macro to post transactions to the "Balance" sheet doesn't work at all.
Thanks in advance!
Shane

 

[StephenCrump]

The most recent problem seems to be that any formula I use in the macro returns the row where the active cell is located, seeming to ignore the formula at all.

.... Almost flawlessly. I have to subtract 2 at the end because it wants to add extra spaces.

Welcome to the Forum!

It's not clear from the code you've posted why LastRow would have anything to do with the ActiveCell, or why you're getting spaces.

In essence, it looks like you're trying to do this?

If Right(Application.Caller, 2) = "36" Then
    cOver = IIf(oB1, 2, 3)  'assumes oB1 is declared as boolean, not string
    With Worksheets("Balance").ListObjects("Table2").ListRows.Add
        .Range(1, 1).Value = txt1
        .Range(1, cOver).Value = txt2
        .Range(1, 5).Value = txt3
    End With
End If

 

[Cityroller]

I can see what you're saying. I went and wrote the bare bones of the sub on its own blank table in a new sheet. It works just fine there. There's obviously something I've added that's not fitting. I'll rewrite the sub and make it work.
I've learned some new things from your answer.
Thanks!

 

[StephenCrump]

You're welcome.

Post back if it's still not working.

 

[Cityroller]

I figured it out. Finally. I still don't know why it does this, but I have five rows above the table with assorted fill, the month name, etc. When it calculates the last row, it works but for some reason it is counting the five spaces above the table and adds that to the final number. I would have thought that telling VBA to count within the table would start at the first row of the table but it's starting at row 1 of the sheet. It's alright now. I just deducted the rows and it doesn't feel like I'm just missing something anymore. It does what it's supposed to.
Can you give me any insight into this?

lastRow = ws.ListObjects(tbl).Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - 4

Thanks.

 

[StephenCrump]

Sorry, I should have realised from your first post what was going wrong ...

You're using the Range.Row property, where in this case Range is ws.ListObjects(tbl).Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)

Range.Row always returns the first row of the first area in Range, i.e. the row number in Excel.So if tbl is B10:C20, say, and B10 is populated, then ws.ListObjects(tbl).Range.Columns(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious) will be B20, and .Row will be 20.