Excel VBA string

[ted.martin]

I have a line of VBA code as follows with works fine:

  Cells(2, iCol + 1).Value = "=VLOOKUP(B2,'[SFE macro Remittance.xlsm]SITS'!$A$2:$C$" & iDataRow & ",2,FALSE)"

In the Excel spreadsheet the cell formula reads

=VLOOKUP(B4,'[SFE macro Remittance.xlsm]SITS'!$A$2:$C$25727,2,FALSE)

This works correctly and gives the correct value in the cell.

My problem is I need to Excel spreadsheet cell to show

=VLOOKUP(B3,'[SFE macro Remittance.xlsm]SITS'!$A$2:$C$25727,2,FALSE) & "B"

If I manually amend the cell formula then the result is correct but I need to incorporate the & "B" into the VBA code.

I have tried various syntax to add the extra & "B" to the VBA code without success.

Can anyone help please? Thanks

 

[CJ_London]

have you tried

"=VLOOKUP(B2,'[SFE macro Remittance.xlsm]SITS'!$A$2:$C$" & iDataRow & ",2,FALSE)" & ""B"""

 

[ted.martin]

Thanks for your quick response.

That syntax goes red immediately it is not quite right.

Just tried

 Cells(2, iCol + 1).Value = "=VLOOKUP(B2,'[SFE macro Remittance.xlsm]SITS'!$A$2:$C$" & iDataRow & ",2,FALSE) & ""B"""

and that did not work either. I got the same error I have been getting all day 'subscript out of range'

 

[arnelgp]

will this work:

Cells(2, icol + 1).Value = "=VLOOKUP(B2,'[SFE macro Remittance.xlsm]SITS'!$A$2:$C$" & iDataRow & ",2,FALSE)&'B'"

 

[ted.martin]

Sorry no. Remember we need to add to the existing VLOOKUP string an ampersand and B in quotes to join it onto the VLOOKUP string that works.
Thanks

 

[arnelgp]

how about if you use Formula instead of Value.

Cells(2, icol + 1).Formula = "=VLOOKUP(B2,'[SFE macro Remittance.xlsm]SITS'!$A$2:$C$" & iDataRow & ",2,FALSE)&""B"""

 

[ted.martin]

Nice approach using .Formula but unfortunately no success. Still keeps coming up subscript out of range.

I feel inserting the ampersand is the problem. Thanks

 

[ted.martin]

SOLVED

 Cells(2, iCol + 1).Value = "=VLOOKUP(B2,'[SFE macro Remittance.xlsm]SITS'!$A$2:$C$" & iDataRow & ",2,FALSE) & ""B"""

 

[arnelgp]

is there any difference in my code and yours?