Variable Declaration issue

[NewToCode]

Hi All,

I'm new to the forum and to code. I've been struggling a variant of the code below. When I try to declare a variable like “cnt” and equate it to “Range(ActiveCell, ActiveCell.End(xlToRight)).Columns.Count” in the code below I get an error- 1004. I've listed both the code that works and does not work below. Can someone explain why the error is occurring. Thanks so much in advance.

Code that works

Please Login or Register  to view this content.

[b][b/]
Code that does not work

Please Login or Register  to view this content.

Regards, Rich

[AlvaroSiza]

For a first post, you did a great job in explaining exactly what the problem was and posed a clear question. Nice work.

Variant is a catch all requiring the most memory and therefore, the least efficient. It has it's uses, but generally speaking, you should try to declare your variables as specific to their use as possible using the applicable object.

In this case, the result of a columns.count would be a Long variable.

What happens when you switch Variant to Long in the 'Code that does not work'?

I would also suggest the following in furthering your education on the subject:

http://www.ozgrid.com/VBA/variables.htm

[NewToCode]

Thank you so much. That works perfectly!! I'll be sure to review the link you suggested.

Rich