Reference a column from a variable

Hi there,

I have a sheet here that has lots of different data in it, I have a column for part numbers, and a few more columns with dates amongst other things, but the last 45 columns form a matrix of jobs1 to 45. Then the rows make up items to be covered and there are approximately 37 rows of this.
In this matrix there is either an "x" or "Done"

Now the titles of Job1 to Job 45 are now a named range and I can now find one of the job numbers, but I can't work out how to access the column.

Basically, the user chooses a job number, and then the macro will pick the item details depending on whether they have been done or not, and copy them to the next sheet to be printed out. But I can't work out how to get a variable that I can use to loop through the column to assess if it is required or not. What is the most efficient way to do this?

The sheet is attached as I realized that this is too difficult for me to explain.

Kind regards
Rob

Do you mean something simple, like this:

Please Login or Register  to view this content.

Hi there,

If you click on the button on my sheet and type any number from 1 to 45, you will see that I have successfully located that. What I am after is to then copy all the details in column F to the next sheet that are flagged up with a red X in the matrix, and I am struggling to find the most efficient way.

Sorry, I honestly didn't see the button. (It didn't look like a button on my small laptop screen, at the 70% screen size the sheet was set at.)

So now that you found the column, you want to loop through the ROWS to assess if it is required or not. Maybe this is what confused me earlier... since you mentioned you wanted to loop through the COLUMNS.

Something like this might work, assuming you want to ignore anything with a "Done" in the cell and ignore anything with an "N/A" in the cell.

(Recall we already have a number for the variable "column.")

Please Login or Register  to view this content.

(Obviously, change the variable names I used to match your own. Also, this copies the data into column A of the Template Worksheet. Maybe you want the data copied into Column B, for example.)

Skywriter, thank you, I was thinking about using an array, as this is how I would have done something similar in Matlab or Python, but the whole spreadsheet thing adds a different bent on the whole affair.

Thanks very much for all your input, Ed, I was going to use your version, as that is spot on with what I was after, but i am going to try the autofilter version of Skywriter's, as this makes good use of the code excel is written in, which I am assuming will always be somewhat quicker than a scripting language.

Tell you what I will try both and give feedback as to which one was the most efficient. Watch this space...

Auto filters won. It works perfectly, thank you all for you help in this matter.