-1

I cannot find the error(s) in this code. What I am trying to do is insert the formula in the column S, when "CN Equity" is used in the column A. I want the macro to run from row 6 to 69.

Sub fx()

Dim x As Long

x = 6

Do

    If InStr(1, (Range("A" & x).Value), "CN Equity") > 0 Then
    Sheets("Sheet1").Range("S" & x).Formula = "=BDP("CADUSD BGN Curncy","LAST_PRICE")" & x
    End If

x = x + 1
Loop Until x = 70

End Sub
user3770308
  • 19
  • 3
  • 2
    Examine `"=BDP("CADUSD BGN Curncy","LAST_PRICE")" & x`. Wrap the double quotes with another set of double quotes as its VBA escape character for quotes or use `Chr(34)` in place of quotes. what is the `& x` supposed to do at the end of the formula? –  Jun 24 '14 at 11:22
  • I tried using the Chr(34) route as you explained, but now get a compile error: Expected: end of statement Using two double quotes also gives me that error. – user3770308 Jun 24 '14 at 11:33
  • well sorry but SO is not a debugging service... you have been pointed out what was wrong now go research VBA syntax + errors. –  Jun 24 '14 at 11:40

1 Answers1

0

Consider:

Sub fx()
    Dim x As Long
    x = 6
    Do
        If InStr(1, (Range("A" & x).Value), "CN Equity") > 0 Then
            Sheets("Sheet1").Range("S" & x).Formula = "=BDP(""CADUSD BGN Curncy"",""LAST_PRICE"")+" & x
        End If
        x = x + 1
    Loop Until x = 70
End Sub

NOTE

Replace the + in the formula with * or & if necessary.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99