Item Selection within a Macro

[cstewart]

I am a teacher and I set up a tool to take exported student test responses and turn them into a table with "offset".

I have set up macros for everything but selecting the number of questions to copy. "Shift + arrow" takes me to the end of the record (of 50) but most test have less than that.

I have tried using "relative reference" and a cell value in the macro steps, but i haven't been successful.

I have spent a lot of time looking for an answer, but I don't even know the right words to use. Could somebody tell me where to look? Thanks.

 

[mirabeau]

There's a variety of methods within macros to find the last used cell in a column, a range, the whole worksheet, or whatever.

Could you perhaps be a bit more specific about just what you're looking for?

 

[cstewart]

I will try, I made a table to take data (student test item responses) from a few columns and turn it into a table.

The table has 50+ columns. But I need to take different numbers of columns based on the number of questions in the test.

I have tried Shift+right, but I number of items changes and Shift+right always takes me to the end of the table.

Even if you could just get me in the right direction by telling me some of the terms I could look for on-line, thanks.

 

[hutchisd]

I usually assign this to a variable in vba:

Application.Counta(Range("A:A"))

It counts the number of non-blank cells in column A. If you have a header row, just subtract 1 from the variable.

Oops, just saw your last post and realized this won't help you.

 

[cstewart]

This is the formula that I entered to make the table from the columns.

"=OFFSET($E$2,(MAX($D$2:$D$50)*(ROW()-ROW(W$3)))+(COLUMN()-COLUMN($K4)),0,1,1)"

It always produces a "0" or a "1", so even if a column is unused, it will have a number in it.

 

[cstewart]

Is there a way to just tell the macro to start at the selected cell and select x number more cells to the right? Then I could just use a cell value for x?

 

[hutchisd]

Is there a way to just tell the macro to start at the selected cell and select x number more cells to the right? Then I could just use a cell value for x?

Try this when cell A1 contains the number of columns you want to select in row 3:

Dim i As Integer
x = Cells(1, 1)
Range(Cells(3, 1), Cells(3, x)).Select

 

[mirabeau]

Is there a way to just tell the macro to start at the selected cell and select x number more cells to the right? Then I could just use a cell value for x?

With any selected cell, this line in your macro will retain your selection and select x more cells to the right

'say x = 3
Selection.Resize(, x + 1).Select

although I can't really see why you should want to do this.

Generally there's no need to select cells or ranges to do further operations or analysis on them. Usually their location is identified in other ways.

 

[cstewart]

That was awesome, thanks. The problem is solved, yay!

 

[cstewart]

Thanks mirabeau, I tried the idea from hutchisd, and it worked well. Thanks for you time helping me.