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

[zeno]

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:

'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

 

[JLGWhiz]

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:

'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]

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?

 

[Norie]

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]

An action would be:

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]

If they all happen on the same row then all you need is the one loop.

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.

Range("O1:O" & K).FormulaR1C1 = "=SUM(R[-1]C[-1]:RC[-1])/2"

 

[zeno]

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