Choosing sheet by name

Question

I have a code in VB:

srcClm2 = Sheet45.Range("B2:B" & lastRowSrc)

But I wanna choose sheet by name. I try the different ways, for example:

Sheets("Cycle").Range("A2:A" & lastRowSrc)

But I have got error: "Type mismatch."

EDIT: This is fragment of my code:

Dim srcClm1(), srcClm2()
Dim lastRowSrc As Long

lastRowSrc = Sheets("Line_Cycle_working_sheet").Cells(Rows.Count, 1).End(xlUp).Row


srcClm1 = Sheets("Line_Cycle_working_sheet").Range("A2:A" & lastRowSrc)

srcClm2 = Sheets("Line_Cycle_working_sheet").Range("B2:B" & lastRowSrc)

Answer 1

You need to declare variables srcClm1, srcClm2 as Variant.

Dim srcClm1 As Variant, srcClm2 As Variant
Dim lastRowSrc As Long

lastRowSrc = Sheets("Line_Cycle_working_sheet").Cells(Rows.Count, 1).End(xlUp).row
srcClm1 = Sheets("Line_Cycle_working_sheet").Range("A2:A" & lastRowSrc)
srcClm2 = Sheets("Line_Cycle_working_sheet").Range("B2:B" & lastRowSrc)

---

Actually you could skip As Variant and declare it just like this:

Dim srcClm1, srcClm2

since Variant type is default. However, it is good practice to add As Variant to make it clear that you declare it as Variant by purpose and not by mistake.