By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,044 Members | 1,291 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,044 IT Pros & Developers. It's quick & easy.

Excel VBA: Search Problems

P: 21
I posted this question already (Admin Edit Need help with my search macro for excel), but I made a mistake in writing the question, so I posted a reply and I am afraid that at a glance it may be viewed as answered. Therefore, I am reposting my question correctly because I desperately need help! My boss needs this and I have tried to manipulate a code that was given to me on this site, but I am not versed in macros. My question is how to search multiple columns of data. I have a month in column B, a year in column C, and a person's name in column D. Below I have included the search macro I have that searches the one column, but I need to be able to search all three columns. I know how to make three input boxes to put the information in that I am searching for, but I don't know how to manipulate this code to search more than one column. Please, please, please help me with this because I am about to go crazy struggling to figure this out. Thank you so much in advance!

Expand|Select|Wrap|Line Numbers
  1. Sub TextBoxSearch_Click()
  2.      Dim searchMon As String
  3.  
  4.     searchMon = InputBox("Enter Entire Month ex:January")
  5.     If searchMon = "" Then Exit Sub
  6.     Sheets("Archive").Select
  7.     ActiveSheet.Unprotect
  8.     Range("DZ1").Value = searchMon
  9.     Range("DZ2").Activate
  10.     For i = 1 To 120
  11.             ActiveCell.Value = "=vlookup(DZ1,B4:DQ53," & i & ",FALSE)"
  12.         ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
  13.     Next i
  14.  
  15.     Range("A1").Select
  16.     ActiveSheet.Protect
  17.     Sheets("Searched Report").Select
  18.     Range("A1").Select
  19.  
  20. End Sub
Sep 5 '08 #1

✓ answered by MikeTheBike

The copy function works great, but is there a way to make three input boxes, or do I just have to type the month, year, and tech's name all in the one box? Sorry that I made this more complicated than it was. Sometimes we get focused on looking for one answer that we don't see the more simply answer right in front of our face.
Hi

I'm not sure this will help, but if we are using code (in Excel!) rather that worksheet function, then I would tend to write my own code/functions.

I you know the columns that the particular data is in then perhaps somethin like this or similar may be of interest
Expand|Select|Wrap|Line Numbers
  1. Sub FindRow(ByRef iRow As Long)
  2.     Const cMonthCol As Integer = 2
  3.     Const cYearCol As Integer = 3
  4.     Const cTechCol As Integer = 4
  5.     Const cStartRow As Long = 4
  6.  
  7.     Dim i As Long
  8.     Dim strMonth As String
  9.     Dim strYear As String
  10.     Dim strTech As String
  11.  
  12.     iRow = 0
  13.  
  14.     strMonth = InputBox("Enter Month")
  15.     If strMonth = "" Then Exit Sub
  16.  
  17.     strYear = InputBox("Enter Year")
  18.     If strYear = "" Then Exit Sub
  19.  
  20.     strTech = InputBox("Enter Technician")
  21.     If strTech = "" Then Exit Sub
  22.  
  23.     i = cStartRow
  24.  
  25.     Do Until Cells(i, cMonthCol) = ""
  26.         If Trim(UCase(Cells(i, cMonthCol))) = Trim(UCase(strMonth)) And Trim(UCase(Cells(i, cYearCol))) = Trim(UCase(strYear)) And Trim(UCase(Cells(i, cTechCol))) = Trim(UCase(strTech)) Then
  27.             iRow = i
  28.             Exit Do
  29.         End If
  30.         i = i + 1
  31.     Loop
  32. End Sub
  33.  
  34. Sub TestRowFound()
  35.     Dim i As Long
  36.  
  37.     FindRow i
  38.     If i <> 0 Then
  39.         MsgBox "Match found in row " & i & "."
  40.     Else
  41.         MsgBox "No match found."
  42.     End If
  43. End Sub
??

Of course there are many ways of doing this, defining the search range etc., it just depend on layout/circumstances.


