macro help 1

[HOMMER]

want a macro to look a cell say a2 that has a number. this number will be different every day and the user types in the number each day. then using this number look at column b and subtract the number that is in a2 untill it is 0 or less. At this point print say in cell c1 the cell number it ran out on. example:
a2 = 401 b2 = 150 c2 = b5 (the number 401 ended at
b3 = 100 cell b5 so i want to
b4 = 150 say 401 will end on b5)
b5 = 100


thanks for the help

 

[mymou]

Hi Hommer

Try making a function.

This will appear as a custom function in Excel's function wizard. Just write a normal function.

Function compare(ByVal Value, ParamArray otherargs())

x = otherargs(0)
Do Until Value < 1
Count = Count + 1
Value = Value - x(Count, 1)
Loop

compare = x(Count, 1)

End Function

Stew

PS Not sure if you need any error traping - or if there is a better way to use ParamArray (never used them before) - but i think it works.