Get the next cell value from column range Help?

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24
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 :)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Which row are your number in? For row 1:

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

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24
Hi, thanks for your fast reply.

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

Thanks
 

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You said the numbers were in column A. That's Columns(1) not Columns(2).

And what is ChangeEvent3?
 

Aarondo

New Member
Joined
Jul 17, 2010
Messages
24

ADVERTISEMENT

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,649
Messages
5,832,879
Members
430,175
Latest member
Sheenamarie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top