Looping in Macro for 3 values to place in another worksheet

[DThib]

Hello,

Latest quandary. I need to set 3 conditions for a row of information to be chosen to have 8 columns copied and placed in another worksheet.
This should pull only those records that match these three conditions from a worksheet with ~30,000 rows.

Can anyone help me figure out how to set up 3 constraints before copying appropriate cells?
I haven't placed the cell result pull yet.

Private Sub WorkaSet()

Dim Rws As Long
Dim Rng As Range
Dim ws, sh As Worksheet
Dim c, a, b As Range
Dim x As Integer


    Set ws = Sheets("Workable")  'specify sheet name here to paste to
    x = 3   'begins pasting in Sheet RFQ on row 2
Application.ScreenUpdating = False


    Set sh = Sheets("Initial Query Pull")
    Set a = sh.Range("$K:$K")
    Set b = sh.Range("$Y:$Y")
            With sh
                For Each c In .Range("$AF:$AF")
                    If (c.Value <> "") And (a.Value = "Assigned") And (b.Value = "") Then
                       'If b.Value = "" Then
                      'searches for "FI ready" cells And (C1.Value = "Assigned")
                        c.EntireRow.Copy
                        ws.Range("B" & x).PasteSpecial Paste:=xlValues
                         
                        x = x + 1


                    End If
                Next c
            End With
    
ws.Range("A1").Select
Application.ScreenUpdating = True


End Sub

DThib

 

[igold]

Hi Dthib,

I have not yet tried to code your requirements. However this is just as an FYI for you. As I read your code, I can see what you want your variables to be declared as. If you look at your declarations in Red:

Dim Rws As Long
Dim Rng As Range
Dim [COLOR=#ff0000]ws,[/COLOR] sh As Worksheet
Dim [COLOR=#ff0000]c, a[/COLOR], b As Range
Dim x As Integer

What you have done is declared the variables ws, c, a as variants. Your declaration should really read as follows:

Dim Rws As Long
Dim Rng As Range
Dim ws As Worksheet, sh As Worksheet
Dim c As Range, a As Range, b As Range
Dim x As Integer

Will it affect the outcome of the code- probably not. I just thought you might like the heads-up.

 

[igold]

Where does it search for "FI ready" (what column) and what happen if it is found or not found...

   'If b.Value = "" Then
                      'searches for "FI ready" cells And (C1.Value = "Assigned")

 

[DThib]

Hi and Thanks

The c is getting the first requirement from AF column in the donor worksheet. Then it will need to make sure the other two cells in the row match to pull the cells from that row.

does that help?

 

[Fluff]

Why not autofilter for the three criteria & then copy the visible rows?

 

[igold]

Hi and Thanks

The c is getting the first requirement from AF column in the donor worksheet. Then it will need to make sure the other two cells in the row match to pull the cells from that row.

does that help?

Does not answer my question. I still don't know where the code is suppose to find the text "FI ready"

Additionally, Fluff has provided good logic to solve your requirements.

 

[DThib]

This is the first of a trio of UserForms to pull data for three escalating meetings. The data is a download from a database query that gets refreshed with each level. This is the part that is giving me trouble.
The FI Ready is a note to myself.

Doing this manually would defeat the purpose.

Deborah

 

[igold]

Which part of this do you see as being done manually. Using AutoFilter and copying the visible rows is all done through VBA.

 

[DThib]

Thanks for you patience, I'll post more later.

 

[igold]

Without seeing your data, I do not know where it starts and ends. You really don't want the code to search the entire one million plus rows. You want to pick a column that will always be have data on the last row of your data set. I arbitrarily used column AF. Perhaps you could modify something like this to fill your particular requirements. I am assuming row 1 is a header row.

Sub test()


    Dim lRow As Long
    Dim ws As Worksheet, sh As Worksheet


    Application.ScreenUpdating = False
    Set sh = Sheets("Initial Query Pull")
    Set ws = Sheets("Workable")  'specify sheet name here to paste to
    lRow = sh.Cells(Rows.Count, "AF").End(xlUp).Row ' pick the column that will always have the last row of data
    
    With sh
        .Range("K1:AF" & lRow).AutoFilter
        .Range("K1:AF" & lRow).AutoFilter Field:=1, Criteria1:="Assigned"
        .Range("K1:AF" & lRow).AutoFilter Field:=15, Criteria1:=""
        .Range("K1:AF" & lRow).AutoFilter Field:=22, Criteria1:=""
    End With


    sh.Range("K1:AF" & lRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
    ws.Range("B3").PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub