Formula is not working in VBA

[Kurt]

I remind the double quotes this time on this formula:

    Range("A2").Formula = "= IF(A2=0, ""Base Bid"", ""Alternate - "" & A2)"

The code will not function as it is.

Can anyone clue me in on what I need to change?

 

[Eric W]

You're putting a formula in A2 which refers to A2, or in other words, a circular reference.

 

[Fluff]

What are you trying to do?
Putting that formula in A2 will just give you a circular reference.

 

[Kurt]

Hello,

Fluff and Eric you are both correct I am getting a circular reference.

Does this need to be in R1C1 annotation?

If so can you show me how to correct it?

 

[Fluff]

What are you trying to do?

 

[Eric W]

R1C1 notation won't change anything. The way to correct this is by putting the formula in another cell. Are you trying to insert this formula into a range? If so, something like:

Range("B2:B10").Formula = "= IF(A2=0, ""Base Bid"", ""Alternate - "" & A2)"

and Excel will handle the relative referencing.

 

[jmacleary]

Hi there. You are creating whats called a circular reference - the line of code you have written will put that formula into A2, so A2 will be trying to calculate a result based on itself. If you want the result as a formula in another cell, change the first A2 to where you want it to be. If what you are doing is trying to look at the contents of A2 and replace them based on what is in there, then try this:

If Range("A2").Value = 0 Then
    Range("A2").Value = "Base Bid"
Else
    Range("a2").Value = "Alternate - " & Range("A2").Value
End If

 

[Marcelo Branco]

Not sure i understand what you are trying to do.

See if this is ok (guessing...)

Range("A2") = Evaluate("=IF(A2=0,""Base Bid"",""Alternate - ""&A2)")

Beware that this overwrites the value in A2

M.

 

[Kurt]

Hi there. You are creating whats called a circular reference - the line of code you have written will put that formula into A2, so A2 will be trying to calculate a result based on itself. If you want the result as a formula in another cell, change the first A2 to where you want it to be. If what you are doing is trying to look at the contents of A2 and replace them based on what is in there, then try this:

If Range("A2").Value = 0 Then
    Range("A2").Value = "Base Bid"
Else
    Range("a2").Value = "Alternate - " & Range("A2").Value
End If

Hello jmaleary,

You are exactly correct!

Thanks for everyone and their input.

This worked beautifully!

However it is still not accounting for the Alternate piece when the value is a 1 instead of a 0.

We are almost there!

Cheers from across the pond!