VBA selection/copy a range depending of date and time conditions

cazdealer · Mar 14, 2011

Hi,
I need help for this problem I've got with my VBA code.

 In a worksheet named “data”. I’ve got data in the first 3 columns.

in column A, it’s the date (format *3/14/2001)
in column B, it’s the time (format *1:30:55 PM)
in column C, it’s a stock price (number with 2 decimals)

In a worksheet named “info”

I want to put:
in A1, the “startingdate”
in B1, the “startingtime”
in A2 the “finishingdate"
in B2, the “finishingtime”

Only for exemple (because I want to change this easily by not always changing my VBA code)

A1 is 12/13/2010
B1 is 9:30:00 AM
A2 is 2/25/2011
B2 is 4:00:00 PM

How could I write something that selects a range from worksheet “data” depending of what is written in cells A1,A2,B1 and B2 of “info” and copying that range to the worksheet “selection” in cell C1?

I started by figuring out only how to defined the date data but I quickly realized that the rest is too complicated for me.
I know I could start by setting up
Dim startingdate as date
Dim finishingdate as date
Dim startingtime as ??????
Dim finishingtime as ??????

startingdate = Worksheets("info").Cells(1,1).Value
finishingdate = Worksheets("info").Cells(2,1).Value
startingtime = Worksheets("info").Cells(1,2).Value
finishingtime = Worksheets("info").Cells(2,2).Value

but for the rest of the code, no ideas…

Could someone give me help with this?

Thanks in advance

venkat1926 · Mar 20, 2011

I am not sure whether I have understood. Your configuration of the two sheets is not available. suppoe
sample data in sheet "data" is

date time price
3/14/2011 1:30:55 PM 23.56
3/15/2011 1:42:55 PM 24.66
3/16/2011 1:54:55 PM 25.76
3/17/2011 2:06:55 PM 26.86
3/18/2011 2:18:55 PM 27.96
3/19/2011 2:30:55 PM 29.06
3/20/2011 2:42:55 PM 30.16
3/21/2011 2:54:55 PM 31.26
3/22/2011 3:06:55 PM 32.36

data in sheet "infor is like this from A1 to B2

3/15/2011 9:30:00 AM
3/16/2011 4:00:00 PM

now try this macro "test" and see sheet "selection"(macro undo is to clear sheet "slection" so that you can recheck). you can modify the macro to suit you.

Code:

Sub test()
Dim r As Range, j As Date, k As Date, m As Range, n As Range, c As Range
Dim p As Integer, q As Integer
With Worksheets("info")
j = .Range("A1") + .Range("B1")
k = .Range("A2") + .Range("B2")
End With

With Worksheets("data")
.Range("A1").CurrentRegion.Sort key1:=.Range("a1"), key2:=.Range("B1"), header:=xlYes
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c In r
If c + c.Offset(0, 1) >= CDate(j) Then
Set m = c
GoTo endloop
Else
End If
Next c
endloop:
p = .Range("a1").End(xlDown).Row
For q = p To 2 Step -1
If Cells(q, 1) + Cells(q, 2) <= CDate(k) Then
Set n = Cells(q, 1)
GoTo endofloop
Else
End If
Next q
endofloop:
Range(m, n.Offset(0, 2)).Copy

With Worksheets("selection")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End With
End Sub

Code:

Sub undo()
With Worksheets("selection")
.Cells.Clear
End With
End Sub

VBA selection/copy a range depending of date and time conditions

cazdealer

Board Regular

Excel Facts

venkat1926

Well-known Member

Similar threads

Forum statistics

Share this page

VBA selection/copy a range depending of date and time conditions

cazdealer

Board Regular

Excel Facts

venkat1926

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock