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.
IsError function from VBA6.3 to v6.0
Anton Rapoport
Matching values are found and the correct cell updated. Non matching values get NEW in the cell.
Brian Gullo
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:
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
Dipesh A.
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.
gorance
I've done that - many thanks for your time Andy; much appreciated.
Steve.
cobain81
Genius ... thank you!
Steve.
meoryou
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.
Phonics3k
Thanks Andy,
vRange covers the whole table of the sheet being checked. i.e. A1:AE2000 (depending on the last row used).
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.
Yeago
We need more information about the variables you are using particularly what information they contain.
So, what is vRange