Hi,

My problem is entering some code which contains an IF statement and a
vlookup function into 1 line of code. I haven't programmed in Excel for a
while and I think I'm a little rusty. I am getting caught out with a syntax
error and I can't see what it is.

I'm dealing with a range of data which basically consists of 5 columns and a
variable number of rows. I will include the exact code below. What the code
is trying to do is put a formula in column 3 which, if isn't equal to zero,
takes the value in column 2 (same row) and goes to a lookup table elsewhere
on the sheet looks up the corresponding value in the lookup table and returns
that value to column 3. I need to do this by using formulas as listed below,
(Any help would be welcome). The line I'm showing below is a literal
representation. This formula needs to go into rng.Cells(2,3)
=if(D5 = 0, "SCR", VLookup(d5, $K$5:$M$40, 3, False)
I need to enter a generalised form of the above specific formula, but I keep
getting mixed up using constructs like ="=IF(Cells(2,3), "SCR",
vlookup(Cells(2,3), ......
I'm just not doing it right. If you can make sense of my message then
you'll probably be able to help me.
Thanks in advance
--
Regards
David G
Albury, Australia

Cells(2,3).Formula="=IF(D5=0,""SCR"",VLookup(D5, $K$5:$M$40,3,False)"


--
__________________________________
HTH

Bob

"David G" wrote in message
...
Hi,

My problem is entering some code which contains an IF statement and a
vlookup function into 1 line of code. I haven't programmed in Excel for a
while and I think I'm a little rusty. I am getting caught out with a
syntax
error and I can't see what it is.

I'm dealing with a range of data which basically consists of 5 columns and
a
variable number of rows. I will include the exact code below. What the
code
is trying to do is put a formula in column 3 which, if isn't equal to
zero,
takes the value in column 2 (same row) and goes to a lookup table
elsewhere
on the sheet looks up the corresponding value in the lookup table and
returns
that value to column 3. I need to do this by using formulas as listed
below,
(Any help would be welcome). The line I'm showing below is a literal
representation. This formula needs to go into rng.Cells(2,3)
=if(D5 = 0, "SCR", VLookup(d5, $K$5:$M$40, 3, False)
I need to enter a generalised form of the above specific formula, but I
keep
getting mixed up using constructs like ="=IF(Cells(2,3), "SCR",
vlookup(Cells(2,3), ......
I'm just not doing it right. If you can make sense of my message then
you'll probably be able to help me.
Thanks in advance
--
Regards
David G
Albury, Australia

I did try Bob's line of code and it gave an error 1004. I'm trying to use 2
functions namely 1 if statement and a VLookup together in one line of code.
The range is defined as rng, hence I use rng.Cells(i,j). So I refer to the
cells as rng.Cells.
The difficulty seems to be getting both of these functions to work in a
single line of code. Unless I get this sorted my application falls away.
Please help if you can?
Let me know other info you may need.
--
Thanks
David G
Albury, Australia


"Bob Phillips" wrote:

Cells(2,3).Formula="=IF(D5=0,""SCR"",VLookup(D5, $K$5:$M$40,3,False)"


--
__________________________________
HTH

Bob

"David G" wrote in message
...
Hi,

My problem is entering some code which contains an IF statement and a
vlookup function into 1 line of code. I haven't programmed in Excel for a
while and I think I'm a little rusty. I am getting caught out with a
syntax
error and I can't see what it is.

I'm dealing with a range of data which basically consists of 5 columns and
a
variable number of rows. I will include the exact code below. What the
code
is trying to do is put a formula in column 3 which, if isn't equal to
zero,
takes the value in column 2 (same row) and goes to a lookup table
elsewhere
on the sheet looks up the corresponding value in the lookup table and
returns
that value to column 3. I need to do this by using formulas as listed
below,
(Any help would be welcome). The line I'm showing below is a literal
representation. This formula needs to go into rng.Cells(2,3)
=if(D5 = 0, "SCR", VLookup(d5, $K$5:$M$40, 3, False)
I need to enter a generalised form of the above specific formula, but I
keep
getting mixed up using constructs like ="=IF(Cells(2,3), "SCR",
vlookup(Cells(2,3), ......
I'm just not doing it right. If you can make sense of my message then
you'll probably be able to help me.
Thanks in advance
--
Regards
David G
Albury, Australia

You're missing a closing bracket

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"David G" wrote in message ...
|I did try Bob's line of code and it gave an error 1004. I'm trying to use 2
| functions namely 1 if statement and a VLookup together in one line of code.
| The range is defined as rng, hence I use rng.Cells(i,j). So I refer to the
| cells as rng.Cells.
| The difficulty seems to be getting both of these functions to work in a
| single line of code. Unless I get this sorted my application falls away.
| Please help if you can?
| Let me know other info you may need.
| --
| Thanks
| David G
| Albury, Australia
|
|
| "Bob Phillips" wrote:
|
| Cells(2,3).Formula="=IF(D5=0,""SCR"",VLookup(D5, $K$5:$M$40,3,False)"
|
|
| --
| __________________________________
| HTH
|
| Bob
|
| "David G" wrote in message
| ...
| Hi,
|
| My problem is entering some code which contains an IF statement and a
| vlookup function into 1 line of code. I haven't programmed in Excel for a
| while and I think I'm a little rusty. I am getting caught out with a
| syntax
| error and I can't see what it is.
|
| I'm dealing with a range of data which basically consists of 5 columns and
| a
| variable number of rows. I will include the exact code below. What the
| code
| is trying to do is put a formula in column 3 which, if isn't equal to
| zero,
| takes the value in column 2 (same row) and goes to a lookup table
| elsewhere
| on the sheet looks up the corresponding value in the lookup table and
| returns
| that value to column 3. I need to do this by using formulas as listed
| below,
| (Any help would be welcome). The line I'm showing below is a literal
| representation. This formula needs to go into rng.Cells(2,3)
| =if(D5 = 0, "SCR", VLookup(d5, $K$5:$M$40, 3, False)
| I need to enter a generalised form of the above specific formula, but I
| keep
| getting mixed up using constructs like ="=IF(Cells(2,3), "SCR",
| vlookup(Cells(2,3), ......
| I'm just not doing it right. If you can make sense of my message then
| you'll probably be able to help me.
| Thanks in advance
| --
| Regards
| David G
| Albury, Australia
|
|
|

Error 1004 means the error is in the worksheet (in this case), not in VBA. An easy way to check or avoid this to record a macro
while entering the formula manually in the worksheet. You can then strip unnecessary code or compare the recorded code to the code
you wrote yourself.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"David G" wrote in message ...
|I did try Bob's line of code and it gave an error 1004. I'm trying to use 2
| functions namely 1 if statement and a VLookup together in one line of code.
| The range is defined as rng, hence I use rng.Cells(i,j). So I refer to the
| cells as rng.Cells.
| The difficulty seems to be getting both of these functions to work in a
| single line of code. Unless I get this sorted my application falls away.
| Please help if you can?
| Let me know other info you may need.
| --
| Thanks
| David G
| Albury, Australia
|
|
| "Bob Phillips" wrote:
|
| Cells(2,3).Formula="=IF(D5=0,""SCR"",VLookup(D5, $K$5:$M$40,3,False)"
|
|
| --
| __________________________________
| HTH
|
| Bob
|
| "David G" wrote in message
| ...
| Hi,
|
| My problem is entering some code which contains an IF statement and a
| vlookup function into 1 line of code. I haven't programmed in Excel for a
| while and I think I'm a little rusty. I am getting caught out with a
| syntax
| error and I can't see what it is.
|
| I'm dealing with a range of data which basically consists of 5 columns and
| a
| variable number of rows. I will include the exact code below. What the
| code
| is trying to do is put a formula in column 3 which, if isn't equal to
| zero,
| takes the value in column 2 (same row) and goes to a lookup table
| elsewhere
| on the sheet looks up the corresponding value in the lookup table and
| returns
| that value to column 3. I need to do this by using formulas as listed
| below,
| (Any help would be welcome). The line I'm showing below is a literal
| representation. This formula needs to go into rng.Cells(2,3)
| =if(D5 = 0, "SCR", VLookup(d5, $K$5:$M$40, 3, False)
| I need to enter a generalised form of the above specific formula, but I
| keep
| getting mixed up using constructs like ="=IF(Cells(2,3), "SCR",
| vlookup(Cells(2,3), ......
| I'm just not doing it right. If you can make sense of my message then
| you'll probably be able to help me.
| Thanks in advance
| --
| Regards
| David G
| Albury, Australia
|
|
|

Hi David,

For quotes within quotes, use two quotes.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"David G" wrote in message ...
| Hi,
|
| My problem is entering some code which contains an IF statement and a
| vlookup function into 1 line of code. I haven't programmed in Excel for a
| while and I think I'm a little rusty. I am getting caught out with a syntax
| error and I can't see what it is.
|
| I'm dealing with a range of data which basically consists of 5 columns and a
| variable number of rows. I will include the exact code below. What the code
| is trying to do is put a formula in column 3 which, if isn't equal to zero,
| takes the value in column 2 (same row) and goes to a lookup table elsewhere
| on the sheet looks up the corresponding value in the lookup table and returns
| that value to column 3. I need to do this by using formulas as listed below,
| (Any help would be welcome). The line I'm showing below is a literal
| representation. This formula needs to go into rng.Cells(2,3)
| =if(D5 = 0, "SCR", VLookup(d5, $K$5:$M$40, 3, False)
| I need to enter a generalised form of the above specific formula, but I keep
| getting mixed up using constructs like ="=IF(Cells(2,3), "SCR",
| vlookup(Cells(2,3), ......
| I'm just not doing it right. If you can make sense of my message then
| you'll probably be able to help me.
| Thanks in advance
| --
| Regards
| David G
| Albury, Australia