Find cell value

[MrThor]

Hi

I want to find a specific cell on the same row as three different combobox values. So I want to find the row where column A is depending on combobox 1 column B on combobox 2 and C on combobox 3 and then I want the cell from Column D on the same row as those values. I have the following code which just gives me the cell depending on the first combobox which is not correct in the end:

Dim rng1 As Range
Dim year As String


Dim rng2 As Range
Dim month As String


Dim rng3 As Range
Dim day As String


Dim rownumber As Long


year = ComboBox1.Value
month = ComboBox2.Value
day = ComboBox3.Value




Set rng1 = Sheets("Sheet2").Columns("A:A").Find(What:=year, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Set rng2 = Sheets("Sheet2").Columns("B:B").Find(What:=month, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Set rng3 = Sheets("Sheet2").Columns("C:C").Find(What:=day, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)


rownumber = rng1.Row


Sheets("Sheet1").Cells(1, 1) = Sheets("Sheet2").Cells(rownumber, 4)

 

[mumps]

The "Resize(, 4)" expands the range to search to columns A to D not just column A. If it's not working for you, do you get an error message? If so, what is the error and which line of code is highlighted when you click "Debug"? Better still, it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[MrThor]

Hi, your code worked perfectly! Thank you

 

[mumps]

My pleasure.

 

[DanteAmor]

Hi, your code worked perfectly! Thank you

You're welcome!

 

[MrThor]

Hi, If I want to use your code but change the value in combobox2 to a text value (ie. "january") instead of a number, how do I do that? For now it does only work with numbers.

Regards

 

[mumps]

Does Column B contain a text value like "January" or does it still contain a number?