match and update vba code

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,036
Hi hope you can help with vba code ease as i am completely stuck. :)

On sheet1 I have 2 comboboxes named ‘name1 and name2’, incell C10 this is where the date (dd/mm/yyyy) is inputted in this formatmanually as this needs to match what is in sheet4 which is the calendar.



I also have a comboxcalled ‘Area’ which will also need to match what is in sheet4 with the area,date and name1 once that info is found on sheet4 then name1 needs to be updatedwith name2 by clicking the update button.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
Can you try to rephrase what you need?

Do it in steps, that will make it a lot more clear, for instance:

1. In Cell C10 i enter a date
2. In combobox name1 I select a name
3. ...
...
X. Now my combobox Nam2 should be updated depending on etc
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,036
Hiya thanksfor your reply back hopefully the below steps make sense ��

Sheet1 I havea……
Combobox1which has a drop down list of peoples names
Combobox2 whichhas a drop down list of Areas in the UK
In C10 cellis where you enter the date manually like dd/mm/yyyy
Combobox3 whichhas a drop down list of Subject like swift swap, change hours, lateness,sickness etc
TextBox1where it updates with the persons date of birth from combobox1
TextBox 3 whereit updates with the persons skill set
ComboBox4which has another drop down list of secondary names of people
Text Box 2 whereit updates with the persons date of birth from combobox4

In sheet2
Column Ahas a list of peoples names
Column Bhas date of birth
Column C is the skill set
Column G isthe Subject
Column I isthe areas

In sheet4

Cell A1,A19, A37, A55 is where I have an area
Cells B1, C1, D1, E1,F1, G1, H1, I1, J1, K1 i have dates from WC 13/07/2019 to 14/09/2019
Cells B19, C19, D19, E19, F19, G19, H19, I19, J19,K19 i have dates from 21/09/2019, 28/09/2019 etc
Cells B37, C37, D37, up to K37 then dates from 05/10/19 etc
Cells B55, C55, D55 up to K55 then dates again for example from01/11/2019
In B4 to K17 this is where names are to be updated under thecorrect date and area
In B22 to K35 this is where names are to be updated underthe correct date and area
In B40 to K53 this is where names are to be updated underthe correct date and area
In B58 to K71 this is where names are to be updated underthe correct date and area

 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
hi patriot,
been looking at the file and what you are trying to achieve. As I understand it you want to use this form to select a suitable person to swap with someone on the schedule.
Would it not be be more logical to start with the date (as a combobox), selecting the dates from the ones on the sheet4 going forward from today? then the details of the person on the schedule for that week are automatically filled. In the right form then only those names with the same skill are shown for selecting.

Alternatively you start with the name of a person on the schedule in the left form, and then only the dates where that person is on shift are selectable. The rest would be the same.

let me know if my understanding is incorrect
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,036
Hi, thank you for the reply, your first option is a great idea, with the combobox for the dates etc, it has to go by area as well though , as different people work in different areas, please can you advise also how would I do the combobox wit the dates, as in sheet 4 the dates are on different lines and are in rows and not columns.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,820
Messages
5,483,095
Members
407,379
Latest member
Bender1964

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top