IF Formula

Mahsun23 · Oct 19, 2006

Hi there,

Is it possible to make an IF formula where the value of the cell is linked to another cell if TRUE, but the value doesn't change anymore if FALSE. In other words the cell should keep the latest value if FALSE.

Regards

Mahsun

Mahsun23 · Oct 20, 2006

Mahsun23

I am not certain this will do exactly what you want but give it a try. First I suggest you make a backup of your file.

1. Right click the sheet tab and choose 'View Code'. This will display the VBA window.
2. Copy the code below and paste it in the large white pane at the right of the VBA window.
3. Close the VBA window, return to your sheet and try changing some values.

If it does not do what you expect, report back with as much detail as possible.

Private Sub Worksheet_Change(ByVal Target As Range)
 Columns("IV").EntireColumn.Hidden = True
 Application.EnableEvents = False
 If Range("C8").Value <> Range("U8").Value Then
 Range("W8").Value = Range("IV8").Value
 Else
 Range("W8").Formula = "=C8/D8 - 1"
 Range("IV8").Value = Range("W8").Value
 End If
 Application.EnableEvents = True
End Sub

Thank you Pete,

I guess there is a genius somewhere downunder.

Is it also possible to disable/enable the Event only for cell W8?

Regards

Mahsun

Peter_SSs · Oct 21, 2006

Is it also possible to disable/enable the Event only for cell W8?

What do you mean? If the suggested code did not do what you wanted, can you explain in what way or under what circumstances it failed? For example, what did it do and what should it have done?

Mahsun23 · Oct 23, 2006

Hi Pete,
Your code works. but there is an other issue.
What I try to say is that when I use the same technique in another cell it doesn't work. I modified your code a little bit as you can see here under for cell Y8.

Private Sub Worksheet_Change2(ByVal Target As Range)
Columns("IU").EntireColumn.Hidden = True
Application.EnableEvents = False
If Range("U8").Value <> Range("V8").Value Then
Range("Y8").Value = Range("IU8").Value
Else
Range("Y8").Formula = "=IF(R8=,"".(U8/D8-1))"
Range("IU8").Value = Range("Y8").Value
End If
Application.EnableEvents = True
End Sub

Peter_SSs · Oct 23, 2006

Mahsun23

