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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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:

Forum statistics

Threads
1,176,401
Messages
5,902,840
Members
435,000
Latest member
jmc12

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