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.

 

[joel]

I think you mean you have dates in column 12 ("L") and the values you
want copied in columns 13 ("M").

1) Are the dates in Order
2) What is the format of the Dates 1/1/09 or Dec 10, 2009? Your
posting had a period after the abbreviation of the month. Is the period
in the actual date on the worksheet.
3) What is the name of worksheet No. 8?
4) Where on sheet No. 8 do you want the data?
5) Do you want to copy both the date and value or just the value?

 

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

 

[joel]

Try this


Sub MoveDates()

StartDate = DateValue("1/7/09")
EndDate = DateValue("12/12/09")

NewCol = 4 'column D
With Sheets("sheet2")
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")
..Cells(6, NewCol) = Data
NewCol = NewCol + 1
End With
End If

End If

Next ColCount

End With




End Sub

 

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

 

[joel]

On the sheet "Scenarios" where are the dates located so I can put th
correct data in the correct column

 

[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

 

[joel]

You didn't answer my question. Where are the dates in the Scenario
sheet???

You won't get the correct answer next month becasue it wilbe off an
additional column. You need to align the dates in the scenario column
with the dates in sheet 2. If will be off a differen t amount each
month if hte coluns remain the same on both worksheets.

 

[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

 

[joel]

I used your approach for getting Start and End Dates. I prefer using
message box to get the start date because you may want to run th
October Report on Nov 1st.

Sub MoveDates()

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

With Sheets("Scenarios")
Set DestDates = .Range("D55")
End With
NewCol = 4 'column D
With Sheets("sheet2")
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 Su

 

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

 

[joel]

It looks like you modified my code and made some errors in the changes.
Post you changes and I will take a look. Here is my code again


Sub MoveDates()

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

With Sheets("Scenarios")
Set DestDates = .Range("D55")
End With

NewCol = 4 'column D
With Sheets("sheet2")
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 Su

 

[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

 

[joel]

I just ran your code and it is working. This means one of three things

1) The cells are not formated as dates. Go to DataInput and scenari
sheets and click on cells with dates. The go to worksheet menu Format
Cells - Number and check if the one of the Date formats is highlighted.
If you see General or Text highlighted then the Dates are not Dates.
You may need to modify the code to use DateValue(Range("A1")) to conver
to a real date for comparisons

2) the code is moving blank data into the scenario sheet. Check th
value of the variable "DATA" to see if there is any real data.

3) The dates in the DataInput Sheet are not within the dates specifie
in the Scenario Sheet.


Add some break points in the code to help you debug the proble

 

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

 

[joel]

A few types

1) Debugging

F9 - Adds break point
F8 - Steps through code
F5 - runs until break point or you get to end of code

You can run these options through the menu selections Run an
Debug.
I often use "Reset" & "Compile" which are not

You can view a variable value by hoovering over the variabl
(doesn't always work), or add the variable to the watch window.
Highlight variable "Data" and right click. The choose "Add to Watch
and click OK in the popup. A new view will appear in the VBA window fo
the watch. As you step through the code you can see the watc
variabgles change.

2) Dates and time
Dates are stored as number with Jan 1, 1900 is Day 1. Jan 2, 190
is Day 2. if you enter a date on the worksheet and then format the dat
as a number you will get the number of day from Jan 1, 1900 to th
particular date. Today is 40113.

Time is stored as a fraction of the day Midnight is the start of th
day which equals 0. Each hour is 1/24. 1:00 AM is 1/24. 6:00 AM i
6/24 or .25.

Adding the Day and time together is equivalent to entering a time suc
as

10/27/09 10:00AM = A1 + B1 where A12 is the date and B1 is the time

3) My code doesn't care how the worksheet cells are formated. Th
formating is only how the dates and time are viewed, not the number tha
is stored internally. that is why in my code I used

Mydate = .Cells(12, ColCount).Value


Let me know how you make out. The code isn't getting into one of th
"IF" statments or you are just moving a cell with no data in th
location. What youcould try is putting in the scenario sheet where th
data is suppose to go data like A B C D E F in row 8 and see if thi
data gets over-writen with blank data. this will indicate the macro i
working but not moving the correct cells of data.

Note the row where the data is being put in the scenario sheet if a
"Offset(1,0)" from where the data is located. 1 indicates 1 row and
indicates 0 columns

 

[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