Addition Macro

[johngio]

Hi all,

I have 3 columns with the following values:

71642 19174 18888
71642 20974 18888

What I would like to do is create a loop that looks at the cells under these values, and if the values have increased by 1800, add another 1800, and if the cell values haven't increased, copy the number from above, such that I end up with:


71642 19174 18888
71642 20974 18888
71642 22774 18888
71642 24574 18888
71642 26374 18888
71642 28174 18888
71642 29974 18888
71642 31774 18888
71642 33574 18888
71642 35374 18888
71642 37174 18888

I have tried something simple for an individual column:

Sub Macro9()

Dim iRow As Integer

For iRow = 16 To 50
If Cells(7, iRow) <> "" Then
Cells(7, iRow).Value = "hello"
Next iRow

End Sub

But I can't get this to work let alone add the value from above.

Can anyone point me in the right direction?

Cheers

John

 

[GlennUK]

Why not used cell formulae? Iin other words, why do you need a macro for this?

Referring to your macro, why are testing row 7 for all those columns? Unless, let me guess, you really wanted the variable "iRow" to be a row pointer? But you have it in the column index position in the CELLS() property call.

 

[johngio]

Glenn,

I've got this working now. Had the rows/columns the wrong way around :P

I have to use a macro because I am writing several macros which I will combine and most of the data within these cells will be overwritten every time I run it.

Cheers

John

 

[GlennUK]

Glad you solved it. ( I assume you mean the whole thing? )

 

[johngio]

Oh boy :P

The whole thing is taking me a while.

Small pieces at a time; this part I think I've got though. . .

Cheers

 

[GlennUK]

Ha ha, good luck. If you get stuck post back.

 

[johngio]

Here's something I'm stuck on you might be able to help me with Glenn (this has originated from the same piece of the puzzle as above).

I have the following formula:

Sub test()
Dim iRow As Integer, iCol As Integer
For iCol = 7 To 9
For iRow = 16 To 50

If Cells(iRow, iCol) = "" Then
If Cells(iRow - 2, iCol) = Cells(iRow - 1, iCol) Or Cells(iRow - 2, iCol) = 0 Then
Cells(iRow, iCol) = Cells(iRow - 1, iCol)
Else
Cells(iRow, iCol) = Cells(iRow - 1, iCol) + Range("Q14")
End If
End If
Next iRow
Next iCol
End Sub

Basically, if one of these columns reaches 100000 I want to stop increasing it's value by Q14, and start increasing the value of the smallest column by Q14. I think I know how to do this, however I need to be able to identify which of the 3 columns has the lowest numerical value.

Do you know if there is a function that can do this?

I think if I put:

If Cells(iRow,iCol)>100000 Then
ADD Q14 TO THE SMALLEST VALUE AND KEEP THE OTHER TWO THE SAME

just don't know how to find that smallest value

Any info would be appreciated.

Cheers

John

 

[GlennUK]

I'd test all 3 at once to see if any are over 100000, and then find out which of the other 2 are the smallest. Have a look at Application.Max and Application.Min ( or in full Application.WorksheetFunction.Max and Application.WorksheetFunction.Min ).