error on lastrow for multiple columns

[pclaesen]

I'm trying to write some listbox values to variable worksheets (product name from listbox = worksheet name).

The following code returns an error 13 type mismatch on the lastRow = ws.Columns("b:d").Find("*", ws.Cells(1), , , 1, 2).Row + 1 part.

Please Login or Register  to view this content.

I used this lastro formula in another part of my workbook where it works great (also see this topic)

Basically the columns that need to be checked for a value are B-C-D.


Could anyone please help me out?
Thanks in advance.

[joe31623]

Your issue may possibly be solved by simply declaring: Dim lastrow As Long.

I don't see a variable declaration for lastrow. Is it a global variable or are you using late binding (in other words, did you declare: Option Explicit on the top of your module, which is recommended)?

Another thing that I would check is that if whether or not lastrow is declared as an integer or a Long (upon late binding by looking at the Locals window in the VBA editor, or elsewhere). Many variables that are declared as integers are not large enough to store the last row of an excel document and must be declared as Long.

[Trebor76]

Hi pclaesen,

Though I'm sure jindon's code is just fine, this is what I normally use:

Please Login or Register  to view this content.

This will work as long as there's actually some data in the ws tab (though I can't see where you've declared a 'ws' variable as a worksheet ) in any column the code is referencing - B to D in this case. If there's not a Run-time error 91 error message will result.

HTH

Robert

[Trebor76]

Your issue may possibly be solved by simply declaring: Dim lastrow As Long.

No the problem still occurs I'm afraid

Another thing that I would check is that if whether or not lastrow is declared as an integer or a Long (upon late binding by looking at the Locals window in the VBA editor, or elsewhere). Many variables that are declared as integers are not large enough to store the last row of an excel document and must be declared as Long.

You will get a different error i.e. Run-Time Error '6' Overflow in this case when the maximum limit an integer can hold 32,767 is exceeded by a higher row number.

[pclaesen]

Hi pclaesen,

Though I'm sure jindon's code is just fine, this is what I normally use:

Please Login or Register  to view this content.

This will work as long as there's actually some data in the ws tab (though I can't see where you've declared a 'ws' variable as a worksheet ) in any column the code is referencing - B to D in this case. If there's not a Run-time error 91 error message will result.

HTH

Robert

Hi Robert,

the worksheet name (variable) is the productcode I enter in the userform and listbox:

Please Login or Register  to view this content.

Your code works, thank you very much! I've been searching for nearly 4h on this :-D.

[Trebor76]

the worksheet name (variable) is the productcode I enter in the userform and listbox:

Without declaring ws as a worksheet variable it's being declared as a variant - the most expensive type of variable in terms of memory.

Your code works, thank you very much! I've been searching for nearly 4h on this

I'm glad to hear that and you're welcome

Robert

[Trebor76]

the worksheet name (variable) is the productcode I enter in the userform and listbox:

Without declaring ws as a worksheet variable it's being declared as a variant - the most expensive type of variable in terms of memory.

Your code works, thank you very much! I've been searching for nearly 4h on this

I'm glad to hear that and you're welcome Thanks also for the rep

Robert

[pclaesen]

Without declaring ws as a worksheet variable it's being declared as a variant - the most expensive type of variable in terms of memory.



I'm glad to hear that and you're welcome Thanks also for the rep

Robert

Robert,

So I have to (just brainstorming here):

Please Login or Register  to view this content.

to speed up things?

[Trebor76]

Declaring all your variables will improve efficiency but there maybe other factors at play if the performance is slow.

For the ws variable you'd use this:

Please Login or Register  to view this content.

Robert