Meet condition otherwise retain current value

[artz]

Hi,

This may be simple, but I am not getting it. I am using an If/AND stament to calculate the value in which is dependent on cells B2 and V2.

Something like this:

Formula in H2:
=IF(AND(B2<>"",V2=0),U2,retain current value in H2)

I only want to update H2 when V2 equals zero. Otherwise, I want the current value in H2 to remain the same when the worksheet updates.

When I tried:

Formula in H2:
=IF(AND(B2<>"",V2=0),U2,U2)

This fomula gave me a circular reference error.

Does anyone in the Forum have a suggestion?

Thanks,

Art

 

[Joe4]

If the current/old value in H2 is a calculation, just build that calculation right into the formula you have posted.

Otherwise, here a few options:

1. Use a "helper" column. Do your calculation in another column, then return either H2 or the calculation based on your conditions.

2. Use VBA

 

[artz]

Hi Joe,

Thanks for your response. I think that I realized after reading your response that using the if statement cell formula wouldn't work.

I have the same issue with a "dummy" column that I have with the cell formula in column H.

Below is an attempt at a VBA approach that won't compile- says that I have an Else without an If statement. Looks to me like I have one.

Sub If_False()
Dim c As Variant
Dim d As Variant
Dim e As Variant
Dim f As Variant
Dim g As Variant
    For Each c In Worksheets("Portfolio").Range("B2:B100").Cells
    For Each d In Worksheets("Portfolio").Range("H2:H100").Cells
    For Each e In Worksheets("Portfolio").Range("T2:T100").Cells
    For Each f In Worksheets("Portfolio").Range("U2:U100").Cells
    For Each g In Worksheets("Portfolio").Range("V2:V100").Cells
        If c.Value <> "" And g.Value = 0 Then d.Value = f.Value
        Else: d.Value = d.Value
    Next
    Next
    Next
    Next
    Next
End Sub

Any help is greatly appreciated.

Thanks,

Art

 

[Joe4]

Can you explain, in plan English, exactly what it is you are trying to do?
It is difficult to try to figure out exactly what you are trying to do from your code and formula attempts.