VBA - If range value = "date" then

[bobunknown]

Hi all,
I'm new to VBA and trying to use it to make my job a bit easier while learning something new.

The problem I am working at the moment is trying to get certain rows of my Excel sheet copied in to a different sheet automatically if a value in a certain range is the correct date.

Here is what I managed to think up so far... be gentle and use small words

Sub Movedata_March()

If Range("M2:M244") = "31.03.2019" Then
Row.Select
Selection.Copy
Sheets("March").Select
ActiveSheet.Paste
End If
End Sub

 

[pbaldy]

Dates need # delimiters, plus dates in VBA need to be in US format. Try:

If Range("M2:M244") = #3/31/2019# Then

 

[bobunknown]

Tried that, I just get a Type mismatch error for:

If Range("M2:M244") = #3/31/2019# Then

 

[Minty]

I'm not sure if the logic makes sense in Excel here, surely a range can't be equal to a date?

 

[jleach]

That Range reference will give you a collection of objects: cells and rows and columns and such, where each cell might have a particular value. The Value is what you need to be comparing for your date, not the range.

It's akin to saying "I have a calendar for the month of May, does it equal July 1st?" There's no correlation between the collection of values and the single value to which you're trying to compare.

Even if the range only consists of a single cell, you'll still need to explicitly access that cell in code before you can compare the value (I'm not sure offhand how, sorry - Excel OM isn't a strong one for me)

 

[pbaldy]

Didn't even notice that. From Access I'd use:

xl.Cells(R, C)

where R and C are either numbers or variables reflecting the desired row/column. From within Excel it may just be:

Cells(R, C)

but I'm also not strong in VBA directly in Excel, I automate it from Access.

 

[bobunknown]

Unfortunately there is no access side of this database it is all from Excel.
Simply put all im trying to do is move information to a different sheet should the date in the cell correspond with a pre-set date.

Any ideas on how I could do this would be appreciated.

 

[pbaldy]

Did you try the test in post 6? If that doesn't work, record a macro putting a value in a cell or something and see how the resulting code refers to the cell.

 

[Gasman]

I'd use something like this

Dim Cell As Range
For Each Cell In Range("M4:M244")
    If Len(Cell.Value) = > 0 and Format(Cell.Value,"ddmmyyyy") = "31032019" Then
     <Your code here >
    End If
Next Cell

Could also try

Cell.Value =datevalue("31.03.2019") ' depending on your date format.

Might even get away without the Value ?


HTH


Edit: That would do one row at a time though.


Might be best to filter for the correct date then use something like below
Note, I had already filtered the data manually before running the code.

    Application.StatusBar = "Copying required data to new sheet....."
    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:X" & lLastRow).SpecialCells(xlCellTypeVisible).Copy
    Workbooks.Add
    strNewName = ActiveWorkbook.Name
    With Selection
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    End With

 

[bobunknown]

It now looks like this:

Sub Info_Transfer()
Dim Cell As Range
    Range ("M4:M244")
    If Len(Cell.Value) >= 0 And Format(Cell.Value, "ddmmyyyy") = "31032019" Then
    Row.Select
    Selection.Copy
    Sheets("March").Select
    ActiveSheet.Paste
End If
End Sub
Next Cell

I have had to add 'Sub Info_Transfer()' as it kept returning a compile Error before. However now I keep getting told that use of 'Range' is an invalid use of property. Same returns if I substitute Range for 'For Each Cell In Range'.

 

[Gasman]

That is because you are not using the For each code?
Also the Next statement is outside the Sub ?


I believe filtering the sheet and then using the second set of code I posted would be easier.?

I used that to do pretty much the same thing in a workbook of mine.
You can record a macro for the filter part and then incorporate it into the code, or do it manually as I did.

 

[Gasman]

This code only prints out the cell that contains the matching date.

Sub Info_Transfer()
Dim Cell As Range

For Each Cell In Range("A1:A21")
    If Len(Cell.Value) >= 0 And Cell.Value = "15/06/2018" Then
        Debug.Print Cell.Value
    End If
Next Cell
 End Sub

 

[arnelgp]

Here is a simple add ins.
oaste your data in sheet1 or use the existing data i provided.
Goto ribbon add-ins. And choose myaddins.
Play with the userform.
View the vba.

 

[bobunknown]

Alright I got it working, to the point where it would find and copy dates (but not the whole row), small progress but progress none the less... right before Excel crashed :banghead:

I have replicated the code I had when it was working:

