locked
Sheet value not fetching properly RRS feed

  • Question

  • 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

     

    Tuesday, October 4, 2011 5:14 AM

Answers

All replies