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 <(E-Mail Removed)>,
"Mike Proffit" <(E-Mail Removed)> 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