Date recognition using IF formula

Brand_New2VBA · May 26th 2011

Hi,

I keep hitting my head against the wall with this formula. I would the data being copied to paste in a certain column only if the information in the range containes the month May in it. I tried Dim May as date but it didnt work then I tried an IF formula telling excel to pick up the word May and I keep getting the error message type mismatch. This is what I have so far and I'm not getting anywhere with it.

Code

Dim wksSource As Worksheet
    Dim wksDestination As Worksheet
    Dim vntCloneValues As Variant
    Dim nextrow As Long
    
    
    Set wksSource = Sheet3
    Set wksDestination = Sheet1
    
    If Range("F7:F12").Value = "May" Then
    vntCloneValues = wksSource.Range("F7:F12").Value
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 3
    wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneValues), 1) = vntCloneValues
    Erase vntCloneValues
     Sheet3.Range("F7:F12").ClearContents
     End If
     
     
    End Sub

Display More

Can anyone help me complete this code? Am i way off or am I close?

Barb-B · May 26th 2011

Re: Date recognition using IF formula

You definitely had a lot of parts right. I'm guessing the contents of F7:F12 is dates? If so, then this should work:

Code

Sub OnlyMay()
    Dim wksSource As Worksheet
    Dim wksDestination As Worksheet
    Dim vntCloneValues
    Dim nextrow As Long
    Dim i As Long
    
    Set wksSource = Sheet3
    Set wksDestination = Sheet1
    
    vntCloneValues = wksSource.Range("F7:F12")
    For i = LBound(vntCloneValues) To UBound(vntCloneValues)
        If Month(vntCloneValues(i, 1)) <> 5 Then vntCloneValues(i, 1) = ""
    Next i
    nextrow = Sheet1.Cells(Sheet1.Rows.Count, "C").End(xlUp).Row + 3
    wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneValues), 1) = vntCloneValues
    Erase vntCloneValues
    Sheet3.Range("F7:F12").ClearContents
     
End Sub

Display More

Brand_New2VBA · May 31st 2011

Re: Date recognition using IF formula

Hi Barb,

Thanks for the advice. I tried your codes and it didnt pan out very well for me (kept getting a "type mismatch error"). I also tried this and it doesnt work either:

Code

Dim wksSource As Worksheet
    Dim wksDestination As Worksheet
    Dim vntCloneValues As Variant
    Dim nextrow As Long
    Set wksSource = Sheet3
    Set wksDestination = Sheet1
    
    If Sheet3.Range("F11").Value >= "5/1/0000-5/31/0000" Then
    If Sheet1.Cells(Rows.Count, "c") = "" Then
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 4
    If Sheet1.Cells(Rows.Count, "c") = "" Then
    nextrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row + 3
    vntCloneValues = wksSource.Range("F7:F12").Value
    wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneValues), 1) = vntCloneValues
    Erase vntCloneValues
     Sheet3.Range("F7:F12").ClearContents
     End If
     End If
     End If
end sub

Display More

this doesnt send only may dates to the may column ("C") it also send every other dat there too.

How to I isolate the dates?

Barb-B · May 31st 2011

Re: Date recognition using IF formula

I think it would be best at this point if you could attach an example file.

Date recognition using IF formula

Participate now!

Share