MTB

Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,429
This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.
Table Name=[tblStudent]
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
It would also help to understand what you're after if you were to provide a few examples of how you expect this to work. I'm not at all clear on how you intend to use a multi-column filter. Would you enter the search items in three separate controls on your form? If not, what do you envisage?
Sep 6 '08 #2

NeoPa
Expert Mod 15k+
P: 31,429
Well, you can probably scratch all that as I now find that this question in the MS Access / VBA forum is actually an Excel question.

Let me rename the thread to something that makes better sense then I may have another look.
Sep 6 '08 #3

NeoPa
Expert Mod 15k+
P: 31,429
Right, now we all understand that you're talking about an Excel issue...

You need to explain what you're trying to achieve a lot better than you have.

Please provide a few examples of how you expect this to work. Would you enter the search items separately? If not, what do you envisage?
Sep 6 '08 #4

P: 21
Right, now we all understand that you're talking about an Excel issue...

You need to explain what you're trying to achieve a lot better than you have.

Please provide a few examples of how you expect this to work. Would you enter the search items separately? If not, what do you envisage?
Yes, you would enter the three searches separately. When you click on a button it would pull up one box that said enter month when you enter the month and click ok another box would open that said enter year when you enter the year and click ok a final box would open that said enter technicians name when you did so and clicked ok the search for those three things would begin. The search would result in a match on one row (where a lot of other information is located in different columns). Then that entire row would be copied to another row where I have set links to make a report. I have made the boxes happen, but I don't know how to make the search actually run. I have no real training on macros only looking at examples that others have done and trial and error from there. If there is another way to search for those three things let me know, I am open to anything. I hope this better explains what I am trying to do.
Sep 8 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi. I haven't got an immediate solution for you but at least we know more about what you need. The method used for your previous search relied on the data you are searching for being ordered in ascending order - otherwise VLOOKUP would not have worked. You can't easily use a variant of that approach this time because VLOOKUP (or its column-wise counterpart HLOOKUP) are single-value comparison functions, and finding a match in a row of the first column does not necessarily mean that you are near the right row at all when there are two other values to consider.

If it wasn't for your row copy and report linkage I would simply suggest you link the Excel worksheet to Access and use an access parameter query to find the matching record for you, but this won't do your copy and link bit for you.

Excel is an excellent financial spreadsheet but a very poor database - and perhaps you might be better off convincing your boss to let you use a real database to handle such work.

-Stewart
Sep 8 '08 #6

NeoPa
Expert Mod 15k+
P: 31,429
While showing a fairly in-depth knowledge of how VLookup() works Stewart, I think you may be mistaken about this particular call.

