Syntax error

P

Pablo

What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
 
L

L. Howard Kittle

Just guessing;

"=if(iserror(VLOOKUP(RC1,[

should be

"=if(iserror(VLOOKUP(R1C1,[

HTH
Regards,
Howard
 
T

Tom Ogilvy

is data_rng a defined name in your workbook. If you do
Insert=>Name=>define, is there name data_rng listed there. If not, then
that is your problem. If data_rng is a string variable, then you would need.

. . .range(" & data_rng & "),. . .

in each case.

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .
 
T

Tom Ogilvy

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

should actually probably be

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address(1,1,xlR1C1,True) & "),. . .


--
Regards,
Tom Ogilvy


Tom Ogilvy said:
is data_rng a defined name in your workbook. If you do
Insert=>Name=>define, is there name data_rng listed there. If not, then
that is your problem. If data_rng is a string variable, then you would
need.

. . .range(" & data_rng & "),. . .

in each case.

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

--
Regards,
Tom Ogilvly


Pablo said:
What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
Click to expand...
Click to expand...
 
D

Dave Peterson

Just a variation of Tom's suggestion.

If data_rng is a named range (Insert|name|define), then drop the range() from
the formular1c1:

ActiveCell.FormulaR1C1 = _
"=if(iserror(VLOOKUP(RC1,[book2.xls]Sheet1!data_rng,2,FALSE))," _
& """"",VLOOKUP(RC1,[book2.xls]Sheet1!data_rng,2,FALSE))"

(You were missing a closing parentheses at the end, too.)

===
If data_rng is a variable that represents a range on sheet1 of the
abretesemamo.xls workbook, then maybe you could do it this way:

dim data_rng as range
with workbooks("abretesesamo.xls").worksheets("sheet1")
set data_rng = .range("A1:B999")
'or even .range("somename")
set data_rng = .range("somename")
'or just for overkill...
'the variable has the same spelling as the named range!
'it might make it easier to keep track of things
set data_rng = .range("data_rng")
end with

ActiveCell.FormulaR1C1 = _
"=if(iserror(VLOOKUP(RC1," & _
data_rng.Address(external:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE))," _
& """"",VLOOKUP(RC1," & _
data_rng.Address(external:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE))"



What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
Click to expand...
 
D

Dave Peterson

For that second suggestion, I think Tom meant:

....[abretesesamo.xls]Sheet1!" & data_rng.address(ReferenceStyle:=xlR1C1) & _
",2,false))....


Tom said:
is data_rng a defined name in your workbook. If you do
Insert=>Name=>define, is there name data_rng listed there. If not, then
that is your problem. If data_rng is a string variable, then you would need.

. . .range(" & data_rng & "),. . .

in each case.

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

--
Regards,
Tom Ogilvly

Pablo said:
What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
Click to expand...
Click to expand...
 
D

Dave Peterson

I bet you still want to drop the .range() stuff.
<vbg>
And maybe the external:=true???? (depending on what data_rng is/was/will be??)

Tom said:
If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

should actually probably be

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address(1,1,xlR1C1,True) & "),. . .

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
is data_rng a defined name in your workbook. If you do
Insert=>Name=>define, is there name data_rng listed there. If not, then
that is your problem. If data_rng is a string variable, then you would
need.

. . .range(" & data_rng & "),. . .

in each case.

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

--
Regards,
Tom Ogilvly


Pablo said:
What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Not on the first suggestion although if it were a defined name you would -
my misread of the formula string.

--
Regards,
Tom Ogilvy


Dave Peterson said:
I bet you still want to drop the .range() stuff.
<vbg>
And maybe the external:=true???? (depending on what data_rng is/was/will
be??)

Tom said:
If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

should actually probably be

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address(1,1,xlR1C1,True) & "),. . .

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
is data_rng a defined name in your workbook. If you do
Insert=>Name=>define, is there name data_rng listed there. If not,
then
that is your problem. If data_rng is a string variable, then you would
need.

. . .range(" & data_rng & "),. . .

in each case.

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

--
Regards,
Tom Ogilvly


What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Just disregard my posts. I should have started over.

--
Regards,
Tom Ogilvy


Tom Ogilvy said:
If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

should actually probably be

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address(1,1,xlR1C1,True) & "),. . .


--
Regards,
Tom Ogilvy


Tom Ogilvy said:
is data_rng a defined name in your workbook. If you do
Insert=>Name=>define, is there name data_rng listed there. If not, then
that is your problem. If data_rng is a string variable, then you would
need.

. . .range(" & data_rng & "),. . .

in each case.

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

--
Regards,
Tom Ogilvly


Pablo said:
What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Have a good night's sleep and go get 'em tomorrow????

<gd&r>

Tom said:
Just disregard my posts. I should have started over.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

should actually probably be

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address(1,1,xlR1C1,True) & "),. . .


--
Regards,
Tom Ogilvy


Tom Ogilvy said:
is data_rng a defined name in your workbook. If you do
Insert=>Name=>define, is there name data_rng listed there. If not, then
that is your problem. If data_rng is a string variable, then you would
need.

. . .range(" & data_rng & "),. . .

in each case.

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

--
Regards,
Tom Ogilvly


What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
P

Pablo

I really apprecite everyone's comments. I will try some of the
suggestions tomorrow. Thank a million.
~ Pablo

Dave Peterson ha escrito:
Have a good night's sleep and go get 'em tomorrow????

<gd&r>

Tom said:
Just disregard my posts. I should have started over.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

should actually probably be

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address(1,1,xlR1C1,True) & "),. . .


--
Regards,
Tom Ogilvy


is data_rng a defined name in your workbook. If you do
Insert=>Name=>define, is there name data_rng listed there. If not, then
that is your problem. If data_rng is a string variable, then you would
need.

. . .range(" & data_rng & "),. . .

in each case.

If data_rng is an object variable of type range, then

. . .range(" & data_rng.Address & "),. . .

--
Regards,
Tom Ogilvly


What is wrong with this line.It gives me a syntax error

ActiveCell.FormulaR1C1 = _

"=if(iserror(VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)),
_

"""",VLOOKUP(RC1,[abretesesamo.xls]Sheet1!range(data_rng),2,FALSE)"
Thanks for your help
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loading a formula from VB 4
Range question 14
macro to copy rows that contain a cell with a certain number 2
File Name changes with dates and Deleting Rows of Zeros 0
EXCEL - IF(IFERROR(VLOOKUP question 0
wrong code 1
"=TEXT(RC[-1],"MMM")" 4
Macro autofills formula when a cell value is changed 4

Top