VBA to fill down to bottom of table with condition | Excel forum

Resolved · Medium Priority · Version 2007

Thu 2 Feb 2012, 17:48 Delegate Ryan said...

Ryan has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

VBA to fill down to bottom of table with condition

Good afternoon,

Please can you help me?

I am trying to write a piece of code that will number each row in my spreadsheet with an ascending order in column A (ie 1,2,3...), but based on 2 conditions. "the value of column j of that row does not = 0 and there is no value already entered in column A. Where either of these conditions are not met, the code simply moves to the next row. The process needs to repeat until the bottom of the table is reached and then stop.

Unfortunately, I am having great trouble both with the if statement and also in getting the code to continue only to the bottom of the data.

Kind thanks,

Ryan

Fri 3 Feb 2012, 19:16 Trainer Anthony said...

RE: VBA to fill down to bottom of table with condition

Hi Ryan, thanks for your query. You may have to tweak the criteria on this but the code will look something like this:

------------------

Option Explicit

Sub test()

Dim numberofrows As Integer
Dim introw As Integer
Dim numberlabel As Integer

numberlabel = 1

numberofrows = Sheets("MyData").Range("j1").CurrentRegion.Rows.Count

For introw = 1 To numberofrows

If Sheets("MyData").Range("a1").Cells(introw, 1).Value = "" Then

If Sheets("MyData").Range("j1").Cells(introw, 1).Value <> 0 Then

Sheets("MyData").Range("a1").Cells(introw, 1).Value = numberlabel
numberlabel = numberlabel + 1

End If

End If

Next introw

End Sub

------------------

Hope this helps,

Anthony