Pasting down empty filtered Cells?

[lizsunnysideup]

Hello. Is there a way to paste down a column into empty blank cells which has been filtered? Here's what I have so far, I've gotten to copying and pasting the last active cell in A, but need to move once cell down and paste the data mirroring the last used row in cell V. Column V always has data, however my task is to fill in the blanks in column A. This is what I have so far.

Sub Macro2()
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("V:V").AutoFilter Field:=22, Criteria1:= _
        "REFILL TOO SOON:DISPENSED TOO SOON"
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Copy
End Sub

 

[lizsunnysideup]

YES!! This worked, and you're right, I didn't need to filter!! Thank you so much!! But just because I'd like to understand what it is that happened, what does "Dim R As Long" mean/do in VBA? And the "For r" what does this tell the system to do?

 

[Michael M]

You have to declare your variables, so by declaring r as long it will basically call it a number....1,2,3...etc.
"For r" is saying make r a number starting at 1....and each time it loops it will add 1 to r.
So the code will check, V1, V2, etc...and provide an answer in A1,A2,etc.

Does that help ??

 

[lizsunnysideup]

Yes this does make sense now. One more question. The example of Refill Too Soon is only one category I'm looking, but if I needed to look for multiple categories in column V, I'm assuming I would use an array to say look for "Refill Too Soon", "Refill Too Soon: Duplicate Claim, and "Refill Too Soon, Claim already processed for Store", how would write out the array? When I recorded a marco, it captured it this way

    ActiveSheet.Range("$A$1:$EE$2007").AutoFilter Field:=22, Criteria1:=Array( _
        "DUPLICATE PAID/CAPTURED CLAIM:MORE CURRENT REFILL EXISTS", _
        "REFILL TOO SOON:CLAIM ALREADY PROCESSED FOR STORE, RX, DOS", _
        "REFILL TOO SOON:DISPENSED TOO SOON"), Operator:=xlFilterValues

But when I attempted to modify the code you provided me using this way of writing out the array, it won't work, but I think it's because I'm no longer auto filtering and I'm not sure if the commas have something to do with it either? Thanks again so much in advance.

 

[Michael M]

With multiple categories in Col V, what result is expected in the blanks in Col A ??

 

[lizsunnysideup]

The same text, Refill Too Soon

 

[Michael M]

Use...

Sub MM1()
Dim r As Long
For r = 1 To Cells(Rows.Count, "V").End(xlUp).Row
    If Range("V" & r).Value = "REFILL TOO SOON:DISPENSED TOO SOON" And Range("A" & r).Value = "" Or _
        Range("V" & r).Value = "DUPLICATE PAID/CAPTURED CLAIM:MORE CURRENT REFILL EXISTS" And Range("A" & r).Value = "" Or _
        Range("V" & r).Value = "REFILL TOO SOON:CLAIM ALREADY PROCESSED FOR STORE, RX, DOS" And Range("A" & r).Value = "" Then
        Range("A" & r).Value = "REFILL TOO SOON"
    End If
Next r
End Sub

 

[lizsunnysideup]

Yes, right again this worked! Thank you so very much for taking the time to answer my questions and to explain how the code you provided works.

 

[Michael M]

Glad to help...