Excel VBA Code Request 2

[Kenja824]

I am wanting to create a new Time-sheet for guys at work here to keep track of their hours through the year, but I would like to do something I don't have the foggiest idea how to do in VBA. I was hoping someone could please help me.

I would like to have a macro to attach to the blue button. When clicked, it would copy the four cells in yellow. Look at the date in A1, then paste the info to the four cells next to the same date in the column A of Sheet2.

If there is already something in those cells, I would like it to give a warning and have the user select OK to proceed or cancel.

On the other code, I would like to attach the macro to the yellow button that would do a near opposite. It would look at the date in J1 and find the same date in Sheet2, then copy the four cells next to it and past them in the four cells to the right of the yellow button. K1-N1.

No warnings needed for that one.

Just to be clear, this is just something I am doing on the side and not important or in a hurry. So please dont go out of your way much. Just if you get bored sometime and feel like you want something to do.

 

[SkipVought]

Ken,

1. You don't need VBA to get the values for any date appearing in column A on sheet2.
[tt]
K1: =INDEX(Sheet2!$A:$E,MATCH($J1,Sheet2!$A:$A,0),2)
L1: =INDEX(Sheet2!$A:$E,MATCH($J1,Sheet2!$A:$A,0),3)
M1: =INDEX(Sheet2!$A:$E,MATCH($J1,Sheet2!$A:$A,0),4)
N1: =INDEX(Sheet2!$A:$E,MATCH($J1,Sheet2!$A:$A,0),5)
[/tt]
BTW, I don't care for tables without headings as you have for these five columns on both sheets.

2. Here's some VBA to accomplish your first request...

'
   Dim lRow As Long, i As Integer, nSum as Single, Ans, xl As Application

   Set xl = Application

   With Sheet1
      'Find the row for the date in A1
      lRow = xl.Match(.Cells(1, 1).Value, Sheet2.Columns(1), 0)

      'Test for existing data
      With Sheet2
         nSum = xl.Sum(.Range(.Cells(lRow, 2), .Cells(lRow, 5)))
         If nSum > 0 then
            Ans = MsgBox("This date has values. Overwrite?", vbYesNo)
            If Ans = vbNo Then Exit Sub
         End If
      End With

      'Write values to Sheet2 row corresponding to date
      For i = 1 To 4
         Sheet2.Cells(lRow, i + 1).Value = .Cells(1, i + 2).Value
      Next
   End With

   Set xl = Nothing

From my iPad sans Excel, untested.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[DAVE9999]

this line needed a slight change so it would work:

lRow = xl.Match(CLng(CDate(Cells(1, 1).Value)), Sheet2.Columns(1), 0)

I attached an updated file that should work for you

 

[Kenja824]

Thank-you both, very much!

Hey Skip
I know how you feel. It gives a feeling of unfinished sloppiness when I don't use headers. Usually anything I create on Excel has headers. In this case I haven't actually started creating the real file yet. I am kind of creating it in my head first. Figuring out how it will work and when I decided on going down this avenue, I just created a quick basic file for an example and didn't think about headers.


Thanks again and God Bless!