Excel/VBA Programming
 

Posted to Excel Programming

Title: Using VLOOKUP in VBA

Part of a VBA program I wrote for an Excel workbook uses VLOOKUP to
read a value from a list on one sheet and enters that value in a cell
on another sheet. After the value is entered, I use the PrintOut
method to print the sheet. The code runs fine until it hits a value
that is not in the list. When it does, the error value is not entered
into the sheet and the program stops, no error message. Here is the
code:

If
Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cnn,
_
Sheets("Custname").Range("CN"), 4, False)) = True Then
cname = "--"
Else
cname = Application.WorksheetFunction.VLookup(cnn,
Sheets("Custname").Range("CN"), 4, False)
End If

I want it to enter two dashes if it gets an error, but instead of doing
that, the program quits. What am I doing wrong? Thanks


--
GHoward
------------------------------------------------------------------------
GHoward's Profile: http://www.excelforum.com/member.php...o&userid=16142
View this thread: http://www.excelforum.com/showthread...hreadid=275759

Excel/VBA Programming
 

"GHoward" wrote in message
...

Posted to Excel Programming

Title: Using VLOOKUP in VBA

Part of a VBA program I wrote for an Excel workbook uses VLOOKUP to
read a value from a list on one sheet and enters that value in a cell
on another sheet. After the value is entered, I use the PrintOut
method to print the sheet. The code runs fine until it hits a value
that is not in the list. When it does, the error value is not entered
into the sheet and the program stops, no error message. Here is the
code:

If

Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cnn
,
_
Sheets("Custname").Range("CN"), 4, False)) = True Then
cname = "--"
Else
cname = Application.WorksheetFunction.VLookup(cnn,
Sheets("Custname").Range("CN"), 4, False)
End If

I want it to enter two dashes if it gets an error, but instead of doing
that, the program quits. What am I doing wrong? Thanks


Nothing, the WorksheetFunction method just doesn't handle errors very well.
If you leave the WorksheetFunction part out, you can test for an error. See
here for details

http://www.dicks-blog.com/excel/2004...rksheetfu.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Excel/VBA Programming
 

Thank you that did answer some of my vlookup questions. I have one more I
have changed around the code to this:
Workbooks("Auto Model Grid").Worksheets("Sheet1").Activate
With ActiveSheet

If .Range("L2:L55") = "CCS" Then
If ccsfile = "yes" Then
.Range("N2:N55").Value =
Application.WorksheetFunction.VLookup("ccswrkbk",
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If

ElseIf .Range("L2:L55") = "DCS" Then
If dcsfile = "yes" Then
.Range("N2:N55").Value =
Application.WorksheetFunction.VLookup("dcswrkbk",
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If
End If
End With

My problem is that the if .range("L2:L55") I get an error on saying it is
out of context. Is this because of the data in that range or does it not
understand what range to look at because I thought with the activesheet
command and with it would be working with that sheet regardless. Thanks for
your help in advance.
Heather
"Dick Kusleika" wrote:


"GHoward" wrote in message
...

Posted to Excel Programming

Title: Using VLOOKUP in VBA

Part of a VBA program I wrote for an Excel workbook uses VLOOKUP to
read a value from a list on one sheet and enters that value in a cell
on another sheet. After the value is entered, I use the PrintOut
method to print the sheet. The code runs fine until it hits a value
that is not in the list. When it does, the error value is not entered
into the sheet and the program stops, no error message. Here is the
code:

If

Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cnn
,
_
Sheets("Custname").Range("CN"), 4, False)) = True Then
cname = "--"
Else
cname = Application.WorksheetFunction.VLookup(cnn,
Sheets("Custname").Range("CN"), 4, False)
End If

I want it to enter two dashes if it gets an error, but instead of doing
that, the program quits. What am I doing wrong? Thanks


Nothing, the WorksheetFunction method just doesn't handle errors very well.
If you leave the WorksheetFunction part out, you can test for an error. See
here for details

http://www.dicks-blog.com/excel/2004...rksheetfu.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Excel/VBA Programming
 

1st. You can't ask if a whole range = "CCS" without a loop or a countif.

--
Don Guillett
SalesAid Software

