Basic macro

jjlang

Board Regular
Joined
Mar 21, 2006
Messages
61
Hi, I'm looking for a really basic macro

In sheet 1 I've got a name in B2, date C2, and hours worked in D2.

In sheet 2, I've got names running down Col A, and dates along row 3.

I need a macro that will find the name and date from sheet 1, and input the hours into the relevant cell in sheet 2


Thanks
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
I don't think you need a macro, in B4:
=SUMIFS(Sheet1!$D$1:$D$16,Sheet1!$B$1:$B$16,$A4,Sheet1!$C$1:$C$16,B$3)
or:
=SUMPRODUCT((Sheet1!$B$1:$B$16=$A4)*(Sheet1!$C$1:$C$16=B$3),Sheet1!$D$1:$D$16)
and copy down and to the right.
 

jjlang

Board Regular
Joined
Mar 21, 2006
Messages
61
Thanks for the reply

I dont understand how a formula like that determines which employee I'm talking about.

All I want to do is select an employee from a drop down in sheet 1 cell B2, a date from a drop down in sheet 1 cell C2, and then type in the hours worked in sheet 1 cell D2.

Once I type in the hours, I want to press the macro button that will put those hours into the relevant cell in sheet 2. ie, it will find the employee in col A, and the date in row 3, and then populate the relevant cell.
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
A misunderstanding. Basic you want, basic you got:
Code:
Private Sub CommandButton1_Click()
Dim colm As Range, rw As Range
With Sheets("Sheet2")
  Set colm = .Range("DateList").Find(Range("C2").Value)
  Set rw = .Range("NameList").Find(Range("B2").Value)
  If rw Is Nothing Or colm Is Nothing Then
    MsgBox "Failed"
  Else
    .Cells(rw.Row, colm.Column).Value = Range("D2").Value
    MsgBox "Success"
    Range("B5:D5").Value = Range("B2:D2").Value
    Range("B2:D2").ClearContents
  End If
End With
End Sub
and see file here where it's working.
 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,326
Messages
5,510,601
Members
408,806
Latest member
Hunlight

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top