VBA: looping through range k with 3 different actions depending on row

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
Hello,

I'm looping with VBA through a range (2 columns) and need to perform 4 different actions. In column O, row 2 (the 1st cell) I have action A, in the last cell, action C, and in the cells in between action B. In the second column, I perform Action D for the whole range. The # of rows is the same in both columns.

I'd like to know if I can use only 1 single loop counter k, or if I should define more, say 3 counter variables per action, or 2 counter variables per column? Here is the example:

Code:
'Column O
    k = Range("M" & Rows.coUnt).End(xlUp).Row
 '** first cell of range
Range("O2").FormulaR1C1 = Action A 
 '**cells in between
    For k = 3 To k - 1
Range("O" & k).FormulaR1C1 = Action B 
    Next k
 '** last cell of range
Range("O" & k).FormulaR1C1 = Action C 
'Column P
Cells(k, "P").FormulaR1C1 = Action D
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,311
Office Version
2013
Platform
Windows
Hello,

I'm looping with VBA through a range (2 columns) and need to perform 4 different actions. In column O, row 2 (the 1st cell) I have action A, in the last cell, action C, and in the cells in between action B. In the second column, I perform Action D for the whole range. The # of rows is the same in both columns.

I'd like to know if I can use only 1 single loop counter k, or if I should define more, say 3 counter variables per action, or 2 counter variables per column? Here is the example:

Code:
'Column O
    k = Range("M" & Rows.coUnt).End(xlUp).Row
 '** first cell of range
Range("O2").FormulaR1C1 = Action A 
 '**cells in between
    For k = 3 To k - 1
Range("O" & k).FormulaR1C1 = Action B 
    Next k
 '** last cell of range
Range("O" & k).FormulaR1C1 = Action C 
'Column P
Cells(k, "P").FormulaR1C1 = Action D
I am not sure what the question is. I only see one loop. The other three statements are fixed with a single range specified in each. You have k set up as a vairable to represent the last row number with data, and there is nothing in the posted code that changes that value. So each time you use k as the row number, it is representing the same row. If the code is woirking as desired, why change it?
 

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
What I have done now, is repeat the action D for column P a total of 3 times. I have modified this after posting this question. But action D is the same for the whole column, and I haven't found a way to use the counter k a second time for the use of the whole column P. So it seems that I have 2 options: 1) write action D 3 times, in parallel to what I wrote for column O, or define a 2nd counter l that will loop for column P (with k = l).
Is this a correct assumption, or is there a way that I don't have to replicate the counter?
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,365
Office Version
365
Platform
Windows
What exactly are these 'actions'?

Can you post the actual code?

If you are performing all the actions on one row then you only need one main loop through the rows.
 

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
An action would be:

Code:
Range("O" & k).FormulaR1C1 = "=SUM(R[-1]C[-1]:RC[-1])/2"
Every of the 4 actions is a variation in the calculation, and every of the 4 actions is different.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,365
Office Version
365
Platform
Windows
If they all happen on the same row then all you need is the one loop.
Code:
k = Range("M" & Rows.coUnt).End(xlUp).Row

For rw = 1 To k
      Range("O" & rw).FormulaR1C1 = "=SUM(R[-1]C[-1]:RC[-1])/2"
      ' code for other formulas
Next rw
By the way, if you are just putting formulas in then you might not even need a loop.
Code:
Range("O1:O" & K).FormulaR1C1 = "=SUM(R[-1]C[-1]:RC[-1])/2"
 

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
What would happen is the following, from a table perspective:

row 1: column 'O' = column 'P' (titles)
row 2: Action A = Action D
row 3: Action B = Action D
row 4: Action B = Action D
...
row n-1: Action B = Action D
row n: Action C = Action D

=: column separation
 

Forum statistics

Threads
1,082,630
Messages
5,366,656
Members
400,909
Latest member
ola97316

Some videos you may like

This Week's Hot Topics

Top