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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

GlennUK

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

Forum statistics

Threads
1,141,931
Messages
5,709,403
Members
421,635
Latest member
mehdi hannechi

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
Top