VBA loop counter and offest

[scottleger4]

Good morning all,

I have a bit of a tricky situation and trying to come up with something creative. Essentially, what I want it to to is something like...

Set Variable to A1
Run Loop
Loop Counter = 1
Reset variable to A1, offset down 1
Run Loop
Loop Counter = 2
Reset variable to A1, offset down 2
Run Loop
Loop Counter = 3
Reset variable to A1, offset down 3
...
...
...

Is there a relatively easy way to do this? Thanks in advance. If the entire length of code or workbook is needed, I get this is very vague.

 

[steve the fish]

If i read right you just want to loop through the cells in column A?

lr = Range("A" & Rows.Count).End(xlUp).Row
arr = Range("A1:A" & lr)

For i = LBound(arr, 1) To UBound(arr, 1)
    MsgBox arr(i, 1)
Next

 

[Fluff]

If you think that your requirements are more than can be handled on the board, you might want to think about contact using the Consulting Services. (link at the top of the board)

 

[JackDanIce]

This doesn't make much sense.

What variable is set to A1?
What is in A1?
What loop - what is the start and end point?
Why are you doing so many repeated loops and counting each complete loop from start to end point?
Why reset to A1 each time and offset?
Why not reset to "A" & Loop counter + 1 (e.g.)
Is the data or whatever you're looping on a single sheet and all other actions relating to same sheet?

Relatively easy is a subjective comment but first more clarity is needed.

 

[scottleger4]

@ steve...kind of. The spreadsheet I am using uses a bunch of data and inputs various values to another program we use at work. I need the loop to continue down the spreadsheet until it gets to the next blank row. However, the data doesn't start until row 14.

@ fluff...I might need to look into that. trying to take what I have and simplify it to the core of what I need will be difficult...the full file/project may very weel need to be reviewed.

@ jack...there are several different variable I have named in the project, each in its own column. The contents I would be using are just numbers (between 0 and 999999). The loop itself starts in row 14, but will have varying length due to user inputs. It could be one line or 1000 lines...I need it to run until it gets to the next blank line. The counter idea I had is basically designed to figure out how far I need to offset down...the rest of the loop requires offsetting all over the sheet based on entry, so a "return to home" that stays constant would help alleviate this by always resetting to the same place initially. All of the data that I would be using is in fact contained in one sheet.

Hope this helps...its really hard to describe so I am trying to keep it boiled down as best I can.

 

[JackDanIce]

Picture, thousand words.

Your spreadsheet is in front of you and invisible to this thread, the description isn't clear (and unfortunately still unclear).

"Return to home" what cell is "home"? Are you using descriptions that only make sense to you because you can see your sheet?

"Loop starts in row 14 but with varying length", what column is this in? And if you have several different variables, is it always same column, does then column change? What is the loop DOING?
i.e. why do you need to loop, you haven't explained purpose of the loop.

Have you tried something like

Range("A1)".End(xldown).Offset(1).Select

or

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

Are you using the loop to test if a cell is non blank and stop when the first blank is found to determine the size from row 14? You don't need to loop to do this which may remove need for a loop counter

Afraid you're still not describing the problem clearly and in a way a blind person who can't see your screen can understand.

Re read your post back to yourself with a blank spreadsheet in front of you - can you recreate your issue just by reading your post?

If you have code, sharing it may help.