"HeatherO" wrote in message
...
Thank you that did answer some of my vlookup questions. I have one more I
have changed around the code to this:
Workbooks("Auto Model Grid").Worksheets("Sheet1").Activate
With ActiveSheet

If .Range("L2:L55") = "CCS" Then
If ccsfile = "yes" Then
.Range("N2:N55").Value =
Application.WorksheetFunction.VLookup("ccswrkbk",
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If

ElseIf .Range("L2:L55") = "DCS" Then
If dcsfile = "yes" Then
.Range("N2:N55").Value =
Application.WorksheetFunction.VLookup("dcswrkbk",
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If
End If
End With

My problem is that the if .range("L2:L55") I get an error on saying it is
out of context. Is this because of the data in that range or does it not
understand what range to look at because I thought with the activesheet
command and with it would be working with that sheet regardless. Thanks
for
your help in advance.
Heather
"Dick Kusleika" wrote:


"GHoward" wrote in message
...

Posted to Excel Programming

Title: Using VLOOKUP in VBA

Part of a VBA program I wrote for an Excel workbook uses VLOOKUP to
read a value from a list on one sheet and enters that value in a cell
on another sheet. After the value is entered, I use the PrintOut
method to print the sheet. The code runs fine until it hits a value
that is not in the list. When it does, the error value is not entered
into the sheet and the program stops, no error message. Here is the
code:

If


Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cnn
,
_
Sheets("Custname").Range("CN"), 4, False)) = True Then
cname = "--"
Else
cname = Application.WorksheetFunction.VLookup(cnn,
Sheets("Custname").Range("CN"), 4, False)
End If

I want it to enter two dashes if it gets an error, but instead of
doing
that, the program quits. What am I doing wrong? Thanks


Nothing, the WorksheetFunction method just doesn't handle errors very
well.
If you leave the WorksheetFunction part out, you can test for an error.
See
here for details

http://www.dicks-blog.com/excel/2004...rksheetfu.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Excel/VBA Programming
 

Thank you I don't know what I was thinking that really helped me alot though.
It's been over a year since I've programmed let alone trying to figure out
how to program in excel/vb. I think I will also need an index function to
store the value in too instead of storing it in a whole range.

"Don Guillett" wrote:

1st. You can't ask if a whole range = "CCS" without a loop or a countif.

--
Don Guillett
SalesAid Software

"HeatherO" wrote in message
...
Thank you that did answer some of my vlookup questions. I have one more I
have changed around the code to this:
Workbooks("Auto Model Grid").Worksheets("Sheet1").Activate
With ActiveSheet

If .Range("L2:L55") = "CCS" Then
If ccsfile = "yes" Then
.Range("N2:N55").Value =
Application.WorksheetFunction.VLookup("ccswrkbk",
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If

ElseIf .Range("L2:L55") = "DCS" Then
If dcsfile = "yes" Then
.Range("N2:N55").Value =
Application.WorksheetFunction.VLookup("dcswrkbk",
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If
End If
End With

My problem is that the if .range("L2:L55") I get an error on saying it is
out of context. Is this because of the data in that range or does it not
understand what range to look at because I thought with the activesheet
command and with it would be working with that sheet regardless. Thanks
for
your help in advance.
Heather
"Dick Kusleika" wrote:


"GHoward" wrote in message
...

Posted to Excel Programming

Title: Using VLOOKUP in VBA

Part of a VBA program I wrote for an Excel workbook uses VLOOKUP to
read a value from a list on one sheet and enters that value in a cell
on another sheet. After the value is entered, I use the PrintOut
method to print the sheet. The code runs fine until it hits a value
that is not in the list. When it does, the error value is not entered
into the sheet and the program stops, no error message. Here is the
code:

If


Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cnn
,
_
Sheets("Custname").Range("CN"), 4, False)) = True Then
cname = "--"
Else
cname = Application.WorksheetFunction.VLookup(cnn,
Sheets("Custname").Range("CN"), 4, False)
End If

I want it to enter two dashes if it gets an error, but instead of
doing
that, the program quits. What am I doing wrong? Thanks


Nothing, the WorksheetFunction method just doesn't handle errors very
well.
If you leave the WorksheetFunction part out, you can test for an error.
See
here for details

http://www.dicks-blog.com/excel/2004...rksheetfu.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com