Use counter value with letter to get dimmed range and use to get new value

[Aaron092]

Not sure if I've explained that well enough and kept it short, so I'll explain better:

At the beginning of my sub, I have a loop which gets the columns of four cells with specific text in them.
Later in the same sub, I need to offset to each of them consecutively.
The way I'm doing this at the moment is:

Please Login or Register  to view this content.

but I get Error 13 on 'ActiveCell.Offset(0,gcol).Select'

Anyone know how I can get around this please?

[HaHoBe]

Hi, Aaron092,

not clear what you are after...

One guess might be to avoid Selects of all kinds:

Please Login or Register  to view this content.

Ciao,
Holger

[TMS]

In

Please Login or Register  to view this content.

gcol needs to be a number. Clearly, it will be a text string with the value "q" plus a number from 1 to 4.

What is in the cells? What are you trying to achieve?

Suggest you post a sample workbook.

Regards, TMS

[Aaron092]

Hi both

Thanks for your replies.
I've attached a sample to this for you.
EESR(DEV).xlsm

What I'm trying to achieve is offset to each of the orange cells column in the workbook, then at the bottom of the column (as it will have a load of data in it) put some values.
This part I've done, but each of the orange cells (Questions) move location depending on whats in the first few columns.

So what I need to do is find each last question in each set of questions (four in total), get their columns, then offset to the columns later in the sub (where the blue is).

It's just that loop I'm having a problem with - getting the final question column letters, and using these to offset.

[HaHoBe]

Hi, Aaron092,

my advice for the person who sampled the code:

Please Login or Register  to view this content.

Range("AH1") shows trailing blanks - is that by defauilt?

To collect the columns for the searched items you may use something like

Please Login or Register  to view this content.

Ciao,
Holger

[TMS]

If I'm honest, I don't really understand your code or what you plan to do. Respect to Holger.

I think that I have done something very similar ... The code creates an array of questions, then loops through it to determine first the column and then the last row of the range relating to the question. It offsets it (to reach the blue area) and resizes it (to the size of the blue area) and then, for the sake of the demonstration, puts the question in each of the cells in the range. No sheets, ranges or cells are selected.

Note that you have an inconsistent number of trailing spaces on questions 2, 3 and 4.

Please Login or Register  to view this content.

Regards, TMS

[Aaron092]

The inconsistency is the bane of my life, but it has to be there

Thank you both - I think both of you have posted something that would do what I'm looking for.
Sorry for any confusion

[TMS]

You're welcome. Thanks for the rep.