VBA code to Select Cell based on multiple criteria

[ankamshetti.nagaraj]

I'm looking for a vba code to select the cell based on the 2 criteria's (1. search the string in column A and 2. search for a date number in row 4). basically i have a sheet which consists of employee names in column A and numbers (1 to 31, which is formula based) as a header in row 4. I have a userform with combobox & Calendar control. My query here is based on the employee name & date selected in userform, i want to select the cell in sheet for that employee.

For Example: In the image if i want to choose Employee3 and date as 9, macro code should select the J8 cell.

I've attached the sample file & code for reference.

any help here is really appreciated !!

Leave.PNG
Userform.PNG

[Mumps1]

Try:

Please Login or Register  to view this content.

[sintek]

Another option...

Please Login or Register  to view this content.

[ankamshetti.nagaraj]

@Mumps1

Thank you for taking your time to look into my query. I did a testing with the sample code provided. However, i see that the fnd variable is not selecting the desired combobox value choosen, instead it always selected the row 7 value.

could you please look into this and provide me an alternate solution.

thank you.!

[Mumps1]

My pleasure.

[ankamshetti.nagaraj]

Hi,

it is always selecting only one employee name. can you please look into it.

[sintek]

Have you tried post 3 that works with your sample file?

[ankamshetti.nagaraj]

Hi @sintek,

thank you for your time and code. I tried testing with your code, it is selecting the cell next to the found employee name, but in the hidden columns. if you observe there are hidden columns and freezed at column A.

thank you.

[sintek]

Nope, you are mistaken...it works as it should...
Unless you are making use of a different file to the one uploaded in post 1

Are you?

Edit...Mumps1's code also works as it should...

[ankamshetti.nagaraj]

Hi Sinket,

Nope, i am using the same file which i have uploaded. if you observe it is a month on month leave tracker, obviously i will be choosing the current month then in that case, the columns will get hide. so, at that instance if i use the code provided by you, it is selecting the cell adjacent to the employee name found in the january month only.

Mumps1's code is selecting the first employee all the time. please do the needful.

Thank you.

[sintek]

Nope...it does work...
You need to ensure that the calendar selection of userform and sheet calendar is same...
See result from selection...

Result.png

[sintek]

If you do not want to manually make the month change on sheet then this will suffice...

Please Login or Register  to view this content.

[ankamshetti.nagaraj]

Hi Sintek,

I tried your code with month selection as January & it works well, but when i move to the next month by clicking on the arrow button in the sheet, it doesn't give me the expected results. please see the image shared below. I've choose February month in the sheet, 3rd Feb in calendar and Employee 4 ideally it should select the Cell AI10, instead it selects D10. if you observe there are columns which gets hide automatically when ever you click on the right arrow in the sheet.

Attachment 706535

Thanks.

[sintek]

Oh okay, now I see...

As I said Mumps code does work...

Please Login or Register  to view this content.

[ankamshetti.nagaraj]

Hi Sintek,

Thank you, this code worked like charm! Really appreciated. Is there any option to restrict the employee to edit other employee leaves. only my manager should be able to edit all the employee leaves.

Basically, i am looking for if Employee 1 opens this template only he/she should be able to edit/update his/her leaves, rest all employee leave details should be protected for editing. Only my manager should have access to update them.

Thanks.

[sintek]

Is there any option to restrict the employee to edit other employee leaves. only my manager should be able to edit all the employee leaves.

This is a new requirement and has no bearing on this thread...Time for a new one...