Addition Macro

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

Board Regular
Joined
Jan 28, 2005
Messages
174
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
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174

ADVERTISEMENT

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
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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 ).
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,148
Members
410,666
Latest member
Al3cs
Top