Data Type Change

[wilgenzt]

Hi this is my first time using this and I am somewhat of a beginner when it comes to Visual Basic. I was trying to make a code that would read in two columns of script and check if there is any new data in the new script that is not in the old script and then record that data. Here is what I have so far.

Function PropLed(newcol As Range, oldcol As Range) As Variant

' Variables and Constants
Dim newcheck As String, oldcheck As String
Dim n As Integer, i As Integer, m As Integer
n = newcol.Count
m = 0
ReDim sol(1 To n) As String

' For loop to read through everything
For i = 1 To n
newcheck = WorksheetFunction.Lookup(newcol(i), oldcol, oldcol)

' If statement to check if it is in the new sheet
If newcheck = "#N/A" Then
m = m + 1
sol(m) = newcol(i)
Else
End If
Next i

'Solution Output
PropLed = WorksheetFunction.Transpose(sol)
End Function

The error is the standard #VALUE! value is in the wrong data type. I am reading in script and I am trying to output script. Should I be using a different function other that Lookup? Or is what I am asking not possible? Again I don't know exactly what I can and can't do here. Any help at all would be greatly appreciated.

 

[pbornemeier]

Welcome to the Board!

When calling the function with:

PropLed range("A1:A8"), range("B1:B7")

I get Run-time error 1004, "Unable to get the Lookup property of the WorksheetFunction class
on this line:

newcheck = WorksheetFunction.Lookup(newcol(i), oldcol, oldcol)

The LOOKUP function requires that the search range is sorted in ascending order.

I would use this:

Function PropLed2(rngNew As range, rngOld As range) As Variant
    'Search each cell in rngOld for each cell in rngNew
    'If a rngNew cell is not found add it to varSolution

    Dim lNewCellCount As Long, lX As Long
    Dim varSolution() As String, lOutputCount As Long
    Dim oFound As Object
    
    lNewCellCount = rngNew.Count
    lOutputCount = 0
    
    ' For loop to read through everything
    For lX = 1 To lNewCellCount
        Set oFound = rngOld.Find(rngNew(lX), LookIn:=xlFormulas, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
            SearchFormat:=False)
        If oFound Is Nothing Then
            lOutputCount = lOutputCount + 1
            ReDim Preserve varSolution(1 To lOutputCount)
            varSolution(lOutputCount) = rngNew(lX).Value
        End If
    Next lX
    
    'Solution Output
    PropLed2 = WorksheetFunction.Transpose(varSolution)
    
    Set oFound = Nothing
    
End Function

 

[wilgenzt]

Thank you very much pbornemeier. It works extremely well. I wish I understood it a little better though. I am also trying to save the row number that I find all of the unique items in so I can later read that row and save the row to the new sheet. But you don't have to do that for me. I will play around with your code and see what I can come up with. Again thank you very much.