I am trying to debug the following line of code and am not sure why I am
getting an error as everything looks fine to me. The line is:

If IsError(Application.VLookup(Cells(ProductTitleRow, j),
Workbooks(Current_Consolidated_Units).Sheets(Cells (i,
RegionColumn)).Range("L410:BC418"), Consolidated_Offset + Growth * Cells(i,
GrowthColumn), False)) Then

And the values which come up as I move my mouse over each item a
Cells(ProductTitleRow, j)="APPL/PROF SVC"
Current_Consolidated_Units="2006 Access Based Units - Actuals.xls"
Cells(i, RegionColumn)="QC"
Consolidated_Offset=36
Growth=6
Cells(i, GrowthColumn)=1

I am at a loss in understanding what might be wrong as it looks to me as
though all types within all fields are correct. Can you tell me if you see a
problem. Or is there something else that might be wrong?

Thanks.

Darrell

I have noticed that when I type in "QC" in place of Cells(i, RegionColumn)
that I get no error. How can this happen? It does not make any sense.

Darrell

"DarrellK" wrote:

I am trying to debug the following line of code and am not sure why I am
getting an error as everything looks fine to me. The line is:

If IsError(Application.VLookup(Cells(ProductTitleRow, j),
Workbooks(Current_Consolidated_Units).Sheets(Cells (i,
RegionColumn)).Range("L410:BC418"), Consolidated_Offset + Growth * Cells(i,
GrowthColumn), False)) Then

And the values which come up as I move my mouse over each item a
Cells(ProductTitleRow, j)="APPL/PROF SVC"
Current_Consolidated_Units="2006 Access Based Units - Actuals.xls"
Cells(i, RegionColumn)="QC"
Consolidated_Offset=36
Growth=6
Cells(i, GrowthColumn)=1

I am at a loss in understanding what might be wrong as it looks to me as
though all types within all fields are correct. Can you tell me if you see a
problem. Or is there something else that might be wrong?

Thanks.

Darrell

Are you using xl2007? If so "QC" could be interpreted as a column designation.
Is the same sheet active every time the code is run?
You are telling Excel to read the sheet name from the active sheet...
I would qualify every "Cells" with the appropriate workbook/worksheet.
That may or may not help, but it is correct practice.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"DarrellK"
wrote in message
I have noticed that when I type in "QC" in place of Cells(i, RegionColumn)
that I get no error. How can this happen? It does not make any sense.
Darrell

There are two things that would scare me--but shouldn't cause trouble if you're
lucky <vbg.

The first is that that formula is too complex to make it easy to debug (my
problem, not yours). The second problem(s) are the unqualified ranges in that
code.

Cells(producttitlerow,j)
will refer to the activesheet or the sheet that owns the code (if the code is in
a worksheet module).

I'd be more explicit:

Dim MyLookupVal As Variant
Dim mySheetName as string
dim myLookupRng as range
dim myGrowthOffset as Variant
dim res as variant

with thisworkbook.worksheets("sheet9999") 'or with me, or with activesheet
mylookupval = .cells(producttitlerow, j).value
mysheetname = .cells(i, RegionColumn).value
myGrowthOffset = .cells(i, growthcolumn).value

if isnumeric(mygrowthoffset) then
'ok
else
msgbox .cells(i, growthcolumn).address & " doesn't contain a number!"
exit sub
end if
end with

set mylookuprng = nothing
on error resume next
set mylookuprng = Workbooks(Current_Consolidated_Units) _
.worksheets(mysheetname).range(L410:bc418")
on error goto 0

if mylookuprng is nothing then
msgbox "Error with lookup range--check sheet name or workbook name!"
exit sub
end if

res = application.vlookup(mylookupval, mylookuprng, _
Consolidated_Offset + (Growth * myGrowthOffset), false)

'the ()'s around the growth*mygrowthoffset aren't required--I just like them!

if iserror(res) then
msgbox "not found"
else
msgbox "Found it and it's = " & res
end if

=========
But this doesn't explain why it didn't work--but it may help you debug it.

Good luck.

DarrellK wrote:

I have noticed that when I type in "QC" in place of Cells(i, RegionColumn)
that I get no error. How can this happen? It does not make any sense.

Darrell

"DarrellK" wrote:

I am trying to debug the following line of code and am not sure why I am
getting an error as everything looks fine to me. The line is:

If IsError(Application.VLookup(Cells(ProductTitleRow, j),
Workbooks(Current_Consolidated_Units).Sheets(Cells (i,
RegionColumn)).Range("L410:BC418"), Consolidated_Offset + Growth * Cells(i,
GrowthColumn), False)) Then

And the values which come up as I move my mouse over each item a
Cells(ProductTitleRow, j)="APPL/PROF SVC"
Current_Consolidated_Units="2006 Access Based Units - Actuals.xls"
Cells(i, RegionColumn)="QC"
Consolidated_Offset=36
Growth=6
Cells(i, GrowthColumn)=1

I am at a loss in understanding what might be wrong as it looks to me as
though all types within all fields are correct. Can you tell me if you see a
problem. Or is there something else that might be wrong?

Thanks.

Darrell

--

Dave Peterson