Find & copy values below dates

[maywood]

Hello everyone,

I am searching for help with this VBA problem in Excel 2003:

I have a worksheet (Sheet2) where I have dates in row 12 (let's say Jan. 07
to Dec. 10). In row 13 I have some values which I need to copy to worksheet
No. 8, row 8 .
But not the whole row 13, just 12 values beginning with today's month.

Example:
Find the values in Sheet2, row 13 which are associated with the months
Oct.09 untill Oct.10 in row 12. Then copy the values to Sheet 8, row 8,
columns D to P.

It's important that the code is some kind of dynamic so that it inserts the
values for Nov 09 - Nov. 10 next week.

 

[maywood]

Hi Joel,

no, I have the dates in row 12 (at the moment C12:AT12) and the values are
below in row 13

1) The dates are in order. But some cells in row 12 are empty (for example
between Dec.09 and Jan.10) or sometimes there is a cell called for example
"Sum 09".

2) The format of the dates in the source sheet is dd.mm.yyyy. But in the
cells it is shown as "mmm. yy"

3) Worksheet No 8 is called "Scenarios"

4) In the fields D6 to P6

5) I only want to copy the values.
In my destination worksheet No. 8 I am already using a macro whicht inserts
today's month in cell D5 and adds the other months in the cells E55.
Or if you can provide a code which combines both it would be also great.

 

[maywood]

Thanks, it works, but not perfectly.

First of all: I defined the variables! ;-)

When I run the macro, it writes the value of Jul. 09 from my source sheet to
my destination sheet below Oct. 09.
And it ends at Mar. 10 with the value of Dec. 09.
Something should be wrong in the code?!?

I need the values of this month (Oct. 09) to Oct. 10.
And if I use the workbook next week, the macro should insert Nov. 09 to Nov.
10.

 

[maywood]

Now I change the formula for 2 variables:
StartDate = Date
EndDate = DateAdd("yyyy", 1, Date)

Then it enters nearly the correct values in my sheet "Scenarios".
But it starts with the value of Nov. 09 in the cell below Oct. 09 and ends
in the cell below Sep. 10 with the value of Oct. 10.

Any suggestions?

 

[maywood]

Now I got it:
StartDate = DateAdd("m", -1, Date)
EndDate = DateAdd("yyyy", 1, Date)

But why is it that strange with the StartDate? Now it is October and I have
to subtract one month from today to get the right value for Ocotber

 

[maywood]

D55

--> D5 is Oct.09 & P5 is Oct. 10

Next week D5 is Nov. 09 & P5 is Nov. 10 and so on

 

[maywood]

The dates in the Scenario-Sheet are in D55

--> D5 is Oct.09 & P5 is Oct. 10

Next week D5 is Nov. 09 & P5 is Nov. 10 and so on

 

[maywood]

Then dates.Offset(1, 0) = dates
Exit For
'End If
Next dates

--> There I get an error, because of the End if without an If...

If I run the macro without the End if there happens nothing.

How to manage it with an InputBox?

 

[maywood]

My Code (just changed the range to D77), the name of Sheet2 and defined the
variables....nothings happens with this macro

Private Sub CommandButton3_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

StartDate = Date
EndDate = DateAdd("yyyy", 1, Date)

With Sheets("Scenarios")
Set destdates = .Range("D77")
End With

NewCol = 4 'column D
With Sheets("DataInput")
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("Scenarios")
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 Sub

 

[maywood]

hmm, I am realy confused at the moment. Just started with VBA 2 weeks ago...

3) With F8 i ran the macro step by step and I can see, that runs correctly.
But it inserts nothing in "Scenario" Sheet

1) The dates in "DataInput" row 12 are formatted as user-defined (MMM YY). I
tried to format the cells in both sheets as dates or as
user-defined...nothing happens.

2) Could be. But do I check this?

 

[maywood]

Hi again!
I created an test.xls with some examples and where the macro also not works.
You can download it here: http://www.fileuploadx.de/281672

Would be great if you can have a look at it.

 

[maywood]

Thanks for types!

I added 3 variables to the watch window: destdates, data and dat.
When I use F8 i can see, how the variable data changes.
But later I get a Run-Time Error 13 "Type Mismatch" when the macro arrives
at the line:

If Month(Mydate) = Month(dat) And _
Year(Mydate) = Year(dat) Then

Some ideas, why this error pops up?
Have you seen my post with my test.xls? http://www.fileuploadx.de/281672
There the code also doesn't work

 

[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.

 

[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 ;-)

 

[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!!!