Hi,
I tried the code given by you with the my requirement but the code is not giving me the output which was working in the sheet which you have given
Sub Button1_Click()
Set myrng = Sheets("Sheet1").Range("A2:AF4614")
myRangeVals = myrng.Value
LookupVals = Sheets("Sheet2").Range("A2:C21").Value
myResults = myRangeVals
End If
For aj = 1 To UBound(myRangeVals)
myResults(aj, 1) = Empty
ExtractFirstNo = Empty
c = Split(myRangeVals(aj, 1))
If IsArray(c) Then
For ai = 0 To UBound(c)
Z = Evaluate("Value(" & c(ai) & ")")
If Not IsError(Z) Then
ExtractFirstNo = Z
Exit For
End If
Next ai
If Not IsEmpty(ExtractFirstNo) Then
For ak = 1 To UBound(LookupVals)
If LookupVals(ak, 1) = ExtractFirstNo Then
If LookupVals(ak, 2) = myRangeVals(aj, 2) Then
myResults(aj, 1) = LookupVals(ak, 3)
Exit For
End If
End If
Next ak
End If
Else
End If
Next aj
myrng.Offset(, myrng.Columns.Count).Resize(, 1).Value = myResults
End Sub
Function Findresult(myText, Description, MatchRange, ShortdescriptionColumn, DescriptionColumn, resultColumn)
a = Split(myText)
If IsArray(c) Then
For ai = 0 To UBound(a)
Z = Evaluate("Value(" & c(ai) & ")")
If Not IsError(Z) Then
ExtractFirstNo = Z
Exit For
End If
Next i
MRVals = MatchRange.Value
For i = 1 To UBound(MRVals)
If MRVals(i, ShortdescriptionColumn) = ExtractFirstNo Then
If MRVals(i, DescriptionColumn) = City Then
FindArea = MRVals(i, resultColumn)
End If
End If
Next ai
End If
End Function
Total columns i have is from A to AG and i am comparing C & D columns and writing the output to E column
I am unable to upload the sheet thats the reason why i am copying the code or content of the sheet.
Please suggest
Below was the code shared by you
Sub asdasd()
Set myrng = Sheets("Sheet1").Range("A2:D8")
myRangeVals = myrng.Value
LookupVals = Sheets("Sheet2").Range("A3:C9").Value
myResults = myRangeVals
For j = 1 To UBound(myRangeVals)
myResults(j, 1) = Empty
ExtractFirstNo = Empty
a = Split(myRangeVals(j, 1))
If IsArray(a) Then
For i = 0 To UBound(a)
Z = Evaluate("Value(" & a(i) & ")")
If Not IsError(Z) Then
ExtractFirstNo = Z
Exit For
End If
Next i
If Not IsEmpty(ExtractFirstNo) Then
For k = 1 To UBound(LookupVals)
If LookupVals(k, 1) = ExtractFirstNo Then
If LookupVals(k, 2) = myRangeVals(j, 2) Then
myResults(j, 1) = LookupVals(k, 3)
Exit For
End If
End If
Next k
End If
Else
End If
Next j
myrng.Offset(, myrng.Columns.Count).Resize(, 1).Value = myResults
End Sub
Function FindArea(myText, City, MatchRange, InvoiceColumn, CityColumn, AreaColumn)
a = Split(myText)
If IsArray(a) Then
For i = 0 To UBound(a)
Z = Evaluate("Value(" & a(i) & ")")
If Not IsError(Z) Then
ExtractFirstNo = Z
Exit For
End If
Next i
MRVals = MatchRange.Value
For i = 1 To UBound(MRVals)
If MRVals(i, InvoiceColumn) = ExtractFirstNo Then
If MRVals(i, CityColumn) = City Then
FindArea = MRVals(i, AreaColumn)
End If
End If
Next i
End If
End Function