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

Need help with my search macro for excel

P: 21
I recently received help writing a search macro for excel, but now I am trying to change it up to use it in another spreadsheet, and I am having some troubles. I need it to search two cells one has a month (cell B4) and the other has a year(cell B5). The macro that I have works great for searching just the month, but I don't know how to change it to incorporate another cell with the year in it. I really appreciate the help this forum provides.
Below is the macro I am currently using.

Expand|Select|Wrap|Line Numbers
  1. Sub TextBox11_Click()
  2.      Dim searchNum As String
  3.  
  4.     searchNum = InputBox("Enter Month Year")
  5.     If searchNum = "" Then Exit Sub
  6.     Sheets("Archive").Select
  7.     Range("B99").Value = searchNum
  8.     Range("B100").Activate
  9.     For i = 1 To 120
  10.             ActiveCell.Value = "=vlookup(B99,B4:DQ53," & i & ",FALSE)"
  11.         ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
  12.     Next i
  13.  
  14.     Range("A1").Select
  15.     Sheets("Searched Report").Select
  16.     Range("A1").Select
  17.  
  18. End Sub
Aug 26 '08 #1
Share this Question
Share on Google+
2 Replies


P: 21
Sorry I made a mistake the month is in column B starting in row 4 and the year is in column C starting in row 4! I also would like for there to be two separate text boxes for the month and year, which I know how to do that but I don't know how to combine the two columns into the search results. The same row will always have the month and year I am searching for if that helps at all. I have changed my code to incorporate the two text boxes to enter my search in, so I am going to relist it.

Expand|Select|Wrap|Line Numbers
  1. Sub TextBoxSearch_Click()
  2.      Dim searchNum As String
  3.      Dim intRng As Integer
  4.  
  5.     searchNum = InputBox("Enter Month")
  6.     If searchNum = "" Then Exit Sub
  7.     intRng = InputBox("Enter Year")
  8.     Sheets("Archive").Select
  9.     ActiveSheet.Unprotect
  10.     Range("B99").Value = searchNum
  11.     Range("C99").Value = intRng
  12.     Range("B100").Activate
  13.     For i = 1 To 120
  14.             ActiveCell.Value = "=vlookup(B99,B4:DQ53," & i & ",FALSE)"
  15.         ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
  16.     Next i
  17.  
  18.     Range("A1").Select
  19.     ActiveSheet.Protect
  20.     Sheets("Searched Report").Select
  21.     Range("A1").Select
  22.  
  23. End Sub
Thanks again
Aug 28 '08 #2

NeoPa
Expert Mod 15k+
P: 31,419
This question was asked incorrectly, so the OP reposted as Excel VBA: Search Problems.
Sep 6 '08 #3

Post your reply

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