Vba else if

[bluegold]

Hi guys,

I have the following VBA script that doesn't quite work for some reason.
Basically I want the script to place the cursor at the 1st free cell of either of my 3 columns depending on which columns have more rows of data. Is there any obvious error in my code? See what happens is if lr1 = lr 3 it sometimes randomly (not all the time) places the cursor at the end of column D instead of A and also if lr3 < lr2 it sometimes places the cursor at end of column A when it should be column AP, it does my head in! Each time I run the macro I seem to get a different result

Sub Update_Cursor()


Dim lr1 As Long
Dim lr2 As Long
Dim lr3 As Long
Dim ws As Worksheet



lr1 = Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Cells(Rows.Count, "D").End(xlUp).Row
lr3 = Cells(Rows.Count, "AP").End(xlUp).Row


Sheets("a").Select
If lr1 = lr3 Then
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ElseIf lr3 < lr2 Then
Range("AP2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Else
Range("D2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If

End Sub

 

[Fluff]

Try moving the sheets select line before you assign values to your lr variables.
At the moment the lr variables will be calculated on the active sheet not sheet "a"

 

[ranman256]

You've already determined the last row, just go to it.

lr1 = Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Cells(Rows.Count, "D").End(xlUp).Row
lr3 = Cells(Rows.Count, "J").End(xlUp).Row


If lr1 = lr3 Then
  Range("A" & lr1).Select
ElseIf lr3 < lr2 Then
  Range("AP" & lr2).Select
Else
  Range("D" & lr3).Select
End If


ActiveCell.Offset(1, 0).Select   'next row

 

[JLGWhiz]

Maybe this

Sub Update_Cursor()
 Dim lr1 As Long
 Dim lr2 As Long
 Dim lr3 As Long
 Dim ws As Worksheet
Sheets("a").Select
 lr1 = Cells(Rows.Count, "A").End(xlUp).Row
 lr2 = Cells(Rows.Count, "D").End(xlUp).Row
 lr3 = Cells(Rows.Count, "AP").End(xlUp).Row
 
    If lr1 > lr2 And lr1 > lr3 Then
        Range("A" & Rows.Count).End(xlUp)(2).Select
    ElseIf lr3 > lr2 And lr3 > lr1 Then
        Range("AP" & Rows.Count).End(xlUp)(2).Select
    ElseIf lr2 > lr1 And lr2 > lr3 Then
        Range("D" & Rows.Count).End(xlUp)(2).Select
    Else
        Rande("A" & Rows.Count).End(xlUp)(2).Select
    End If
 End Sub

 

[bluegold]

Thanks guys my script now works as desired EVERYTIME