Find & copy values below dates
 

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.

Find & copy values below dates
 

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?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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 E5:P5.
Or if you can provide a code which combines both it would be also great.


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?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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.


"joel" wrote:


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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" wrote:

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.


"joel" wrote:


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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" wrote:

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" wrote:

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.


"joel" wrote:


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

D5:P5

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

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

"joel" wrote:


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

The dates in the Scenario-Sheet are in D5:P5

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

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


"joel" wrote:


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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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

Sub MoveDates()

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

With Sheets("Scenarios")
Set DestDates = .Range("D5:P5")
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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" wrote:


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

Sub MoveDates()

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

With Sheets("Scenarios")
Set DestDates = .Range("D5:P5")
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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("D5:P5")
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

My Code (just changed the range to D7:P7), 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("D7:P7")
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" wrote:


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("D5:P5")
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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 scenario
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 convert
to a real date for comparisons

2) the code is moving blank data into the scenario sheet. Check the
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 specified
in the Scenario Sheet.


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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?

"joel" wrote:


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 scenario
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 convert
to a real date for comparisons

2) the code is moving blank data into the scenario sheet. Check the
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 specified
in the Scenario Sheet.


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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

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

"joel" wrote:


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 scenario
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 convert
to a real date for comparisons

2) the code is moving blank data into the scenario sheet. Check the
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 specified
in the Scenario Sheet.


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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 and
Debug.
I often use "Reset" & "Compile" which are not

You can view a variable value by hoovering over the variable
(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 for
the watch. As you step through the code you can see the watch
variabgles change.

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

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

Adding the Day and time together is equivalent to entering a time such
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. The
formating is only how the dates and time are viewed, not the number that
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 the
"IF" statments or you are just moving a cell with no data in the
location. What youcould try is putting in the scenario sheet where the
data is suppose to go data like A B C D E F in row 8 and see if this
data gets over-writen with blank data. this will indicate the macro is
working but not moving the correct cells of data.

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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

"joel" wrote:


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 and
Debug.
I often use "Reset" & "Compile" which are not

You can view a variable value by hoovering over the variable
(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 for
the watch. As you step through the code you can see the watch
variabgles change.

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

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

Adding the Day and time together is equivalent to entering a time such
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. The
formating is only how the dates and time are viewed, not the number that
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 the
"IF" statments or you are just moving a cell with no data in the
location. What youcould try is putting in the scenario sheet where the
data is suppose to go data like A B C D E F in row 8 and see if this
data gets over-writen with blank data. this will indicate the macro is
working but not moving the correct cells of data.

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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" wrote:


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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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 he
http://www.2shared.com/file/8719078/.../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" wrote:


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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Find & copy values below dates
 

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

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

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

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

the results are putting 13 months of data into Tabelle1 (not 12) which
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("D7:P7")
'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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Find & copy values below dates
 

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

"joel" wrote:


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

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

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

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

the results are putting 13 months of data into Tabelle1 (not 12) which
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("D7:P7")
'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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.