lfalla
09-27-2016, 11:10 AM
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
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