I have two cells in one sheet - lets call it DATA sheet. and I have another sheet - lets call it SEARCH sheet. I want to compare the two cells in DATA, with the info I have a SEARCH.
lets say, if: (cell A.value in DATA is in column X in SEARCH) and (cell B.value in DATA in column Y in SEARCH) - both in the same row in SEARCH - then give me the first value of this row in sheet SEARCH.
is it possible with VBA?
thanks
Yes it's possible :) You have 3 way:
1. Loop (example For)
Function FindPair(ByRef CellA As Range, ByRef CellB As Range) As Variant ' <- Change Variant for specific Type of data
'Set default value if not found match
Set FindPair = Nothing
Dim sheetToSearch As Worksheet
Dim lastRow As Long
'Check input
If CellA Is Nothing Or CellB Is Nothing Then
Call MsgBox("Something is wrong!", vbExclamation)
Exit Function
Exit Function
'For easy ro read
Set sheetToSearch = Sheets("Search")
lastRow = (sheetToSearch.UsedRange.Cells(1, 1).Row + sheetToSearch.UsedRange.Rows.Count) 'First row of UsedRange + rows count
'Look up
For r = 0 To lastRow
'Change column here, if you need
If sheetToSearch.Range("A" + r).Value = CellA.Value Then
If sheetToSearch.Range("B" + r).Value = CellB.Value Then
'Return data
FindPair = sheetToSearch.Range("A" + r).Value
Exit For
End If
End If
Next r
'You can compare *.Value = *.Value - for compare binary data
'or *.Text = *.Text - for compare value like as text with formatting
End Function
2. Find
Application.WorksheetFunction.Find()
Please look for documentation how it works: VBA Find
3. Vlookup
Application.WorksheetFunction.VLookup()
Please look for documentation how it works: Excel VLookup
Hope you looking for this..
Sub comp_value_in_diff_cells()
On Error Resume Next
lastrowA = Worksheets("DATA").Range("A" & Rows.Count).End(xlUp).Row
lastrowB = Worksheets("DATA").Range("B" & Rows.Count).End(xlUp).Row
If lastrowA = lastrowB Then
For i = 1 To lastrowA
search1 = Worksheets("DATA").Range("A" & i).Value
search2 = Worksheets("DATA").Range("B" & i).Value
With Worksheets("SEARCH")
For j = 1 To (.UsedRange.Cells(1, 1).Row + .UsedRange.Rows.Count)
a = .Range(j & ":" & j).Find(search1).Row
avalue = .Range(j & ":" & j).Find(search1).Column
b = .Range(j & ":" & j).Find(search2).Row
bvalue = .Range(j & ":" & j).Find(search2).Column
If a = b Then
If avalue < bvalue Then
If Worksheets("DATA").Range("C" & i).Value <> "" Then
Worksheets("DATA").Range("C" & i).Value = Worksheets("DATA").Range("C" & i).Value & "|" & search1
Else
Worksheets("DATA").Range("C" & i).Value = search1
End If
Else
If Worksheets("DATA").Range("C" & i).Value <> "" Then
Worksheets("DATA").Range("C" & i).Value = Worksheets("DATA").Range("C" & i).Value & "|" & search2
Else
Worksheets("DATA").Range("C" & i).Value = search2
End If
End If
End If
Next j
End With
Next i
Else
Msgbox ("DATA Sheet A & B Mismatch")
End If
End Sub
.UsedRange.Rows.Count is tricky in Excel 2016 (I don't remember how it works on previous versions)... Not always UsedRange starts form Row=1... It can be start - for example - form Row=5... then last row is not Rows.Count ! If I can advise - better approach is: lastRow = (sheet.UsedRange.Cells(1, 1).Row + sheet.UsedRange.Rows.Count)