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:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top