quick question VBA and arrays

[mountainman88]

I want to quickly put a range of cells containing text into an array called blockstrings. Why does the following not work?:

blockstrings = Worksheets("VBAinputs").Range("a5:a" & blocknameslastrow)

instead i have to use the following to make it work:

Worksheets("VBAinputs").Range("a1").Select
blockstrings = Range("a5:a" & blocknameslastrow)


thanks

 

[Fluff]

Hi & welcome to MrExcel.
There is no reason why your first code shouldn't work. Have you declared "blockstrings" & if so what is it declared as?
Also what is "blocknameslastrow"

 

[mountainman88]

Blockstrings is declared as a variant
blocknameslastrow is a variable, it works fine in second case so shouldnt be an issue

another question.

i have a variables firstrow and lastrow

what is the VBA syntax to select a range of entire rows from firstrow to lastrow##

thanks

 

[mountainman88]

Sub pasta()


Dim startrow As Integer
Dim firstrow As Integer
Dim blocksize As Integer
Dim blockstrings() As Variant
Dim blocknameslastrow As Integer
Dim i As Single


firstrow = 5
startrow = Worksheets("VBAinputs").Range("$D$3").Value
blocksize = Worksheets("VBAinputs").Range("$D$4").Value


blocknameslastrow = Worksheets("VBAinputs").Range("a1048576").End(xlUp).Row


blockstrings = Worksheets("VBAinputs").Range("a5:a" & blocknameslastrow) ****BREAKS HERE****




For i = 1 To UBound(blockstrings)


Worksheets("SVpulls").Range("a" & startrow, "XYZ" & (startrow + blocksize - 1)).Copy Destination:=Worksheets("SVpulls").Range("a" & startrow + (blocksize * i))
Worksheets("SVpulls").Range("a" & startrow + (blocksize * i)).Value = blockstrings(i, 1)


Next i




End Sub

 

[Rick Rothstein]

Blockstrings is declared as a variant
blocknameslastrow is a variable, it works fine in second case so shouldnt be an issue

As Fluff pointed out, there is nothing about your first posted code that should have prevented it from working.

i have a variables firstrow and lastrow

what is the VBA syntax to select a range of entire rows from firstrow to lastrow##

thanks

Rows(firstrow & ":" & lastrow).Select

I would point out, though, that it is rarely necessary to select any cells when writing VB code.

 

[mountainman88]

this is the code working fine:

Sub pasta()


Dim startrow As Integer
Dim firstrow As Integer
Dim blocksize As Integer
Dim blockstrings() As Variant
Dim blocknameslastrow As Integer
Dim i As Single


firstrow = 5
startrow = Worksheets("VBAinputs").Range("$D$3").Value
blocksize = Worksheets("VBAinputs").Range("$D$4").Value


blocknameslastrow = Worksheets("VBAinputs").Range("a1048576").End(xlUp).Row


Worksheets("VBAinputs").Range("a1").Select
blockstrings = Range("a5:a" & blocknameslastrow)


For i = 1 To UBound(blockstrings)


Worksheets("SVpulls").Range(startrow & ":" & (startrow + blocksize - 1)).Copy Destination:=Worksheets("SVpulls").Range("a" & startrow + (blocksize * i))
Worksheets("SVpulls").Range("a" & startrow + (blocksize * i)).Value = blockstrings(i, 1)


Next i




End Sub

 

[Fluff]

You need to remove the () from this line

Dim blockstrings() As Variant

 

[mountainman88]

perfect! so should i only use () when I know the array size? what exactly was the issue?

 

[Fluff]

I never use the () when assigning a range to an array.
Why it worked on the active sheet, but not on another sheet I don't know. It's not something I've come across before.

 

[mountainman88]

more array questions!

setup = Worksheets("Lead").Range("b1:" & Cells(2, setlastcol))

this code isnt working to create an array range. In the example if setlastcol = 12 then it should be adding the range "b1:L2" to the setup array. my syntax is screwy im sure.

thanks!