Excel - advanced formula help

Closed
mjohnson - Oct 21, 2010 at 02:49 AM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Oct 28, 2010 at 09:12 AM
Hi,

I am trying to come up with (find) a formula in Excel 2007 that will allow me to affect either "cell2" or "cell3" depending on the value of "cell1". To be more specific, the values in B1:B100 are either the letter "s" or "f". The values in C1:C100 and D1:D100 are numerical. If "s" is the value in B3 (for example), I want it to subtract 1 (-1) from C3, and leave D3 at it's current value; but if "f" is the value of B3, I want it to subtract 1 from D3, and leave C3 at it's current value.

B is the type of event while C and D are the number of times remaining that that event can be carried out.

I hope this makes sense. Thanks in advance!!



1 reply

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Oct 28, 2010 at 09:12 AM
Hi mjohnson,

A formula won't do the job. A formula can't change the value of another cell.
A VB code can.

So try this code:
Sub test()
Set MyRng = Range("B1:B100")
For Each cell In MyRng
    If cell.Value = "s" Then cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - 1
    If cell.Value = "f" Then cell.Offset(0, 2).Value = cell.Offset(0, 2).Value - 1
        Next
End Sub

Best regards,
Trowa
0