Can you clarify what formula you want in Y8? There are two problems with the formula as posted:
1. IF(R8=, What should be after the = and before the , ?
2. Confirm that the . in the formula should be a , ?
Range("Y8").Formula = "=IF(R8=,"".(U8/D8-1))"

Mahsun23 · Oct 23, 2006

Dear Pete,

Sorry, I am a little bit negligent.

The formula should be =IF(R8=0,"",(U8/D8-1))

It still doesn't work.

Cheers

Mahsun23

Peter_SSs · Oct 23, 2006

Mahsun23

You need to have both lots of code within the (only) Worksheet_Change event. See if this is what you want. Note a slight change in the order in the code and also note the """" in the Y8 formula.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False

 Columns("IV").EntireColumn.Hidden = True
 If Range("C8").Value <> Range("U8").Value Then
 Range("W8").Value = Range("IV8").Value
 Else
 Range("W8").Formula = "=C8/D8 - 1"
 Range("IV8").Value = Range("W8").Value
 End If

 Columns("IU").EntireColumn.Hidden = True
 If Range("U8").Value <> Range("V8").Value Then
 Range("Y8").Value = Range("IU8").Value
 Else
 Range("Y8").Formula = "=IF(R8=0,"""",(U8/D8-1))"
 Range("IU8").Value = Range("Y8").Value
 End If

 Application.EnableEvents = True
End Sub

Mahsun23 · Oct 23, 2006

Dear Pete, or shall I call you Mr Excel?

Thank you again,

Finally I made the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Columns("IV").EntireColumn.Hidden = True
If Range("C8").Value <> Range("U8").Value Then
Range("X8").Value = Range("IV8").Value
Else
Range("X8").Formula = "=C8/D8 - 1"
Range("IV8").Value = Range("X8").Value
End If

Columns("IU").EntireColumn.Hidden = True
If Range("U8").Value <> Range("V8").Value Then
Range("Y8").Value = Range("IU8").Value
Else
Range("Y8").Formula = "=IF(R8=0,"""",(U8/D8-1))"
Range("IU8").Value = Range("Y8").Value
End If

Columns("IT").EntireColumn.Hidden = True
If Range("V8").Value <> Range("W8").Value Then
Range("Z8").Value = Range("IT8").Value
Else
Range("Z8").Formula = "=IF(S8=0,"""",(V8/D8-1))"
Range("IT8").Value = Range("Z8").Value
End If

Application.EnableEvents = True
End Sub

It works!

I got one more question.

I have to use this code for more 5 more rows, e.q. in stead of row 8, I have to use this code also for 9, 10, 11 etc.

And also for two more sheets (in the same excel file)

Do I have to put them all in the same worksheet_Change event? I can see in VBA that every sheet has an own code pane.

Regards

Mahsun

Peter_SSs · Oct 24, 2006

..or shall I call you Mr Excel?

Definitely not!

I have to use this code for more 5 more rows, e.q. in stead of row 8, I have to use this code also for 9, 10, 11 etc.

Try this structure:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim StartRow As Integer
 Dim EndRow As Integer
 Dim r As Integer

 StartRow = 8
 EndRow = 13
 Application.EnableEvents = False
 Columns("IT:IV").EntireColumn.Hidden = True
 For r = StartRow To EndRow
 If Cells(r, 3).Value <> Cells(r, 21).Value Then
 Cells(r, 23).Value = Cells(r, 256).Value
 Else
 Cells(r, 23).Formula = "=C" & r & "/D" & r & " - 1"
 Cells(r, 256).Value = Cells(r, 23).Value
 End If
 If Cells(r, 21).Value <> Cells(r, 22).Value Then
 Cells(r, 25).Value = Cells(r, 255).Value
 Else
 Cells(r, 25).Formula = "=IF(R" & r & "=0,"""",(U" & r & "/D" & r & "-1))"
 Cells(r, 255).Value = Cells(r, 25).Value
 End If
 If Cells(r, 22).Value <> Cells(r, 23).Value Then
 Cells(r, 26).Value = Cells(r, 254).Value
 Else
 Cells(r, 26).Formula = "=IF(S" & r & "=0,"""",(V" & r & "/D" & r & "-1))"
 Cells(r, 254).Value = Cells(r, 26).Value
 End If
 Next r
 Application.EnableEvents = True
End Sub

And also for two more sheets (in the same excel file)

Do I have to put them all in the same worksheet_Change event? I can see in VBA that every sheet has an own code pane.

Each sheet will need its own Worksheet_Change event code.

Mahsun23 · Oct 25, 2006

Dear Pete,

It's an exciting code that you provided.

I changed the number 23 to 24, (because W needed to be X)
See the changed code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim StartRow As Integer
Dim EndRow As Integer
Dim r As Integer

StartRow = 8
EndRow = 11
Application.EnableEvents = False
Columns("IT:IV").EntireColumn.Hidden = True
For r = StartRow To EndRow
If Cells(r, 3).Value <> Cells(r, 21).Value Then
Cells(r, 24).Value = Cells(r, 256).Value
Else
Cells(r, 24).Formula = "=C" & r & "/D" & r & " - 1"
Cells(r, 256).Value = Cells(r, 24).Value
End If
If Cells(r, 21).Value <> Cells(r, 22).Value Then
Cells(r, 25).Value = Cells(r, 255).Value
Else
Cells(r, 25).Formula = "=IF(R" & r & "=0,"""",(U" & r & "/D" & r & "-1))"
Cells(r, 255).Value = Cells(r, 25).Value
End If
If Cells(r, 22).Value <> Cells(r, 23).Value Then
Cells(r, 26).Value = Cells(r, 254).Value
Else
Cells(r, 26).Formula = "=IF(S" & r & "=0,"""",(V" & r & "/D" & r & "-1))"
Cells(r, 254).Value = Cells(r, 26).Value
End If
Next r
Application.EnableEvents = True
End Sub

I thought I was there, but when I was working on another sheet I had to change the start row to 8 and the end row to 35. And because there are some empty rows I got an error message. This is understandable because the code doesn't recognize empty rows.

Is there a way to by pass these empty rows?

I have a good feeling that this is my last question.

Best Regards

Mahsun

Peter_SSs · Oct 25, 2006

Mahsun

Assuming the rows that are not empty all have something in column C (column 3 in code) then try this.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim StartRow As Integer
 Dim EndRow As Integer
 Dim r As Integer

 StartRow = 8
 EndRow = 13
 Application.EnableEvents = False
 Columns("IT:IV").EntireColumn.Hidden = True
 For r = StartRow To EndRow
 If Cells(r, 3).Value <> "" Then 'Check to see if there is somrthing in col C
 If Cells(r, 3).Value <> Cells(r, 21).Value Then
 Cells(r, 23).Value = Cells(r, 256).Value
 Else
 Cells(r, 23).Formula = "=C" & r & "/D" & r & " - 1"
 Cells(r, 256).Value = Cells(r, 23).Value
 End If
 If Cells(r, 21).Value <> Cells(r, 22).Value Then
 Cells(r, 25).Value = Cells(r, 255).Value
 Else
 Cells(r, 25).Formula = "=IF(R" & r & "=0,"""",(U" & r & "/D" & r & "-1))"
 Cells(r, 255).Value = Cells(r, 25).Value
 End If
 If Cells(r, 22).Value <> Cells(r, 23).Value Then
 Cells(r, 26).Value = Cells(r, 254).Value
 Else
 Cells(r, 26).Formula = "=IF(S" & r & "=0,"""",(V" & r & "/D" & r & "-1))"
 Cells(r, 254).Value = Cells(r, 26).Value
 End If
 End If ' If nothing in col C the code jumps to here
 Next r
 Application.EnableEvents = True
End Sub

IF Formula

Mahsun23

New Member

Mahsun23

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Mahsun23

New Member

Peter_SSs

MrExcel MVP, Moderator

Mahsun23

New Member

Peter_SSs

MrExcel MVP, Moderator

Mahsun23

New Member

Peter_SSs

MrExcel MVP, Moderator

Mahsun23

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

IF Formula

New Member

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock