PDA

View Full Version : Using Variables in VBA formulas



leal72
10-08-2009, 10:56 AM
Getting the 1004 error - formula you typed contains an error

want to use named ranges in the formula.

Dim ShowRN As String
Dim DispRN As String
Dim BlankRN As String
Dim WsName As String

WsName = ActiveSheet.Name
ShowRN = WsName & "!" & "Show" & i
DispRN = WsName & "!" & Disp & i
BlankRN = WsName & "!" & "Blank" & i

ActiveWorkbook.Worksheets(WsName).Names.Add Name:="Series" & i, _
RefersToR1C1:="=IF('[" & ShowRN & "]','[" & DispRN & "]','[" & BlankRN & "]')"

leal72
10-08-2009, 12:33 PM
This got it working how I wanted
ActiveWorkbook.Worksheets(WsName).Names.Add Name:=Series, _
RefersToR1C1:="=IF(" & ShowRN & "," & DispRN & "," & BlankRN & ")"