CODEWITHSUNDEEP
excelvba
salifgotem
salifgotem

Reputation: 75

Error 1004 when insert multiple strings in a cell

The code :

Sub ConcatenateS()
    

Dim String1 As String
Dim String2 As String
Dim String3 As String
Dim String4 As String

String1 = "=""Et la Société "
String2 = """&'Proposition de contrat'!C6&"""
String3 = " domiciliée "
String4 = """&RECHERCHEV(C17;'Liste des sous-traitants'!A2:F35;2;VRAI)&"""""

Worksheets("Commande de Travaux").Range("A30").Value = String1 & String2 & String3 & String4

End Sub

Cans someone explains to me why I receive this error ? (the formula is in French, but I don't think it's a problem because even when I only enter string 1 it doesn't function)

Upvotes: 0

Views: 34

Answers (1)

Gove
Gove

Reputation: 1804

String 1 won't work by itself because it lacks the closing quote.

When I make a new workbook and create sheets with the following names:

  • Proposition de contrat
  • Liste des sous-traitants
  • Commande de Travaux

and change string 4 to English as follows:

String4 = """&vlookup(C17,'Liste des sous-traitants'!A2:F35,2,true)&"""""

Your code works fine for me. At first, I thought that your issue might have been that one of the sheet names did not match, but that would be error #9, not #1004. Here is my complete that functions in case it is of help.

Sub ConcatenateS()

    Dim String1 As String
    Dim String2 As String
    Dim String3 As String
    Dim String4 As String
    
    String1 = "=""Et la Société "
    String2 = """&'Proposition de contrat'!C6&"""
    String3 = " domiciliée "
    String4 = """&vlookup(C17,'Liste des sous-traitants'!A2:F35,2,true)&"""""
    
    Debug.Print String1 & String2 & String3 & String4
    Worksheets("Commande de Travaux").Range("A30").value = String1 & String2 & String3 & String4

End Sub

Upvotes: 1

Related Questions