CODEWITHSUNDEEP
excelvbaexcel-formula
sunstache32
sunstache32

Reputation: 149

'Error 1004' when concatenating string

I've concatenated a string to paste an index match formula into rows of a column. Every time I try running this piece of code, I get Runtime Error '1004, but I can't see what I have wrong. Here's the code I have:

Dim j As Long

'Loop down the rows in mainfile

For j = 2 To lastFullRow2



    Dim firstArgument As String
    firstArgument = "Sheet2!" & valuecolumnLetter & "2:" & valuecolumnLetter  & lastFullRow1 & ""
    'MsgBox "firstArgument" & firstArgument

    Dim secondArgument As String
    secondArgument = "Sheet2!" & parameter1columnLetter & "2:" & parameter1columnLetter & lastFullRow1 & ""
    'MsgBox "secondArgument " & secondArgument

    Dim thirdArgument As String
    thirdArgument = "Sheet2!" & parameter2columnLetter & "2:" & parameter2columnLetter & lastFullRow1 & ""
    'MsgBox "thirdArgument " & thirdArgument

    Dim fourthArgument As String
    fourthArgument = "Sheet2!" & parameter2columnLetter & "2:" & parameter2columnLetter & lastFullRow1 & ""
    'MsgBox "fourthArgument " & fourthArgument


    Dim condition3 As String
    condition3 = "Sheet3!" & "D2:" & D & j & ""
    'MsgBox "condition3 " & condition3


    Dim patid1 As String
    patid1 = "Sheet2!" & "D2:" & D & lastFullRow2 & ""
    'MsgBox "patid1 " & patid1

        With ws_mainfile


Dim commandstring As String

commandstring = "=INDEX(" & firstArgument & ",MATCH(1,(" & secondArgument & "=" & condition1 & ")*(" & thirdArgument & "=" & condition2 & ")*(" & patid1 & "=" & condition3 & "),0))"



ws_mainfile.Range("AN" & j).FormulaArray = commandstring



        End With

Next j

The debugger is saying the error is at the ws_mainfile.Range... = commandstring line.

Upvotes: 0

Views: 394

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

condition3 = "Sheet3!" & "D2:" & D & j & ""

patid1 = "Sheet2!" & "D2:" & D & lastFullRow2 & ""

Have you defined a variable D and what is its value?

Maybe you meant:

condition3 = "Sheet3!" & "D2:D" & j 

patid1 = "Sheet2!" & "D2:D" & lastFullRow2

There's also no need to concatenate an empty string onto the end of those lines.

Upvotes: 3

Related Questions