Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get my "'s Right - Never !!
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,Fal se)" End With TIA, Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get my "'s Right - Never !!
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)" "JMay" wrote: 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,Fal se)" End With TIA, Jim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get my "'s Right - Never !!
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 "JMay" wrote: 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,Fal se)" End With TIA, Jim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get my "'s Right - Never !!
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! -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "JMay" wrote in message ... 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,Fal se)" End With TIA, Jim |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get my "'s Right - Never !!
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. -- Cheers, Shane Devenshire "JMay" wrote: 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,Fal se)" End With TIA, Jim |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get my "'s Right - Never !!
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) JMay wrote: 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,Fal se)" End With TIA, Jim -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |