Find a specified value from a range of cells in another workbook VBA

[GCC83]

Completely stuck with a project I'm working on - apologies if there is a simple answer, I'm learning VBA as I go along...

I have a cell (K62) on workbook 1 within which which users specify a number between 1-31 (ie days of the month). I need the macro to find the corresponding number within row 6 of workbook 2 (from D6-AH6). I then want to select the matching cell from workbook 2 to locate the correct column.

I would share what I've got so far, but to be frank, I don't have a clue where to start after 2 hours of research via Google and several failed attempts!

Any assistance would be greatfully received, and this is my first post having just registered so please be gentle!

 

[Flona2012]

Hi
and welcome to PCReview,,,
its simple, but i didn't get it right,,, so at Workbook1 there's cell K62 and user will enter number betwee 1-31 (Days of Month)
in workbook2 there is at row 6 (from D-->AH which is 31 cells counted) it will go to column with same value
ex) if he entered K62=4 then it will be cell G6 in workbook2 ???
if yes, you need to retrieve the value inside G6??? and this value will lead to another cell??? and how??? give me example of data in cell like this,,,,

so my questions,
1- is it always Row 6 in workbook2 or does it change???
2- is it another workbook or another sheet you mean???

 

[GCC83]

Hi Fiona,

Thanks very much for responding!

To answer your questions:
1- is it always Row 6 in workbook2 or does it change???
Yes it will always be in Row 6. It will not always be on the same sheet within the workbook though, as there is a different sheet for each month.

2- is it another workbook or another sheet you mean???
It's an entirely different workbook.

In your example, yes the value from K62 in workbook 1 would match the cell in G6 of workbook 2. I then want to select cell G6 so that I can use it as a point of reference for the next part of the macro.

Basically workbook 2 is a 'Holiday Log', and I'd like my macro to look down the correct row for that date (i.e. row G in your example, from G10 down to G80) and extract from the table the names of each person who is either on holiday or off sick on that day (A10-A80). I will also need to pick up the number of hours each individual is on holiday for (G10-G80). The data in G10-G80 would be 7.0 for a full days holiday for instance, or 3.5 for a half day.

I hope that makes sense, I tried to attach a simplified version of the sheet with this post but I do not appear to be able to???

I don't expect anybody to do the work for me though, I'm just taking it a step at a time so I can understand how it all works.