Reference a column from a variable

[Rob K]

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

[Ed_Collins]

Do you mean something simple, like this:

Sub Find_Job()
'===========
Dim user_chosen_job As Integer
Dim column As Integer
Dim job As Integer

user_chosen_job = 33    ' for example.  Store whatever job the user chooses in this variable.

'loop through your columns, looking to find what column this Job # is in
For column = 10 To 54
   job = Val(Mid(Cells(9, column), 5, 2))
   If user_chosen_job = job Then
      MsgBox ("We found the job!  The column for this job is " & column)
      Exit For
   End If
Next column

End Sub

[Rob K]

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.

[skywriter]

I'm still not clear what you want to do. But using some of what you have in place, rngData will be the data below the job number, in rows 10 to 47. You could put them in an array and put column F in an array and loop through them very quickly perhaps building another array to put into your template sheet.

Dim colNumber As Long
Dim rngData As Range
colNumber = Rework.Cells(1, Int(JobNumber)).Column
Set rngData = Overview.Range(Cells(10, colNumber), Cells(47, colNumber))

[Ed_Collins]

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.")

template_row = 2

For Row = 11 To 52
  If Cells(Row, Column) <> "Done" And Cells(Row, column) <> "N/A" Then
     template_row = template_row + 1
     JobSheet.Cells(template_row, 1) = Cells(Row, column)
  End If
Next Row

(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]

I don't know why I didn't think of this in the first place. This would probably be the fastest and easiest. Modify to fit your needs.

colNumber = Rework.Cells(1, Int(JobNumber)).Column
With Overview.Range("A10:BF100")
    .AutoFilter Field:=colNumber, Criteria1:="=x"
    Overview.Range("F11:F100").Copy JobSheet.Range("A3")
    .AutoFilter
End With

[Rob K]

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...

[Rob K]

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

[skywriter]

Rob thanks for the rep. points and feedback.

Please click the Thread Tools drop down box above your first post and choose solved.