CODEWITHSUNDEEP
excelvbafor-loopif-statement
Jmaragno
Jmaragno

Reputation: 59

Having trouble with my For and If Statements

I am trying to make something where I click my search button and it searches the value of cell "A1" in a sheet of 10,000 items and then populates all the data that I want from that row of information into the sheet with my searchbox. The problem I am encountering is that when I search an item and it populates the row it is supposed to, then I go to search for another item, it will just overwrite the first one. I want it so it will move down a line so when I go to search a 2nd,3rd even 4th item they are all in different rows. I will include my code here so please if you can help that'd be great. I have tried using Offset and still to no prevail.

Sub SearchBox()

Dim erow As Long
Dim ws As Worksheet
Dim lastrow As Long
Dim count As Integer

lastrow = Sheets("Charlotte Gages").Cells(Rows.count, 1).End(xlUp).Row

For x = 2 To lastrow
i = 3
If Sheets("Charlotte Gages").Cells(x, 1) = Sheets("Gages").Range("A1") Then
Sheets("Gages").Cells(i, 1) = Sheets("Charlotte Gages").Cells(x, 1)
Sheets("Gages").Cells(i, 2) = Sheets("Charlotte Gages").Cells(x, 2)
Sheets("Gages").Cells(i, 3) = Sheets("Charlotte Gages").Cells(x, 3)
Sheets("Gages").Cells(i, 4) = Sheets("Charlotte Gages").Cells(x, 4)
 Sheets("Gages").Cells(i, 5) = Sheets("Charlotte Gages").Cells(x, 5)
Sheets("Gages").Cells(i, 6) = Sheets("Charlotte Gages").Cells(x, 6)
count = count + 1
If Sheets("Charlotte Gages").Cells(x, 1) = "Found" Then
    i = 3 + 1

End If

 Next x


If count = 0 Then
MsgBox ("Cannot Find Gage, Please check Gage ID")

End If

End Sub

Upvotes: 0

Views: 41

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Your incrementation is wrong:

Sub SearchBox()
Dim lastrow As Long
dim i as long, x as long

lastrow = Sheets("Charlotte Gages").Cells(Rows.count, 1).End(xlUp).Row
i = 3
For x = 2 To lastrow

    If Sheets("Charlotte Gages").Cells(x, 1) = Sheets("Gages").Range("A1") Then
        Sheets("Gages").Cells(i, 1).Resize(,6).Value = Sheets("Charlotte Gages").Cells(x, 1).Resize(,6).Value            
        i = i + 1
    End If

 Next x


If i = 3 Then
    MsgBox ("Cannot Find Gage, Please check Gage ID")    
End If

End Sub

Upvotes: 3

Related Questions