abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,429
- Office Version
- 2019
- 2010
- Platform
- Windows
hello
I have this code brings data from sheet to another between two dates , but some times I writing wrong date in l2,m2 may be from date > to date and may not be the date in l2 and m2 so I add message to show the wrong because it gives me error in code whether I write the right date or wrong it shows always the message
this is my code I disabled the message in the code to understand what I want
If any body has idea please provide me
thanks
I have this code brings data from sheet to another between two dates , but some times I writing wrong date in l2,m2 may be from date > to date and may not be the date in l2 and m2 so I add message to show the wrong because it gives me error in code whether I write the right date or wrong it shows always the message
this is my code I disabled the message in the code to understand what I want
VBA Code:
Sub CopyDataUsingDateRange()
Application.ScreenUpdating = False
Dim wsData As Worksheet, wsDate As Worksheet,
Dim dSDate As Variant, dEDate As Variant
Dim lRowStart As Long, lRowEnd As Long
Dim aData() As Variant
Dim i As Long
'set the worksheet objects
Set wsData = ThisWorkbook.Sheets("process")
Set wsDate = ThisWorkbook.Sheets("data")
'required variables
dSDate = wsDate.Range("l2").Value
dEDate = wsDate.Range("m2").Value
'set the array - you can make this dynamic!
aData = wsData.Range("A1:h1000").Value
'for loop to find start
For i = 2 To 1000
'If dSDate > dEDate Or dSDate <> Format(Date, "mm/dd/yyyy") Or dEDate <> Format(Date, "mm/dd/yyyy") Then
' MsgBox "the first date can't be more than end date", vbInformation
' Exit Sub
' End If
If aData(i, 1) = dSDate Then
lRowStart = i
Debug.Print "Start row = " & lRowStart
Exit For
End If
Next i
'now loop backwards to find end date
For i = 1000 To 2 Step -1
If aData(i, 1) = dEDate Then
lRowEnd = i
Debug.Print "End row = " & lRowEnd
Exit For
End If
Next i
'now we have start and end dates
'going to use copy/ paste for simplicity
wsData.Range("A" & lRowStart, "h" & lRowEnd).Copy
'paste in date sheet
wsDate.Range("A2").PasteSpecial Paste:=xlPasteValues
'clear clipboard
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
thanks