Hi all. I've got a vlookup formula on a spreadsheet with relative cell addressing that my macro copies when appropriate. I want to retain that formula but concatenate " For Serial Number 12345" so that appears in the cell.

sSerialNum is the variable. Here's my (failing) approach:

If Left(items(j), 3) = "UPG" Then
sForsn = " For Serial Number " & sSerialNum
ActiveCell.Offset(0, 4).Formula = ActiveCell.Offset(0, 4).Formula _
& " & "" & sForsn & """
End If

This results in the formula
=VLOOKUP(C30,PriceMatrix,2,0)) & " & sForsn & "

which tacks on '& sForsn &' after the successful lookup of the value. Of course, I want "For Serial Number 12345" to be there instead.

Any help... much thanks!
Mike

Quote marks need to be doubled. One way:

If Left(items(j), 3) = "UPG" Then
sForsn = " For Serial Number " & sSerialNum
With ActiveCell.Offset(0, 4)
.Formula = .Formula & " & """ & sForsn & """"
End With
End If


In article ,
"Mike Proffit" wrote:

Hi all. I've got a vlookup formula on a spreadsheet with relative cell
addressing that my macro copies when appropriate. I want to retain that
formula but concatenate " For Serial Number 12345" so that appears in the
cell.

sSerialNum is the variable. Here's my (failing) approach:

If Left(items(j), 3) = "UPG" Then
sForsn = " For Serial Number " & sSerialNum
ActiveCell.Offset(0, 4).Formula = ActiveCell.Offset(0, 4).Formula _
& " & "" & sForsn & """
End If

This results in the formula
=VLOOKUP(C30,PriceMatrix,2,0)) & " & sForsn & "

which tacks on '& sForsn &' after the successful lookup of the value. Of
course, I want "For Serial Number 12345" to be there instead.

Any help... much thanks!
Mike