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

 

[Norie]

Try this.

With Worksheets("Lead")
    setup = Worksheets("Lead").Range("b1", .Cells(2, setlastcol))
End With

PS That's not really an array question, more to do with how you are referencing things, i.e. ranges.

 

[mountainman88]

If setup(1, i) = "Total" Then Range("TotalTemp").Copy Worksheets("Lead2").Column(i + 1) Else Range("DataTemp").Copy Worksheets("Lead2").Column(i + 1)

also this code isnt working, to copy named ranges TotalTemp or DataTemp to coumns based on the array contents.

thanks!

 

[mountainman88]

Try this.

With Worksheets("Lead")
    setup = Worksheets("Lead").Range("b1", .Cells(2, setlastcol))
End With

PS That's not really an array question, more to do with how you are referencing things, i.e. ranges.

hey thanks, do i really have to use a 'with' statement? there must be a more efficient way?

 

[Fluff]

What makes you think that With Statements are inefficient?
This would be even better

With Worksheets("Lead")
    setup = .Range("b1", .Cells(2, setlastcol))
End With

Your post#12 is a totally different question, so please start a new thread.

 

[Norie]

Here's the same code without the With.

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

Not sure if it's any more efficient but it is longer.

 

[mountainman88]

Here's the same code without the With.

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

Not sure if it's any more efficient but it is longer.

Hey thanks, I’m interested to know why VBA needs to qualify the .cells when it already has worksheets(“lead2”)?

 

[mountainman88]

What makes you think that With Statements are inefficient?
This would be even better

With Worksheets("Lead")
    setup = .Range("b1", .Cells(2, setlastcol))
End With

Your post#12 is a totally different question, so please start a new thread.

It seems like an extra step just to include a variable in a column reference, I assumed there would be a syntax that could be used on the same line?

will start a new post for #12

Thanks!

 

[Fluff]

Any range that is not fully qualified will act on the Active sheet, so with this

setup = Worksheets("Lead").Range("b1:" & [COLOR=#0000ff]Cells(2, setlastcol)[/COLOR])

It's looking at the "Lead" sheet range B1, but the part in blue is still looking at the active sheet because it hasn't been qualified with the sheet reference.

 

[Rick Rothstein]

It seems like an extra step just to include a variable in a column reference,

When you have two or more references to the same object, the With statement block is more efficient as VB only has to evaluate the object once and then automatically apply it to all the properties or methods that reference. Otherwise, if you repeat the object at each property or method that references it, you force VB to evaluate the object each time. Also, when you have multiple references to the same object, the With statement block cuts down of typing considerably which, once you get used to reading a With block, makes reading the code sometime in the future when you need to modify it much, much easier.