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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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:

Watch MrExcel Video

Forum statistics

Threads
1,133,377
Messages
5,658,460
Members
418,446
Latest member
JValencia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top