vendredi 23 juin 2017

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

Aucun commentaire:

Enregistrer un commentaire