Setting a range based on variables

[DFrank231]

I am trying to set a range to be manipulated using 4 integer variables that denote the top of the range cell row/column and the bottom of the range row/column. The range will always be one column wide but will be a varying number of rows long. I have tried various similar situations I have found on various forums, but I always seem to end up with a ‘Run-time error ‘1004’: Method ‘Range;’ of object ‘_Global’ failed. I have tried a few things and now I think I finally am ready to ask for help… how can I set variable rng to be “[rTop][cTop]:[rBottom][cBottom]” (cell example – “A34:A54”)

Please Login or Register  to view this content.

[p24leclerc]

I don't know where you are heading with this code but I doubt you'll ever get anywhere. Sorry to be that hard on you. Here are some explanations:

First of all, you must SET a range as in:

Please Login or Register  to view this content.

remark the use of the function Cells which needs 2 parameters (rows and columns) as compared to the Range one which needs an address (like "A2").

Not sure at all but is it that you want to ask the operator to mark the limits of that range? Why? Don't you know where it starts? Ends?

How does you sheets looks like? It would hlep us to help you on that.
Regards

[TMS]

You could use:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Note that in your Dim statements, only the last/rightmost variable is defined. The others are all variants. It is usual to define row variables as Long.


Regards, TMS

[DFrank231]

You could use:

Please Login or Register  to view this content.

Regards, TMS

This is giving me a type mismatch. It essentially is trying to set rng as Cell(value in top cell, value in bottom cell) isntead of Cell (cell directory of top cell, cell directory of bottom cell)

Below is a picture of different points of the debug that shows what it is doing. here are my declarations:
Dim rTop As Integer
Dim rBottom As Integer
Dim cTop As Integer
Dim cBottom As Integer

Dim rng As Range

vendor mismatch.png

[mikerickson]

I think that TMShucks meant

Please Login or Register  to view this content.

Also since there are more rows in an Excel sheet than there are integers, all those variables should be dimmed as Long.

[TMS]

Please Login or Register  to view this content.

[DFrank231]

Thank you everyone. changing the variables to long, changing the first cell to Range and using rng.address fixed every problem.

[TMS]

You're welcome.