Formula Cell Reference in Macro

[newatmacros]

Right now I am working on a macro that creates new columns and fills those specifically placed columns with a certain function; however, I cannot let my macro be dependent on where they are able to refer to for the function. Just like the macro is designed to not be dependent where it may place the columns, the function formula part also cannot be dependent on the same column as to reference to i.e. (always B column or always D column). What it can always reference to is the cell right before it i.e. (to the left of it). I entered in the RC(-1) function as the reference point for the formula; however, excel is signifying that I have a circular reference due to the fact that the formula when inserted has a notation of itself with the (-1); therefore, it is unable to calculate.
I can really use any greater expertise!
Help?

Sub ConvertingDummies()
Dim Found As Range
Dim LR As Long
On Error Resume Next
Set Found = Rows(1).Find(what:="Stage", LookIn:=xlValues, lookat:=xlWhole)
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Stage 1=Pipeline 0=Closed Lost"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula = "=IF(EXACT(RC(-1),""Negotiate""),""1"",""0"")+IF(EXACT(RC(-1),""Closed Won""),""1"",""0"")+IF(EXACT(RC(-1),""Prove""),""1"",""0"")+IF(EXACT(RC(-1),""Sales Acceptance""),""1"",""0"")+(IF(EXACT(RC(-1),""Develop""),""1"",""0"")+IF(EXACT(RC(-1),""Sales Complete""),""1"",""0""))"


Set Found = Rows(1).Find(what:="Product/Solution", LookIn:=xlValues, lookat:=xlWhole)
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Product/Solution 1=Network 0=TMS/Blank"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula = "=IF(EXACT(RC(-1),""Network""),""1"",""0"")"


Set Found = Rows(1).Find(what:="ENT_PHY_STATE", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "ENT_OP_CLASS_DESC 1=East Coast 0=West Coast"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula = "=IF(EXACT(RC(-1),""MI""),""1"",""0"")+IF(EXACT(RC(-1),""IN""),""1"",""0"")+IF(EXACT(RC(-1),""OH""),""1"",""0"")+IF(EXACT(RC(-1),""PA""),""1"",""0"")+IF(EXACT(RC(-1),""NY""),""1"",""0"")+IF(EXACT(RC(-1),""VT""),""1"",""0"")+IF(EXACT(RC(-1),""ME""),""1"",""0"")+IF(EXACT(RC(-1),""NH""),""1"",""0"")+IF(EXACT(RC(-1)," & _
"""MA""),""1"",""0"")+IF(EXACT(RC(-1),""RI""),""1"",""0"")+IF(EXACT(RC(-1),""CT""),""1"",""0"")+IF(EXACT(RC(-1),""NJ""),""1"",""0"")+IF(EXACT(RC(-1),""DE""),""1"",""0"")+IF(EXACT(RC(-1),""MD""),""1"",""0"")+IF(EXACT(RC(-1),""DC""),""1"",""0"")+IF(EXACT(RC(-1),""WV""),""1"",""0"")+IF(EXACT(RC(-1),""VA"")," & _
"""1"",""0"")+IF(EXACT(RC(-1),""NC""),""1"",""0"")+IF(EXACT(RC(-1),""SC""),""1"",""0"")+IF(EXACT(RC(-1),""GA""),""1"",""0"")+IF(EXACT(RC(-1),""FL""),""1"",""0"")+IF(EXACT(RC(-1),”KY”),”1”,”0”)+IF(EXACT(RC(-1),”TN”),”1”,”0”)+IF(EXACT(RC(-1),”MS”),”1”,”0”)+IF(EXACT(RC(-1),”AL”),”1”,”0”)" & _
""


Set Found = Rows(1).Find(what:="ENT_DOMRA_SOURCE", LookIn:=xlValues, lookat:=xlWhole)
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "ENT_DOMRA_SOURCE 1=Inspection 0=MCS150 Filing/UCC"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula = "=IF(EXACT(RC(-1),""Inspection""),""1"",""0"")"


Set Found = Rows(1).Find(what:="ENT_OP_CLASS_DESC", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "ENT_OP_CLASS_DESC 1=For-Hire 0=Private"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula = "=IF(EXACT(RC(-1),""FOR-HIRE""),""1"",""0"")+IF(EXACT(R1C23:R351C23,""For-Hire""),""1"",""0"")"
End Sub

 

[mikerickson]

In R1C1 notation the reference to the cell to the left is R[-1]C
Square brackets are use for relative references. Round brackets are used for the arguments of a function.

 

[newatmacros]

Hi Mike,
Thank you for the quick reply!
I tried what you suggested with the brackets and it worked perfectly! Thank you so much for the tip. I know that it will definitely help me later on as create more macros with formula references!