How do you loop through rows in a specified column?

Question

I am new to programming in general. Using VBA I am trying to loop through range("b6:b18"). Through each pass I am subtracting b3 from the value, and putting a date in a cell, and offsetting by one column until the value is less than b3. Basically I am trying to set up auto payments. Example: I have 5 accounts. 250.36, 17.23, 96.12, 600.00, 5.46. My payment is 36.12 a month. So, 1/1/13 = 36.12 until the first account is < 36.12. But each month must equal the payment. There may be money subtracted from each account in one month.

The code I can't figure out is:

pmt = b3
for each cell in range("b6:b18")
    if cell.value > pmt then
        activecell.value= cell.value -pmt
        activecell =activecell.offset(0,1)

But that code does nothing when closed with next endif, and I don't know how to display the dates in sequential order.

score 1 · Answer 1 · answered Oct 15 '12 at 05:19

I can tell you how to fix the above code so that it 'works' but your overall description is confusing.

That being said, hopefully it puts you on the right track

dim pmt as double
dim i as long

pmt = Range("B3").value

for i = 6 to 16
    if Range("B" & i).value > pmt then
        range("B" & i).value = range("B" & i).value - pmt
    end if
next i

score 1 · Answer 2 · edited May 23 '17 at 12:18

Its not clear to me exactly what you are trying to do, nor how your data is layed out, so I can't give you the code necassary to solve your problem, but here a few hints that may get you on the right road.

Use Option Explicit as the very first line in your Module. This will force you to decalre all your variables.
Don't use ActiveCell or Select unless absolutly necassary. See this answer for some hints on how to avoid these.
When assigning objects, such as setting a Range variable, use Set.
```
Dim rng as Range
Set rng = Range("B3")
```
Not doing this implies using the default property, ie
```
Dim rng as Range
rng = Range("B3")
```
and
```
rng.Value = Range("B3").Value  
```
are equivalent
Using a Range reference without specifying a Worksheet implies the active sheet.
```
Set rng = Range("B3")
```
and
```
Set rng = ActiveSheet.Range("B3")
```
are equivalent
Diagnosing your code: use Step Into (F8) to run your code one line at a time, and
Use the watch window to see the values (and types) of your variables

If you provide some more info about your data layout and expected results, more help can be given

How do you loop through rows in a specified column?

2 Answers2