CODEWITHSUNDEEP
vbaexcelcopypaste
ExoticBirdsMerchant
ExoticBirdsMerchant

Reputation: 1516

Find the Row of a String Value in a Column while Criteria are met in the same Row but the other Columns

Ok i am trying to find a value from a Column. This Column has about 600 values. While it is not a problem to find the value for example "Lassie" and locate that it is on Cell A301 it creates a problem the fact that i need to check the Row which the value Lassie is (here it is 301) and search if other criteria are being met.

I need something like this:

Then if all these conditions are met i need to copy the CellH301 and paste it in another Sheet

The trick is that i cannot search for CellA301 first and if it equaled to "Sales Revenue, Net" do all the other tests because there are numerous cells in column A which are populated with the String Value "Sales Revenue, Net" and only the one that meets the above conditions altogether is the correct one.

EDIT

Sub wussss()

''disable autofilter in case it's already enabled'
ThisWorkbook.Sheets("31_December_2010").AutoFilterMode = False

With ThisWorkbook.Sheets("31_December_2010").Range("A1:AZ1")
    'set autofilter'
    .AutoFilter Field:=1, Criteria1:="Sales Revenue, Net"
    .AutoFilter Field:=11, Criteria1:="1/1/2010"
    .AutoFilter Field:=12, Criteria1:="12/31/2010" 'x
    .AutoFilter Field:=13, Criteria1:=""
    .AutoFilter Field:=14, Criteria1:=""
    .AutoFilter Field:=15, Criteria1:="" 'X  IT FAILS HERE
    .AutoFilter Field:=16, Criteria1:=""
    .AutoFilter Field:=17, Criteria1:=""
    .AutoFilter Field:=18, Criteria1:=""
    .AutoFilter Field:=19, Criteria1:=""
    .AutoFilter Field:=20, Criteria1:=""
    .AutoFilter Field:=21, Criteria1:=""
    .AutoFilter Field:=22, Criteria1:=""
    .AutoFilter Field:=23, Criteria1:=""
    .AutoFilter Field:=24, Criteria1:=""
    .AutoFilter Field:=25, Criteria1:=""
    .AutoFilter Field:=26, Criteria1:=""
    .AutoFilter Field:=27, Criteria1:=""
    .AutoFilter Field:=28, Criteria1:=""
    .AutoFilter Field:=29, Criteria1:=""
    .AutoFilter Field:=30, Criteria1:=""

End With

With ThisWorkbook.Sheets("Sheet1")
    On Error Resume Next
    Set Rng = .Range("A2:A" & .Rows.Count).Rows.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
End With
If Not Rng Is Nothing Then
   MsgBox Rng.Row ' returns first visible row number
End If

ThisWorkbook.Sheets("Sheet1").AutoFilterMode = False 'disable autofilter'
End Sub

Upvotes: 1

Views: 327

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

As follow up from comments, this code works:

Sub test()
    Dim i As Byte
    Dim rng As Range

    With ThisWorkbook.Sheets("31_December_2010")
        'disable autofilter in case it's already enabled'
        .AutoFilterMode = False
        With .Range("A1:AZ1")
            .Value = .Value
            'set autofilter'
            .AutoFilter Field:=1, Criteria1:="Sales Revenue, Net"
            .AutoFilter Field:=11, Criteria1:="1/1/2010"
            .AutoFilter Field:=12, Criteria1:="12/31/2010" 'x
            For i = 13 To 30
                .AutoFilter Field:=i, Criteria1:=""
            Next i
        End With

        On Error Resume Next
        Set rng = .Range("A2:A" & .Rows.Count).Rows.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0

        If Not rng Is Nothing Then MsgBox rng.Row ' returns first visible row number

        .AutoFilterMode = False 'disable autofilter'
    End With
End Sub

Also little note:

You can apply AutoFilter only on used part of sheet. E.g. if last column of UsedRange is M, in line .Range("A1:AZ1").Autofilter Excel aplies filter only for A1:M1. However part .Value = .Value of the code above, explicitly adds A1:AZ1 to used part of sheet.

Upvotes: 1

Related Questions