VBA Formula Code Won't Work

[MikeG]

I wonder if someone could help. I have the following VBA code:

Sub Rectangle1_Click()

Name = "Mike"

Cells(4, 4) = "=if(a=1," & Name & ",2)"

End Sub


What I want it to give when I run it is:

=If( a=1, "Mike",2)

What I get is

=If( a=1, Mike,2)

i.e. it's looking for a range name called Mike.

Thanks,

MikeG

 

[wigi]

Sub Rectangle1_Click()

   Const sName As String = "Mike"
   Cells(4, 4).Formula = "=IF(a=1," & Chr(34) & sName & Chr(34) & ",2)"

End Sub

 

[wigi]

Alternatively,

Sub Rectangle1_Click()

   Const sName As String = "Mike"
   Cells(4, 4).Formula = "=IF(a=1,""" & sName & """,2)"

End Sub

 

[MikeG]

Sub Rectangle1_Click()

   Const sName As String = "Mike"
   Cells(4, 4).Formula = "=IF(a=1," & Chr(34) & sName & Chr(34) & ",2)"

End Sub

Thanks very much wigi - I got the first one working ok. Could you help me with one more thing.

I actually now want to make the variable mike itself based on a range in the sheet called mike so I tried


Const sName As String = Range("Mike")

but this did not work. Do you know what I am doing wrong?

 

[wigi]

Maybe (depending oin sheet name and cell address):

Sub Rectangle1_Click()

   Dim sName As String
   sName = ThisWorkbook.Sheets("Mike").Range("A1").Value
   Cells(4, 4).Formula = "=IF(a=1," & Chr(34) & sName & Chr(34) & ",2)"

End Sub

 

[MikeG]

Maybe (depending oin sheet name and cell address):

Sub Rectangle1_Click()

   Dim sName As String
   sName = ThisWorkbook.Sheets("Mike").Range("A1").Value
   Cells(4, 4).Formula = "=IF(a=1," & Chr(34) & sName & Chr(34) & ",2)"

End Sub

Perfect!

I really appreciate the help.

MikeG