#Value on vba formula with factset

[MadMatLePsyke]

Good Morning All,
I have a small issue after running my VBA code. Instead of the correct results I have #Value!
My code is this one :

Sub DATAFAC()

Set Wb = ThisWorkbook
Set WsB = Sheets("BOARD")
Set WsFA = Sheets("FACTSET")

Dim R As Byte
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String
Dim str5 As String
Dim str6 As String
Dim str7 As String
Dim str8 As String

    
    WsFA.Select
    Range("A:A").Select
    Selection.Copy
    Range("A:A").Select
    Selection.PasteSpecial Paste:=xlValues
    
    
    
    R = ActiveSheet.UsedRange.Rows.Count
    str2 = "FE_ESTIMATE"
    str3 = "SALES"
    str4 = "MEAN"
    str5 = Cells(4, 4)
    str6 = "0"
    str7 = "EUR"
    
    
        For i = 5 To R + 2
            str1 = Cells(i - 1, 1).Value
            Cells(i, 4).Select
            Selection.Formula = "=FDSB(" & str1 & ",""" & str2 & " (" & str3 & "," & str4 & ",ANN_ROLL," & str5 & "," & str6 & ",,,'CURRENCY=" & str7 & ",BKRF= 1957')"")"
            i = i + 5
        Next i

instead of a number I have #value, but if I'm putting the ref of cell ie : A4 instead of str1 on Excel I have the correct results
Can someone help me ?

HNY

 

[RoryA]

What kind of data is str1? If it's text, it needs to be in quotes.

 

[MadMatLePsyke]

What kind of data is str1? If it's text, it needs to be in quotes.

Str1 if numerical or alpha numerical : B13X013 or 7383072
when I add quote on in each side of str1 results in the cell in Excel is ...... " & str1 & "
not from the quote I think but more range format maybe ...
Any idea ?

 

[RoryA]

It needs to be done the same way the second argument was was:

Cells(i, 4).Formula = "=FDSB(""" & str1 & """,""" & str2 & " (" & str3 & "," & str4 & ",ANN_ROLL," & str5 & "," & str6 & ",,,'CURRENCY=" & str7 & ",BKRF= 1957')"")"

although you could just use the cell references instead.

 

[MadMatLePsyke]

It needs to be done the same way the second argument was was:

Cells(i, 4).Formula = "=FDSB(""" & str1 & """,""" & str2 & " (" & str3 & "," & str4 & ",ANN_ROLL," & str5 & "," & str6 & ",,,'CURRENCY=" & str7 & ",BKRF= 1957')"")"

although you could just use the cell references instead.

RoryA
it's working, you really make my day
thx a lot

and HNY

 

[RoryA]

Glad we could help.

Happy New Year to you too.