Object definition and structure, with variables

[Neal Zimm]

I tried to define an object representing a variable number
of rows that are to be cut and inserted into another sheet.
I got a type mismatch error on the code below.
In the module where this code is going to go,
the variables fromrow and to row are available and
'working'. I am a novice at 'object definition'.
Help will be appreciated.

Dim insertrange As range
Dim fromrow, torow As Integer

fromrow = 21: torow = 40

Set insertrange = Rows("&fromrow:&torow")

' Rows("21:40").Select trying to 'replace' this line of code
insertrange.Select 'with this line of code
Selection.Cut
Sheets("new").Select
range("A41").Select
Selection.Insert Shift:=xlDown

 

[Bob Phillips]

Try

Set insertrange = Rows(fromrow&":"&torow)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[NickHK]

Neal,
Small point, but fromrow is declared as a Variant, whilst torow is an
Integer, which is not what you mean, I think.
You need to explicitly state the type of each variable, otherwise it
defaults to a Variant.
i.e. Dim fromrow as Integer
Dim torow As Integer

NickHK

 

[Neal Zimm]

Wow, I thought that all variables in one row of code took on the meaning of
the 'as phrase' at the end of that line of code. (other languages do that).
Just to make 'deadly' sure, that means that a dim statement can have only one
variable following it, and that little fact must make for some long lists in
modules where there are a lot of variables. Please confirm, thanks, Neal

 

[Neal Zimm]

Yup, I misread the example in the Excel 'help' arena. The very first example
they give is Dim var1, var2 and then they talk about
both of them being variants.
Later in the example, where more than one var is in one line of code, there
IS an 'as' phrase following EACH var. Thanks much.

 

[Patrick Molloy]

each vaiable in a DIM statement must be specifically assigned a type or it
defaults to VARIANT

eg

DIM Index as integer, text as String, something, counter as long

in this statement the variable called 'something' will default to Variant.
Its better to be explicit in your code though, so this is better

DIM Index as integer, text as String, something as Variant, counter as long

this is the same as:
DIM Index as integer
DIM text as String
DIM something as Variant
DIM counter as long

Personally I go for the latter, since for me anyway, I find it easier to
debug or alter when I revisit months later.

 

[Bob Phillips]

And if you will store a row number in the variable, better to use a type
Long, or it might overflow.

--

HTH

RP
(remove nothere from the email address if mailing direct)