MrExcel Publishing
Your One Stop for Excel Tips & Solutions

putting a different formula in column F depending on the number in column C


Posted by Ben on October 27, 2000 1:17 PM

How can I create a macro that puts a different formula in column F depending on the number that is in column C

E.g. If the number in column C is 1 the formula in column F is =B2+B1, but if the number is 3 in column C then the formula in column F is =7+12+13.

The number in column C ranges from 1 to 7.
The table varies in size from a previously run macro.

Cheers!


Posted by Ivan Moala on October 28, 2000 5:42 AM

Ben
Suggest you look @
1) Using case select in the macro to test for
number in column C eg.
Select case Range("C1").value
case is 1
.....do something
case is 2
.....do something else
end select

2) Get data range cells count in your Data range
by setting this range to a variant and getting the
variants property Count
eg Set dataRg = Range(Range("D1"),Range("D1").End(xldown))

LoopRg = dataRg.Cells.Count

3) Use this count to loop through the cells and
add the appropriate formula.


HTH

Ivan