Find 2 values in 2 columns

[Wim Geuens]

Hi,

i'm looking for the code that helps me to find an exact match of the values in 2 columns.
The macro will get 2 values of a userformbox and needs to find value 1 in column A and value 2 in column B. It may only return the rownumber when the 2 values matches.

I have the code below, but I did something wrong because it will go to column B for a 20 times to find the value but doesn't keep in mind that value A needs to be exact as well

Thanks,
W

  Sheets("INPUT").Activate
  Type = Range("B5").Value
  Name = Range("B4").Value
 
  For j = 0 To 20
  With Sheets(maand).Range("A:A")
  Set rng = .Find(What:=Name, _
  After:=.Cells(.Cells.Count), _
  LookIn:=xlValues, _
  LookAt:=xlWhole, _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, _
  MatchCase:=False)
  If Not rng Is Nothing Then
  rng.Select
  Random = rng.Offset(j, 1).Value
 
  If Random = "Holiday" Then
  vRow = ActiveCell.Row
  Cells(vRow, 3).Interior.ColorIndex = 37
  End If
 
  Else
  End If
  End With
 
  Next

 

[Marc L]

Hi,

check and correct the Offset property !
​

 

[Wim Geuens]

Doesn't work, because it doesn't keep the value from "Name" in mind

Regards,
Wim

 

[Marc L]

Chech first if your Find function works for column A.
If works, you must correct Offset property !​

 

[shrivallabha]

There must be good reason why you are resorting to VBA because the same can also be achieved using native excel.

DV can be used for creating simple drop downs for user input.

I am uploading a file which you can check if it suits your purpose. One is normal formula with helper column and the other is array formula without helper column.