Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA to fill down to bottom of table with condition
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
VBA to fill down to bottom of table with condition
Resolved · Medium Priority · Version 2007
Reply Thu 2 Feb 2012, 17:48Delegate 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
For upcoming training course dates see: Pricing & availability
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
|