User-1024101449 posted
Hi,
I have two sheets and i'm comparing the values.
But, i am unable to get any values in the second sheet. it says empty string.
what is the problem.
my code is below
--------------------
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim i, j As Long
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
For i = 16 To sh1.Range("C65536").End(xlUp).Row 'First Sheet Line Nos.
x = ExtractElement(Range("C" & i).Value, i, "-")
For j = 2 To sh2.Range("A65536").End(xlUp).Row 'Second Sheet Line Nos.
y = ExtractElementDest(Range("A" & j).Value, j, "-")
If x = y Then
sh1.Range("T" & i).Value = "Available"
GoTo Next_i
End If
Next
sh1.Range("T" & i).Value = "Not Available"
Next_i:
Next
Function ExtractSourceLine(str, n, sepChar)
' Returns the nth element from a string,
' using a specified separator character
Dim SplitLn1 As Variant
SplitLn1 = Split(str, sepChar)
For Idx_1 = 0 To UBound(SplitLn1)
If Idx_1 = 1 Then
LineWd1 = SplitLn1(1)
End If
If Idx_1 = 2 Then
LineWd2 = SplitLn1(2)
End If
ExtractSourceLine = LineWd1 & "-" & LineWd2 ' Get the Exact word to compare
Next
End Function
Function ExtractDestLine(str, n, sepChar)
' Returns the nth element from a string,
' using a specified separator character
Dim SplitLn1 As Variant
SplitLn1 = Split(str, sepChar)
For Idx_1 = 0 To UBound(SplitLn1)
If Idx_1 = 1 Then
LineWd1 = SplitLn1(1)
End If
If Idx_1 = 2 Then
LineWd2 = SplitLn1(2)
End If
ExtractDestLine = LineWd2 & "-" & LineWd1 ' Get the Exact word to compare
Next
End Function
