Can't get my "'s Right - Never !!

[JMay]

I always BOMB when trying to UNDERSTAND How to place the " 's and the & 's
when stringing together say line 3 below. Can someone assist?

With Target
.Offset(0, -1).Formula = "=row()-2"
.Offset(0, 1).Formula = "=Vlookup(B"&target.row,Sheet2!$A$1:$B$7,2,False)"
End With

TIA,

Jim

 

[bpeltzer]

For starters, you'll probably always have an even number of "s; whatever
literal string you open must get closed.
Next, whenever you drop in an expression to be evaluated within part of a
string, it will likely need the & before and after.
If you are in the VB editor (esp in break mode, with target.row having a
legit value), you can enter a question mark and then the expression in the
Immediate window; you need the result to look exactly like your formula
should.
"=Vlookup(B" & target.row & ",Sheet2!$A$1:$B$7,2,False)"

 

[Mike H]

Try this

With Target
.Offset(0, -1).Formula = "=row()-2"
.Offset(0, 1).Formula = "=Vlookup(B" & Target.Row &
",Sheet2!$A$1:$B$7,2,False)"
End With


Mike

 

[Bernard Liengme]

I never use the .formula method but I expect you need to change the last one
to
Offset(0, 1).Formula =
"=Vlookup(indirect("B"&target.row),Sheet2!$A$1:$B$7,2,False)"
But how will the worksheet know about "target row"
Why not try

Offset(0, 1).Formula = "=Vlookup(indirect("B" &
ROW()-2),Sheet2!$A$1:$B$7,2,False)"

I have not tested this!

 

[ShaneDevenshire]

Hi,

There is really nothing wrong with the first formula, although I have
simplified them slightly.
To correct the second formula use:

With target
.Offset(0, -1) = "=row()-2"
.Offset(0, 1) = "=Vlookup(B" & .Row & ",Sheet2!$A$1:$B$7,2,False)"
End With

In general quotes are like parentheses - there should be an equal number.
In this case you want to make the .Row argument a vba variable so it must be
outside the quoted text otherwise it is taken literally. This means that you
will need an " & vbaVariable & " type of layout. You got half of it.

As for single quotes, these often appear around path references, especially
those that contain names with spaces. The easiest way to handle them is to
record the code. In fact that is the easiest way to start entering your
formulas in code - record them, and then modify them.

The idea of having a equal number of quotes (single or double) is a general
rule which may not always hold. For example if your formula is looking for a
' in a string then the single quote might appear once.

 

[Dave Peterson]

And sometimes, it's just easier to use R1C1 reference style:

.Offset(0, 1).FormulaR1C1 = "=Vlookup(rc2,Sheet2!r1c1:r7c2,2,False)"

rc2 means same row as the formula, but column 2 (B).

R1C1 is row 1 column 1 (A1)
r7c2 is row 7 column 2 (B7)