CODEWITHSUNDEEP
vbaexcel
Tom85
Tom85

Reputation: 5

Excel VBA - How to find a value in a column and return the row it is on

I currently use a for loop to look through column A in the HDD database sheet and when it finds the matching search criteria it copies all the information in that row into a designated area.

I am trying to use the same for loop to tell me which row the search criteria is found on so i can delete that row from the HDD database sheet.

Tried a few things but nothing seems to work.

Any help is very welcome on this.

Private Sub EditButton_Click()

    Dim Userentry As String
    Dim i As Long
    Dim ws, ws1, ws2 As Worksheet
    'Dim found As Range
    Dim RowNumber As String
    Set ws = Sheets("HDD database")
    Set ws1 = Sheets("HDD log")
    Set ws2 = Sheets("Sheet3")


    Userentry = editTxtbox.Value
    'ws1.Range("A36").Value = Userentry

    For i = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row
        If (ws.Cells(i, 1).Value) = Userentry Then
            ws2.Range("A1").Offset(1, 0).Resize(1, 8).Value = _
                      ws.Cells(i, 1).Resize(1, 8).Value
        End If

    Next i

    addnewHDDtxtbox.Value = ws2.Range("A2")
    MakeModeltxtbox.Value = ws2.Range("B2")
    SerialNumbertxtbox.Value = ws2.Range("C2")
    Sizetxtbox.Value = ws2.Range("D2")
    Locationtxtbox.Value = ws2.Range("E2")
    Statetxtbox.Value = ws2.Range("F2")


    For i = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row
        If (ws.Cells(i, 1).Value) = Userentry Then
            RowNumber = ws.Cells.Row
        End If
    Next i

    ws1.Range("I3").Value = RowNumber

End Sub

Upvotes: 0

Views: 17568

Answers (2)

seadoggie01
seadoggie01

Reputation: 530

I think this should help. Using Range.Find() should speed it up a little. I can edit this if you want more, just comment.

'I liked your found :)
Dim found As Range
'Set found equal to the cell containing your string
Set found = ws.Range("A:A").Find(Userentry)
'Show the row of found if you want
'MsgBox found.Row
'Delete found's row
'ws.found.Rows.Delete
'Alternately, set the value of I3 to found's row
ws1.Range("I3").Value = ws.found.row

Upvotes: 1

user4467707
user4467707

Reputation: 75

Change:

RowNumber = ws.Cells.Row

to

RowNumber = i

Upvotes: 0

Related Questions