SuzukiBandit


Hi All, I'm having problems with a small VBA script that I wrote in v6.3 but need to implement in 6.0. Excel 2002 -> 2000.

The following code works fine in the later version:

For i = 2 To LastRow
' select the next row based on loop
vString = "A" & i

If IsError(Application.VLookup(Range(vString).Value, vRange, 31, False)) Then

Y = "NEW"
Else
Y = Application.VLookup(Range(vString).Value, vRange, 31, False)
End If
' write the result to the active workbook
Cells(i, 31).Value = Y
Next i

But won't work in the earlier version as there's a type mismatch at the If Iserror ... line.

Any ideas why or thoughts on a workaround

Thanks,

Steve.




Re: IsError function from VBA6.3 to v6.0

Andy Pope


Hi,

We need more information about the variables you are using particularly what information they contain.
So, what is vRange







Re: IsError function from VBA6.3 to v6.0

SuzukiBandit

Thanks Andy,

vRange covers the whole table of the sheet being checked. i.e. A1:AE2000 (depending on the last row used).

OldFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select last week's file")

Workbooks.Open (OldFN)
OldLastRow = Range("A65536").End(xlUp).Row ' last used row of old sheet
vRange = Range("A2", Cells(OldLastRow, 31))

This all works fine on the laptop! Briefly, the aim is as follows:

OldFN is last week's list of outstanding cases that were allocated to specific people. Do a Vlookup to maintain those cases that also appear on this week's list (i.e. didn't get paid) by returning column 31 from vRange (the location of the handler ID) or put "NEW" in the cell to indicate the case needs allocating.

Is that a bit clearer Sorry, really should have included that snippet earlier.

Steve.






Re: IsError function from VBA6.3 to v6.0

Andy Pope

I can not reproduce an error using your code and my data.
Matching values are found and the correct cell updated. Non matching values get NEW in the cell.







Re: IsError function from VBA6.3 to v6.0

SuzukiBandit

It does on my laptop : works fine - but on a desktop using Excel 2000, I get a Type Mismatch on the If IsError line.

I'm totally stumped!

Thanks for trying!

Steve.





Re: IsError function from VBA6.3 to v6.0

Andy Pope

It did try it in xl2000 and xl97 without error.

If you want to email a test file that fails I will take a look.

Check my profile and use the email address on my website.





Re: IsError function from VBA6.3 to v6.0

SuzukiBandit

I've done that - many thanks for your time Andy; much appreciated.

Steve.





Re: IsError function from VBA6.3 to v6.0

Andy Pope

See this msdn article,
http://support.microsoft.com/default.aspx scid=kb;en-us;177991

Especially part F
The maximum number of elements in the array is limited by available memory or the Excel worksheet maximum size (65536 rows X 256 columns). However, the maximum number of elements in the array that you can pass to Excel using the Excel Transpose function is 5461. If you exceed this limit, you receive the following error message:
Run-time error '13':
Type Mismatch

So to get around the problem short term load both all data and the first column only into arrays.
Use the MATCH function instead to locate the row and the extract the data from the complete array. Obviously this too will error in xl2000 if there are more than 5461 rows.
If that is the case then maybe keep the workbook open and use Range objects to do the VLOOKUP.

Revised code snippet.
Workbooks.Open (OldFN)
OldLastRow = Range("A65536").End(xlUp).Row ' last used row of old sheet
vDataRange = Range("A2", Cells(OldLastRow, 31))
vRange = Range("A2", Cells(OldLastRow, 1))

' close the selected workbook
ActiveWorkbook.Close False, OldFN

' get the last row number on current sheet
LastRow = Range("A65536").End(xlUp).Row

' do a vlookup between the current and old sheet for allocated cases on all rows
For i = 2 To LastRow
' select the next row based on loop
vstring = "A" & i

If IsError(Application.Match(Range(vstring).Value, vRange, 0)) Then

Y = "NEW"
Else
Y = vDataRange(Application.Match(Range(vstring).Value, vRange, 0), 31)
End If
' write the result to the active workbook
Cells(i, 31).Value = Y
Next i







Re: IsError function from VBA6.3 to v6.0

SuzukiBandit

Genius ... thank you!

Steve.