Range Syntax

[jwise]

I'm getting object errors on this statement:

r1rng = Range(ActiveSheet.Cells(i, 2), ActiveSheet.Cells(i, k)).Address( _
RowAbsolute:=False)

The statement was copied from another RUNNING macro except variable names are different and the row and column variables are different.

This statement is building a range for everything outside of column 1 in a variable-sized worksheet. I is first row and k is last column.



Thanks

[SamT]

Which of the 999,999,999,999,999 errors are you getting? By text please, not by error Number

[jwise]

I apologize for my indiscretion.

"Run time error 91."
"Object variable or with block variable not set."

[SamT]

Dim r1rng As String 'String or Variant Required. String is more code specific.

r1rng = Range(ActiveSheet.Cells(i, 2), ActiveSheet.Cells(i, "k")).Address(RowAbsolute:=False)
'Double Quotes required around Cell Column Name Strings (Cells(i, "k"),)
'  Unless k is a number variable

If k is not a number Variable then this is equivalent and faster

r1rng = "$B" & i & ":$K" & i

If i = 2, the result is "$B2:$K2", unless you're using R1C1 Addresses. I don't like or know R1C1 addressing.

[jwise]

K is indeed a number (a Long, and the last data column).
"r1rng" is defined as a Range. This too was a source of grief. The goal was to define a range from the second column to the last. This range is generated on every sixth row because the first of the six rows is actually a header row identifying the data in the five following rows. This single row range is then union-ed into a "big" range. Once I'm out of the loop, I select these cells and exit. The next subroutine processes selected cells. Since there are (in this case) 720 rows and 43 columns, it is exceedingly difficult to select the cells with the mouse while scrolling back and forth horizontally and vertically. Hence the code. I've seen several different ways to create this range, but could get none of them to work. I have no clue what is the most efficient way to do this, so I'd appreciate a tip!

[SamT]

Your code is assigning the address of the Range to the variable Var = Range(...).Address. In VBA you need to use "Set" o assign an Object to a variable
Set Var = Range(...)

I have no clue what is the most efficient way to do this, so I'd appreciate a tip!

Depends on the processing you do to the Range(s). you have about 120 Range parts (5 Rows each) Or about 24Kcells to process. I would say that using an array(s) of one type or another will be best

Describe the Processing, please.

[Paul_Hossler]

Not knowing your desired processing, this is a guess and some made up examples

Dim r1rng as Range, rData as Range, rInter as Range

Set rData = Activesheet.UsedRange

k=10 ' last column
iLast = 100 ' last row


For r = 2 To iLast
    'assuming that I and k are 'in bounds'
    Set r1rng = Range(ActiveSheet.Cells(i, 2), ActiveSheet.Cells(i, k))

    Set rInter = Intersect(r1rng, rData)

    'process, process, process ... the intersection

Next I

There are more elegant ways to do this, but we'd need to know about the overall macro and processing

[jwise]

I have another macro which actually does the work, but it processes "selected" cells. The purpose of this macro is to select these cells. The cell values are all abbreviations, but there are two sets of abbreviations. Thus this other macro looks at the selected cells, and does VLookUp to get the replacement abbreviation. So the total logic of the macro at hand is to select all the cells which are these abbreviations. The number of columns changes from time to time, thus I dynamically determine the number of columns (k in this case) and try to create a range. In this case, it should look like "Set r1rng = Range(ActiveSheet.Cells(1, 2), ActiveSheet.Cells(1, k)),(RowAbsolute:=False). Maybe the "Set" was my big issue all along. Kind of surprising since I've written 10,000 LOC.
"Intersect" Not familiar with this one. I'll have to look that up. Leaving out the "Set" is way up there on "my dumb things I did" list.

[Paul_Hossler]

Set r1rng = Range(ActiveSheet.Cells(1, 2), ActiveSheet.Cells(1, k)),(RowAbsolute:=False). Maybe the "Set" was my big issue all along. Kind of surprising since I've written 10,000 LOC.
"Intersect" Not familiar with this one. I'll have to look that up. Leaving out the "Set" is way up there on "my dumb things I did" list.

Maybe, but there's no .Address and therefore no RowAbsolute:= involved


Just

Set r1rng = Range(ActiveSheet.Cells(1, 2), ActiveSheet.Cells(1, k))

or the way I'd do it ...

Set r1rng = ActiveSheet.Cells(1, 2).Resize(1, k-1)

[SamT]

For Speed,I would assign all the Name/Abbreviation pairs to a Scripting Dictionary, assign the entire table from Row 2 down to an array.

Use a For loop from i = 2 to last Row
for j = 1 to k
If j Mod 6 <> zero then
If Exists Dictiionary(Array(i,j) Then
Array(i, j) = Dictionary(Array(i,j)

Let Sheet Cells 1,2 .Rezise to Array = Array

24K cells will take about 3 seconds

No, that is not code. It's barely an algorithm, more of a hint of an algorithm.

[jwise]

I have marked the thread as solved. I believe the main issue was my "r1rng =" statement which should have been "Set r1rng =" as I was advised. The problem I have with this is that the code ran for 2 years (albeit only once each year); I must have modified or used some other code instead. Selecting all those cells manually is an abomination! And I do know that "Set" should be used with objects...
Thanks very much for the code snippets-- I appreciate snippets in code or pseudo-code. I also freely admit that I don't understand "RowAbsolute." I just copied the code from the internet and changed the variable names. I also appreciate the "scripting dictionary" suggestion because I use this technique in many different macros. I can never get away from "efficiency" as it was part of my raison d'etre as a mainframe assembler guy. OOP is still heresy to me. Thanks so much for your patience and professionalism.

[SamT]

I can never get away from "efficiency" as it was part of my raison d'etre as a mainframe assembler guy. OOP is still heresy to me.

This thread may interest you. I'm developing a VBA OOP style app for the OP. And tutoring on same. With a little help from my VBAX friends la la la.
Bet Angel and Excel

[V.B.A.02]

you should define r1rng as string as you are storing range address in it.. add following line before you use r1rng

"Dim r1rng as String"

[SamT]

r1rng is an object