CODEWITHSUNDEEP
vbaexcel
user3812753
user3812753

Reputation: 79

Excel VBA: determing rows on which the value is found

I am trying to have the row number in which the value was found:

Set rep = Sheets("Details")
For i = 2 To n
If Sheets("Work").Range("A:A").Find(Worksheets("Work_report").Range("E" & i).Value, lookat:=xlWhole) Is Nothing Then
Else:
findrow = Sheets("Work_report").Range("E" & i).Find(Worksheets("Work").Range("A:A").Value, lookat:=xlWhole).Row
o = rep.Range("A" & Rows.Count).End(xlUp).Row + 1
rep.Range("A" & o).Value = "FT_EXCEL"
rep.Range("B" & o).Value = Sheets("Start").Range("C5") & "AB" & Format(o - 1, "00")
rep.Range("C" & o).Value = Sheets("Work_report").Range("E" & findrow)
End If
Next i

For the row I want to use "findrow" which basically would need to find the row in Work_Report. This is only executed if, from sheet Work the cell value was found in Work_Report, so in order to find the number of the row I am trying to invert the line and have it found back in Work_Report, however this gives me the error of - Object variable or With block variable not set.

Thanks!

Upvotes: 0

Views: 151

Answers (2)

user3598756
user3598756

Reputation: 29421

you can do it in one shot with exploiting AutoFilter() method's operator xlFilterValues value

Sub main()
    Dim rep As Worksheet

    Dim criteriaArr As Variant
    With Worksheets("Work_report") '<--| reference "Work_report" sheet
        criteriaArr = Application.Transpose(.Range("E2", .Cells(.Rows.Count, "E").End(xlUp)).Value) '<--| store its column E cells content from row 2 down to last not empty one
    End With

    Set rep = Sheets("Details")
    With Worksheets("Work") '<--| reference "Work" sheet
        With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) '<--| reference its column A cells from row 1 (header) down to last not empty one
            .AutoFilter Field:=1, Criteria1:=criteriaArr, Operator:=xlFilterValues '<--| filter it with "Work_report" sheet column E content
            If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then '<--| if any filtered cells other then headers
                With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) '<--| reference filtered cells skipping header
                    rep.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count).Value = "FT_EXCEL" '<--| write 'rep' sheet column A corresponding cells content
                    With rep.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count)
                        .Formula = "=CONCATENATE(Start!$C$5,""AB"",TEXT(ROW(),""00""))" '<--| '<--| write 'rep' sheet column B corresponding cells content
                        .Value = .Value
                    End With
                End With
            End If
        End With
        .AutoFilterMode = False
    End With
End Sub

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

I prefer MATCH to find on single column searches:

Set rep = Sheets("Details")
Dim test As Long
For i = 2 To n
    test = 0
    On Error Resume Next
    test = Application.WorksheetFunction.Match(Worksheets("Work_report").Range("E" & i).Value, Sheets("Work").Range("A:A"), 0)
    On Error GoTo 0
    If test > 0 Then
        o = rep.Range("A" & Rows.Count).End(xlUp).Row + 1
        rep.Range("A" & o).Value = "FT_EXCEL"
        rep.Range("B" & o).Value = Sheets("Start").Range("C5") & "AB" & Format(o - 1, "00")
        rep.Range("C" & o).Value = Sheets("Work_report").Range("E" & test)
    End If
Next i

Upvotes: 1

Related Questions