Get the next cell value from column range Help?

[Aarondo]

Hi,

I have a list of numbers in a row in my worksheet.

I am wanting it so that everytime my macro runs it will automatically find the next number in that row and get the value of it. Can anyone tell me the best way to go about doing this as Im new to VBA.

Thanks
Aarondo

 

[Andrew Poulsom]

Which row are your number in? For row 1:

MsgBox WorksheetFunction.Max(ActiveSheet.Rows(1)) + 1

 

[Aarondo]

Hi, thanks for your fast reply.

My numbers are listed in column A and go right down as far as A can go.

Thanks

 

[Andrew Poulsom]

Then it's:

MsgBox WorksheetFunction.Max(ActiveSheet.Columns(1)) + 1

 

[Aarondo]

Hi thanks for your reply.

It doesnt seem to work for me. Everytime it just gives me the number 1 in the messagebox. Heres how Ive implemented into my VBA. Any ideas? Thanks

Private Sub ChangeEvent3(ByVal Target As Range)
Dim nextRow As Integer
If Range("P9").Value = Range("H9").Value And Range("P11").Value = Range("G9").Value Then
MsgBox WorksheetFunction.Max(Sheets("Mylog").Columns(2)) + 1
End If
End Sub

 

[Andrew Poulsom]

You said the numbers were in column A. That's Columns(1) not Columns(2).

And what is ChangeEvent3?

 

[Aarondo]

Ahh yeah I know, that was my mistake, theyre actually in column B

 

[Andrew Poulsom]

Ahh yeah I know, that was my mistake, theyre actually in column B

Then I don't know why you get the number 1. Are you sure that your numbers are actually numbers not text entries that look like numbers? You can use the ISNUMBER function to check.

 

[Aarondo]

Thanks, it works