Entering two functions in code

[David G]

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

 

[Bob Phillips]

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

 

[Niek Otten]

Hi David,

For quotes within quotes, use two quotes.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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

 

[David G]

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

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

 

[Niek Otten]

You're missing a closing bracket

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Niek Otten]

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


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