Sub Macro1()
    Columns("M:M").Select
    If Len(Cell.Value) = > 0 and Format(Cell.Value,"dd.mm.yyyy") = "31.03.2019" Then
    Selection.Copy
    Sheets("March").Select
    ActiveSheet.Paste
End If
End Sub

But now I get the error '424' Object required despite the fact it was working two mins ago . I know this is likely a simple problem for you guys with experience but my knowledge is limited and id appreciate any expiation for why its doing this so I can fix said problems in the future myself.

Additionally:
When I insert the line Row.Select I get the and undefined variable error and am therefore unable to move the whole row across as I would like.

Thanks In advance.

 

[Gasman]

You cannot just mix code if you do not know what it does.?

Firstly you select the whole column M
Then you test for my cell object value, but you have not declared the cell object?
Even if it worked you would then copy the column M as that is what is selected.?


So I doubt that is the code that you had working.

I would record a macro carrying out the steps that you would take manually. End the recording and then look at the code and try and understand it. It will be more verbose than you would write manually, but it will give you lots of tips.

That is how I pretty much learnt Excel VBA plus Google, and still learning.

The second code snippet I supplied in #9 is the way I would approach it as it worked for me.
I needed to copy the filtered data to a new sheet to email to respective people. At the time I just manually filtered and then ran the macro.
I know that my last column was X, and I find the last row of the data and then copy it all.

You sound like you need to do the same.

 

[bobunknown]

Thanks for the advice.

I did try and record a macro at the start as you suggested and that is where I got the lines of code for selection of the columns, copying, sheet selection and pasting. The part that I am struggling with as you know is the filtering as the recording gave me absolutely nothing on that to go off.

This has lead me obviously to here where I am trying to go through the suggestions you guys have posted to the best of my ability and understanding, and yes to mashing code together to try and work out what it dose.

 

[Gasman]

Here is something I use to copy new rows in an Excel workbook downloaded each day.
Basically it switches off any filters that might exist (just in case)
It then filters for all rows where I have #N/A in the first column.
I had inserted a new column and VLOOKUP formula previously in the code that looks up the key in the destination workbook. If I have #N/A, then they do not exist and I want to copy those rows.
I need to know where I can paste them and I do that with lngStartClient, which finds the last row in my destination workbook and sheet and adds 1 to it.
So I select that cell and then paste the data.

All pretty much what you would do manually. There are probably better ways, but it works for me.

HTH

 ' Now find last row in clients, switch off any filters to do this
    Windows(strStatsName).Activate
    Sheets("Clients").Select
    If ActiveSheet.AutoFilterMode Then
        If ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
    ElseIf ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
    
    lngStartClient = Range("A" & Rows.Count).End(xlUp).Row + 1
    ' Switch back to GMP to get new clients
    Windows(strGMPName).Activate
    Sheets("Deduped").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AA$5000").AutoFilter Field:=1, Criteria1:="#N/A"
       
    ' Now check again just in case to see if there are any rows to copy
    
    lngNA = Range("A" & Rows.Count).End(xlUp).Row
 If (lngNA = 1) Then
        MsgBox "Second check: No rows to copy today, macro will terminate"
        'Set rng = Nothing
        GoTo Exit_Sub
    End If

    Application.StatusBar = "Copying new data to statistics workbook....."
    
    Range("B2:Y" & lngNA).SpecialCells(xlCellTypeVisible).Copy
    ' Back to stats file
    Windows(strStatsName).Activate
    Sheets("Clients").Select
'    If ActiveSheet.AutoFilterMode Then
'        If ActiveSheet.FilterMode Then
'            ActiveSheet.ShowAllData
'        End If
'    End If
    
    'Need to unhide any columns ready to paste
    Columns("A:AB").Select
    Selection.EntireColumn.Hidden = False

    Range("A" & lngStartClient).Select
    ActiveSheet.Paste

 

[Gasman]

I have just recorded a macro that filters for a particular date and this is what it produced.
I filtered for 26th June this year in data just for this year.

Sub FilterTest()
'
' FilterTest Macro
'

'
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AA$255").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "6/26/2018")
End Sub

 

[bobunknown]

Thanks,

Ill muddle through what's here so far and see if I cant wrap my head around it and try get something working.

 

[Gasman]

Break it down into small chunks.


Identify what data you want
Filter for that data.
Work out where you want to put it.
As you have filtered the data I believe you can just use Cells.Select to select everything.
Then go to teh destination workbook/sheet, find the place to start, select that cell and then paste.


Always try and break a big problem down into smaller manageable chunks.