Find & copy values below dates

[joel]

My last reply didn't get posted. row 7 on secenario sheet I made an
actual date. Don't know what you want when you have months 1 to 12.
What does month 1 actaully mean. The current month? what happens if
you want to run October data on November 1.

Error 13 is occuring becasue excel is expecting a date in row 7 and the
data is not in a date format.

 

[maywood]

With months 1 to 12 you are talking about the test sheet?

If yes: I filled the cells just for a better overview with 1 to 12. Below
the 1 there should be the inserted the current month, below the 12 the month
in 1 year. And below the dates there should be inserted the data.

When I run it in November I don't need the data for October anymore.

 

[joel]

I'm not sure what the problem is right now. The spreadsheet you posted
this morning didn't have dates in the scenario sheet and now you said
you have dates. It should be working! Correct?

1) Did you update the spreadsheet?
2) Are you getting error 13?
3) Are you getting an data?
4) If yo are having problems then find out where in the macro you are
NOT getting. there are two IF statements and a For loop an you should
be getting into all the items. Put break points and find out where you
ARE NOT getting in the code.

 

[maywood]

Hi Joel,

first of all: Thanks for your great help so far and the time you spent on my
problem!

I tried your code now with Excel 2007 and it doesn't work with it, too.
I created another test file which you can download here:
http://www.2shared.com/file/8719078/4f03937/test2007.html

In it, I described the cells and my target a bit more detailed. Hope you can
have look over it and find the mistake in my fileformat or the code I am
using.

In Sheet2 (Tabelle2) you will find the simplified input data associated with
continuous months and in Sheet1 (Tabelle1) you find the destination for the
dates (yellow) and the destination for data below (grey).
In rows 15-16 & 20-21 I filled the cells with the nominal condition after
pressing the button in Ocotber 09 or November 09.

Hope this helps to understand me ;-)

 

[joel]

I made the code idiot proof to work under any condition. this made th
code a little bit more complicate. If made made some asumptions I coul
of written the code with less steps.

I did two things.
1) I made the start date the 1st day of the current month. Otherwis
there would of been no October data. I'm using the forst day of th
month in the Tabelle1 sheet

2) I added code to put the dates in row 7 of sheet Tabelle1 at th
beginning of the macro. this is the only change I made. the dates wer
missing.

Now the code will look up each date and put it into the correct colum
no matter what order the dates are on sheet Tabelle2.

the results are putting 13 months of data into Tabelle1 (not 12) whic
is baed on the sample data you provided.



Option Explicit


Private Sub CommandButton1_Click()
Dim StartDate As Variant
Dim EndDate As Variant
Dim NewCol As Variant
Dim LastCol As Variant
Dim ColCount As Variant
Dim MyDate As Variant
Dim DestDates As Variant
Dim Data As Variant
Dim Dat As Variant
Dim StartCol As Integer
Dim MonthCount As Integer
Dim YearCount As Integer


StartDate = DateSerial(Year(Date), Month(Date), 1)
EndDate = DateAdd("yyyy", 1, Date)


With Sheets("Tabelle1")
Set DestDates = .Range("D77")
'enter the 12 months into row 7
StartCol = DestDates.Column

MonthCount = Month(StartDate)
YearCount = Year(StartDate)
For ColCount = StartCol To (StartCol + 12)
MyDate = DateSerial(YearCount, MonthCount, 1)
.Cells(7, ColCount).NumberFormat = "mmm-yy"
.Cells(7, ColCount) = MyDate
If MonthCount = 12 Then
MonthCount = 1
YearCount = YearCount + 1
Else
MonthCount = MonthCount + 1
End If
Next ColCount
End With

NewCol = 4 'column D
With Sheets("Tabelle2")
LastCol = .Cells(12, Columns.Count).End(xlToLeft).Column
For ColCount = 3 To LastCol
If IsDate(.Cells(12, ColCount)) Then
MyDate = .Cells(12, ColCount).Value

If MyDate >= StartDate And _
MyDate <= EndDate Then

Data = .Cells(13, ColCount)

With Sheets("Tabelle1")
For Each Dat In DestDates
If Month(MyDate) = Month(Dat) And _
Year(MyDate) = Year(Dat) Then

Dat.Offset(1, 0) = Data
Exit For
End If
Next Dat
End With
End If

End If

Next ColCount

End With
End Su

 

[maywood]

Idiot proof is the right thing for me
It works (at least with my test-file...I will try the real file tomorrow).
Thank you very much for your help Joel!!!