Help with Macro please!

[Rob Gould]

Hi all,

I am trying to get a macro to work in Excel. Basically, this is what I
want it to do:

A user will enter a date into a cell, say A1 on Sheet 1. They then
enter some sales figures in cells below this. These figures are picked
up on Sheet 2 in Row 1 so that they are all in a row. Below this row I
have all the days of the year. Once the user has finished entering the
figures, the macro needs to copy the row of data on Sheet 2 and paste
Values Only to the row corresponding to the date entered. This way, I
am building up a year of data. I don't want users entering data
directly onto Sheet 2.

Doing this manually, I copy the date entered on Sheet 1, go to Sheet 2,
CTRL+F to Find, paste the date into the find what box, find the cell
with the date, close the find box, move 1 cell to the right and enter
"qq" (could be anything really). Then I copy the row of data in
row A of Sheet 2, Find "qq" and paste values only.

When I incorporate this into a macro, however, it stops during the find
the date section of the process.

Question 1: Is there a far better way of doing this?
Question 2: If not, how do I solve the problem?

This looks pretty confusing, I'm sure, but if you have the time to
try to follow it, I would appreciate the help!

Regards,

Rob

 

[Bob Phillips]

Rob,

Give this a try. It assumes that the dates are in A2, A3, etc,. so
calculates the row number based upon this.

Sub Test()
Dim cLastRow As Long
Dim cLastCol As Long
Dim thisDate As Date
Dim i As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To cLastRow
Worksheets("Sheet2").Cells(1, i - 1).Value = _
Cells(i, "A").Value
Next i
thisDate = Range("A1").Value

With Worksheets("Sheet2")
.Range("A1").Resize(1, i - 1).Copy _
Destination:=.Cells(thisDate - DateSerial(Year(thisDate), 1, 0)
+ 1, "B")
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob Gould]

Hi Bob,

Thanks for that, but I can't seem to get it to work. This is probably
because the example I gave was to illustrate the problem. The actual
arrangement of my data is as follows:

Data to copy is in cells G1:BH1 on the "Data" worksheet
Date to use is in A4 on "Dates" worksheet
Date range to look up date is E7:E377 on the "Data" worksheet
Data to then be pasted in col G in row corresponding to date.

Hope this helps you to help me!

Rob