Dynamic variable assignment VBA

Question

Ok so I have been struggling to figure out what the problem is with my assignment of my variables 'aveCellAdrs' & 'q'. Each suffers from the error: method 'Range' of object 'Global_' failed.

What I want the variables 'q' & 'aveCellAdrs' to do, is refer to the dynamic variable 'k' (k changes each time the loop goes through itself) and then get their new range from that.

Heres is my code:

             Dim i As Integer
             Dim j As Integer
             Dim k As Range
             Dim q As Range
             Dim tableRange2 As Range
             Dim total As Double
             Dim table2average As Double
             Dim aveCellAdrs As Range


For i = 1 To 20
    Set k = Range("B73").End(xlUp).Offset(i, 0) 'This finds the top-most cell as a starting point
            Set aveCellAdrs = Range(k).End(xlToRight)  'Used to enter the average found in the loops below to the correct cell
                 Set q = Range(k).End(xlToRight).Offset(0, -1)  'This finds the right-most cell in the row
                      Set table2Range2 = Range(k, q)  'Identifying the dynamic range to which we will calculate each rows' average

Hopefully this makes sense. Here is the attached screenshot of the code block:

enter image description here

How and where did you declare the three variables? Also I don't see any aveCellAdrs variable in the code you posted. — BruceWayne
– BruceWayne, Commented Jul 7, 2017 at 16:26
Ok I think I cleaned it all up. Sorry, I didn't realize I had messed with it, I'm still kind of new to StackOverFlow :( — Matthew
– Matthew, Commented Jul 7, 2017 at 16:50
if k is defined as a range, then you just use Set aveCellAdsr=k not Set aveCellAdsr=Range(k) — braX
– braX, Commented Jul 7, 2017 at 16:52
Yes but I want the cell that Range(k).end(xlToRight) finds. If I set aveCellAdrs simply to k, then aveCellAdrs just holds the value of k which I don't need. Edit: Oh, do you mean simply do Set aveCellAdrs = k.end(xlToRight)? — Matthew
– Matthew, Commented Jul 7, 2017 at 17:01
Sorry yeah, I'm still really new to this. I will definitely read up and implement! — Matthew
– Matthew, Commented Jul 7, 2017 at 17:22

C Dieguez · Accepted Answer · 2017-07-07 17:35:03Z

braX is correct, in VBA range is an object, not a datatype. When you enter:

Set table2Range2 = Range(k, q)

what I think you're trying to do is to pass the left-most and right-most limits of the range to range() in order to set the range for table2Range2. If that's the case, you want to pass the cell addresses (i.e. B2:E2), which is what range() expects to receive. What you're actually doing in your code above is trying to pass entire range object, which is causing range() to throw an error.

Here's what I would do: start off by setting a range as reference point, create some string variables to hold the cell addresses at the corners of the range you're trying to create. Build that into a string and pass that to range() to set tablerange dynamically for each iteration of your loop. Once you set a range you can use the .address method to grab the cell address. See below:

Dim i As Integer
Dim startingPoint As Range
Dim rngStart As String
Dim rngEnd As String
Dim tableRange As Range



For i = 1 To 20
    Set startingPoint = Range("B73").End(xlUp).Offset(i, 0)
    rngStart = startingPoint.Address 'This finds the top-most cell as a starting point
    rngEnd = startingPoint.End(xlToRight).Offset(0, -1).Address 'This finds the right-most cell in the row
    Set tableRange = Range(rngStart & ":" & rngEnd)  'Identifying the dynamic range to which we will calculate each rows' average
Next

Collectives™ on Stack Overflow

Dynamic variable assignment VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related