ExcelBanter - Entering two functions in code

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Entering two functions in code (https://www.excelbanter.com/excel-programming/416754-entering-two-functions-code.html)

Entering two functions in code

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

Entering two functions in code

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

Entering two functions in code

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

Entering two functions in code

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

Entering two functions in code

Entering two functions in code

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
|
|
|