Need a little help with vba: Please advice on range selection till last row from given address.

[pedie]

Hi, Last line in the code is where i need help...

After finding and setting found adderss as address i want vba to select that range and copy it to sheet2.range("A1") as values.


Thanks alot for helping

[/FONT]
[FONT=Courier New]Dim startdate As String
Dim enddate As String
Dim myfirstadd, mysecondadd As String[/FONT]
[FONT=Courier New]startdate = Me.DTPicker1.Value
enddate = Me.DTPicker2.Value[/FONT]
[FONT=Courier New]Dim c As Range
With Sheet1.Range("a1:as1")
Set c = .Find(startdate, LookIn:=xlValues)
If c Is Nothing Then
MsgBox startdate & " not found"
Exit Sub
Else
myfirstadd = c.Address
End If
End With[/FONT]
[FONT=Courier New]With Sheet1.Range("a1:as1")
Set c = .Find(enddate, LookIn:=xlValues)
If c Is Nothing Then
MsgBox startdate & " not found"
Exit Sub
Else
mysecondadd = c.Address
End If
End With[/FONT]
[FONT=Courier New]dim lr as long[/FONT]
[FONT=Courier New]lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New][B]Range(myfirstadd & ":" & mysecondadd & Lr).Select[/B][/FONT]
[FONT=Courier New]

 

[VoG]

Maybe

Set c = .Find(CLng(startdate), LookIn:=xlValues)

 

[Norie]

No problem.

You could actually make it a little simpler by not using the addresses.

This worked for me with 'real' dates in row 1.

Sub test()
Dim startdate As Date
Dim enddate As Date
Dim myfirstadd As Range, mysecondadd As Range
Dim lr As Long

    startdate = Me.DTPicker1.Value
    enddate = Me.DTPicker2.Value
 
'        startdate = DateSerial(2011, 5, 1)
'        enddate = DateSerial(2011, 5, 4)
    
    With Sheet1.Range("A1:AS1")
        Set myfirstadd = .Find(startdate, LookIn:=xlValues)
        If myfirstadd Is Nothing Then
            MsgBox startdate & " not found"
            Exit Sub
        End If
    End With
 
    With Sheet1.Range("A1:AS1")
        Set mysecondadd = .Find(enddate, LookIn:=xlValues)
        If mysecondadd Is Nothing Then
            MsgBox startdate & " not found"
            Exit Sub
        End If
    End With
 
    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
 
    Range(myfirstadd, mysecondadd).Resize(lr).Copy
    
End Sub

 

[pedie]

Peter, what does CLng function do? I am not sure....
Norie, thats much better....!


Thanks again guys!

 

[VoG]

Clng converts a value to a long integer. Did it work?

 

[pedie]

Peter, thanks for explaining...
No, it says not found even though the dates are there.
so i first set the format to general, and then enter date picker values in sheet cell and then format the same to general then run the code..then it finds it...
Thats kinda long way, yea? :D

(This is not the first time..this things has happend to me many a times...)

Thanks again.