Problem with .Find in Loop.

[lfalla]

Hello. In advanced I would like to thank any response and apologize for the frequency of posting. I've done a huge code which works - the idea is to filter through 24 parameters and search in a DB. When the person put one or as many parameters as needed (up to 24) and click the button, the code will search on the DB for the entry that matches or comes close to fit the parameters.

As far as this goes, it works fine. The problem comes with the loop and the print function. The code is supposed to pick one specific row (the first one) of the DB and print on a place (namely Sheet("1").blankrange). Now comes the problem: when the code finds an entry that matches it will copy the whole DB's row and print the whole thing on Sheet("1"), using the first cell as the value. For example: I've searched for "chocolate cookies brown" on Sheet("1"), pressed the button. The DB entry that matches is, for example, "cookie chocolate brown handmade flour chocolate butter" (from "A1:G1"). The code will pick A1 (cookie) and print 7 times on Sheet("1"). Any thoughts in how to fix it? I realize that the problem is pretty much the way how I organized the loop, but I honestly dont know how to remediate it. I have tried to change the order, put stuff outside loop and other "dumb" solutions, but nothing works. I have also thought of something along "maybe he's picking the entire row range and printing", but doesnt look like the case.

A part of the code (since it is REALLY long) that has the problem imo:


With Sheets("2").Range("$A$2:$AI$250")
If validity = 0 Then
MsgBox "You need to put a parameter to search"
Else
Set rng = .Find(What:="*", After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlPart) ' The code before this point adds filters with the parameters so the idea here is to find anything that has text '
If Not rng Is Nothing Then
Application.Goto rng, True
firstaddress = rng.Address
Do
Sheets("1").Activate ' START - this part will look on sheet("1") for a emply cell '
For Each cell In blankrange.Cells
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell ' END '

Sheets("2").Activate
num = rng.Row

Sheets("1").Activate ' The idea of this and the following line is to print the result '

ActiveCell = Sheets("2").Cells(num, "A")

numresult = numresult + 1 ' Since I have space limit, with this command I can see if there was more results than space to print and inform the user '

Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> firstaddress

End If
End If
End With

If numresult > 32 Then ' I have up to 32 rows to put results. if it exceeds, the code will print all following codes into the last row and display that message '
MsgBox "You got too many results! Add more filters."
End If

[SamT]

'First Found Rng.Value = "Blah"
Set rng = .FindNext(rng) 'Find next "Blah".
'
For Each cell In blankrange.Cells 'Find next empty cell.
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
'
ActiveCell = Sheets("2").Cells(num, "A") 'Print first cell in Row to that next empty cell


Somewhere in Sheets("2").Range("$A$2:$AI$250"), you have the search term, (example: "Blah",) 7 times and the first cell in the row, (seven times) is "Cookies."



You said that you searched for "chocolate cookies brown", but
The DB entry that matches is, for example, "cookie chocolate brown handmade flour chocolate butter" (from "A1:G1")
Implies that each cell in your db only contains one word

You said, "when the code finds an entry that matches it will copy the whole DB's row"
That is wrong, it will only 'print' the cell in Column A of the Row. [Cells(num, "A")]

I hope this helps.

[lfalla]

EDIT:

Thanks A LOT.

Although I had replied with information, your answer made me realize what was wrong. The whole problem was the first defined range (A2:AI250). That would make the .Find search the whole range - finding all information on the row, counting all cells and then printing Cell(num,"A") N times (the number of used cells in the row). A simple change on that first Range did the magic.

Once again, thanks a lot!