variable to store multiple values

[somesoldiers]

Hi Guys

I need a bit of help with the below macro which I am trying to create

I recorded the below vlookup, which works perfectly. It checks a list on sheet “Map” and returns a value depending on whether the reference is one of the 6 or not. These 6 are likely to change over time so I would prefer to declare them as variables rather than build them directly into the macro

Do
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=""CBT 10Y SWAP 09/Sep"","".03125"",IF(RC[-2]=""CBT 10Y T-NOTE 09/Sep"","".03125"",IF(RC[-2]=""CBT 2Y T-NOTE 09/Sep"","".03125"",IF(RC[-2]=""CBT 2Y T-NOTE 09/Dec"","".03125"",IF(RC[-2]=""CBT 5Y T-NOTE 09/Sep"","".03125"",IF(RC[-2]=""CBT T-BONDS 09/Sep"","".03125"",""1""))))))"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -2))

Was thinking of declaring the variables which will always be in C2-C7 as below and substituting each reference in the vlookup. However something is wrong as I am getting a value of 1 for all

Dim strCurr1 As String: strCurr1 = Sheets("Map").Range("C2").Value
Dim strCurr2 As String: strCurr2 = Sheets("Map").Range("C3").Value
Dim strCurr3 As String: strCurr3 = Sheets("Map").Range("C4").Value
Dim strCurr4 As String: strCurr4 = Sheets("Map").Range("C5").Value
Dim strCurr5 As String: strCurr5 = Sheets("Map").Range("C6").Value
Dim strCurr6 As String: strCurr6 = Sheets("Map").Range("C7").Value

    Do
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=""& strCurr1 &"","".03125"",IF(RC[-2]="" & strCurr2 &"","".03125"",IF(RC[-2]="" & strCurr3 &"","".03125"",IF(RC[-2]="" & strCurr4 &"","".03125"",IF(RC[-2]="" & strCurr5 &"","".03125"",IF(RC[-2]="" & strCurr6 &"","".03125"",""1""))))))"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -2))

Can anyone suggest what might be wrong and how I might fix this please?

P.S. "Curr" is probably not the right one to use but am not sure which is

Thanks All

Noel

[StephenR]

Not sure but have you just got a bit quote-happy...

"=IF(RC[-2]=" & strCurr1 & ",.03125,IF(RC[-2]=" & strCurr2 & ",.03125,IF(RC[-2]=" & strCurr3 & ",.03125,IF(RC[-2]=" & strCurr4 & ",.03125,IF(RC[-2]=" & strCurr5 & ",.03125,IF(RC[-2]=" & strCurr6 & ",.03125,1))))))"

[somesoldiers]

Hi Stephen,

Am getting Run Time Error 1004

Application-defined or object defined error

[romperstomper]

You could use MATCH:

Do
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(MATCH(RC[-2],Map!R2C3:R7C3,0)),0.03125,1)"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -2))

[somesoldiers]

that's way tidier than what I had and works a treat,

thanks a million guys