Home » Forums » Open Source Learning Center » Question and Answer

Need a Macro to loop as long as there is data in the next column.

Submitted by dkassin on 28 February, 2012 - 20:43

I have a macro that fills in data every six columns. I have a macro that precedes this one that adds data to a column. I need the code that would sandwich my current macro so that I would run as long as there were columns with data still there.
I'm looking for some sort of do loop. Just haven't been able to get the coding right I suspect it would looks something like this:
Sub RepeatMacro()
Do...

Loop

End Sub

If you could provide details on how to get it to repeat as long as there is any data in a column It would be greatly appreciated.

Thanks

‹ Non-Linear interpolation Divide one excel workbook to many ›
»
Nick's picture

here we go: StartCol = 2 '

Submitted by Nick on 29 February, 2012 - 14:12.
here we go: 

StartCol = 2  ' for example
Counter = 0
while cells(1, StartCol + Counter *6 )<>""  ' loop 6 cols 
' do your thing
Counter = Counter +1
wend
»

a BETTER IDEA

Submitted by dkassin on 29 February, 2012 - 16:12.

Would it be possible to write a code, where the macro insert a row, and then have the loop run where it runs the macro in every column where there is data in the cell above it? If that were possible I believe it would work.

So the macro inserts a row when activated.
Then write the code for the loop to run as long as there is data in row above the newly inserted row.
Then once it hits a column that does not have data in the row about it.
The loop stops and the macro stops working.

»

I believe I found the reason

Submitted by dkassin on 29 February, 2012 - 15:43.

I believe I found the reason that It was not working correctly that would allow for it to work, i just think the code might need a little tweaking. In the macro that I need to run it inserts a row, so every time it was running it would insert columns which didn't work.
So i went into the code and started the code after the macro inserted a line. Now it works but there is no data in the column. Could you write the code so that it runs the macro until it hits data in a column and that way I could just write in a cell which would trigger the macro to stop at the last column?

»