When Range_Lookup is set to TRUE, it is indeed true that sorted data is expected (if it's to work reliably). However, when set to FALSE it searches through the data until it finds an exact match, regardless of the order of the data.
Sep 8 '08 #7

NeoPa
Expert Mod 15k+
P: 31,429
Yes, you would enter the three searches separately. When you click on a button it would pull up one box that said enter month when you enter the month and click ok another box would open that said enter year when you enter the year and click ok a final box would open that said enter technicians name when you did so and clicked ok the search for those three things would begin. The search would result in a match on one row (where a lot of other information is located in different columns). Then that entire row would be copied to another row where I have set links to make a report. I have made the boxes happen, but I don't know how to make the search actually run. I have no real training on macros only looking at examples that others have done and trial and error from there. If there is another way to search for those three things let me know, I am open to anything. I hope this better explains what I am trying to do.
As I'm a little the worse for wear at the moment ;), I will indicate an approach that I use when either searching or sorting on multiple column values. If this isn't clear enough I can flesh it out when I'm more compos mentis.

Produce a column at the left of your data which is a composite of the three columns you need to search. For string data this is fairly straightforward (=B2 & C2 & D2 ==> copied down column A).

Simply then, search in this column for the string built up from the three values entered.

Let me know how you get on.
Sep 8 '08 #8

Expert Mod 2.5K+
P: 2,545
Oops. Sorry for that overlook. NeoPa is indeed correct - data does not need to be sorted for it to work as long as the range indicator is set false. I do too many reference tables which are always sorted, and confused myself here.

NeoPa's suggestion of combining the three columns is good, and indeed I think it is the only practicable way to achieve the outcome and retain the spreadsheet approach.

-Stewart
...When Range_Lookup is set to TRUE, it is indeed true that sorted data is expected (if it's to work reliably). However, when set to FALSE it searches through the data until it finds an exact match, regardless of the order of the data.
Sep 8 '08 #9

P: 21
The copy function works great, but is there a way to make three input boxes, or do I just have to type the month, year, and tech's name all in the one box? Sorry that I made this more complicated than it was. Sometimes we get focused on looking for one answer that we don't see the more simply answer right in front of our face.
Sep 9 '08 #10

Expert 100+
P: 634
The copy function works great, but is there a way to make three input boxes, or do I just have to type the month, year, and tech's name all in the one box? Sorry that I made this more complicated than it was. Sometimes we get focused on looking for one answer that we don't see the more simply answer right in front of our face.
Hi

I'm not sure this will help, but if we are using code (in Excel!) rather that worksheet function, then I would tend to write my own code/functions.

I you know the columns that the particular data is in then perhaps somethin like this or similar may be of interest
Expand|Select|Wrap|Line Numbers
  1. Sub FindRow(ByRef iRow As Long)
  2.     Const cMonthCol As Integer = 2
  3.     Const cYearCol As Integer = 3
  4.     Const cTechCol As Integer = 4
  5.     Const cStartRow As Long = 4
  6.  
  7.     Dim i As Long
  8.     Dim strMonth As String
  9.     Dim strYear As String
  10.     Dim strTech As String
  11.  
  12.     iRow = 0
  13.  
  14.     strMonth = InputBox("Enter Month")
  15.     If strMonth = "" Then Exit Sub
  16.  
  17.     strYear = InputBox("Enter Year")
  18.     If strYear = "" Then Exit Sub
  19.  
  20.     strTech = InputBox("Enter Technician")
  21.     If strTech = "" Then Exit Sub
  22.  
  23.     i = cStartRow
  24.  
  25.     Do Until Cells(i, cMonthCol) = ""
  26.         If Trim(UCase(Cells(i, cMonthCol))) = Trim(UCase(strMonth)) And Trim(UCase(Cells(i, cYearCol))) = Trim(UCase(strYear)) And Trim(UCase(Cells(i, cTechCol))) = Trim(UCase(strTech)) Then
  27.             iRow = i
  28.             Exit Do
  29.         End If
  30.         i = i + 1
  31.     Loop
  32. End Sub
  33.  
  34. Sub TestRowFound()
  35.     Dim i As Long
  36.  
  37.     FindRow i
  38.     If i <> 0 Then
  39.         MsgBox "Match found in row " & i & "."
  40.     Else
  41.         MsgBox "No match found."
  42.     End If
  43. End Sub
??

Of course there are many ways of doing this, defining the search range etc., it just depend on layout/circumstances.


MTB
Sep 9 '08 #11

NeoPa
Expert Mod 15k+
P: 31,429
Interesting code Mike, but Excel search functions are highly optimised and can perform at many times the speed of VBA code checking each cell.
Sep 9 '08 #12

NeoPa
Expert Mod 15k+
P: 31,429
The copy function works great, but is there a way to make three input boxes, or do I just have to type the month, year, and tech's name all in the one box? Sorry that I made this more complicated than it was. Sometimes we get focused on looking for one answer that we don't see the more simply answer right in front of our face.
It's certainly possible for all three to be called from the same piece of code. It's not necessary for each to be triggered by separate events (button clicks etc).

I don't know what code you're using at the moment, but if you post it I can tell you how you can combine it into a single triggered routine.
Sep 9 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.