RenatoM
12-20-2006, 05:06 PM
Im trying to do this...
Why doesnt it work???
Function Cleanall(data)
Dim rang As Range
Dim clean, clean1, clean2, clean3, clean4, clean5, clean6, clean7, clean8, clean9, clean10 As Integer
rang1 = Worksheets("Xref").Range("A:B")
clean = Left(data, InStr(1, data, " ") - 1)
clean1 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 1)
clean2 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 2)
clean3 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 3)
clean4 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 4)
clean5 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 5)
clean6 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 6)
clean7 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 7)
clean8 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 8)
clean9 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 9)
clean10 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 10)
proc = Application.WorksheetFunction.VLookup(clean, rang1, 2, 0)
proc1 = Application.WorksheetFunction.VLookup(clean1, rang1, 2, 0)
Proc2 = Application.WorksheetFunction.VLookup(clean2, rang1, 2, 0)
Proc3 = Application.WorksheetFunction.VLookup(clean3, rang1, 2, 0)
Proc4 = Application.WorksheetFunction.VLookup(clean4, rang1, 2, 0)
Proc5 = Application.WorksheetFunction.VLookup(clean5, rang1, 2, 0)
Proc6 = Application.WorksheetFunction.VLookup(clean6, rang1, 2, 0)
Proc7 = Application.WorksheetFunction.VLookup(clean7, rang1, 2, 0)
Proc8 = Application.WorksheetFunction.VLookup(clean8, rang1, 2, 0)
Proc9 = Application.WorksheetFunction.VLookup(clean9, rang1, 2, 0)
Proc10 = Application.WorksheetFunction.VLookup(clean10, rang1, 2, 0)
If IsError(proc) Then
If IsError(proc1) Then
If IsError(Proc2) Then
If IsError(Proc3) Then
If IsError(Proc4) Then
If IsError(Proc5) Then
If IsError(Proc6) Then
If IsError(Proc7) Then
If IsError(Proc8) Then
If IsError(Proc9) Then
Cleanall = clean10
Else
Cleanall = clean9
End If
Else
Cleanall = clean8
End If
Else
Cleanall = clean7
End If
Else
Cleanall = clean6
End If
Else
Cleanall = clean5
End If
Else
Cleanall = clean4
End If
Else
Cleanall = clean3
End If
Else
Cleanall = clean2
End If
Else
Cleanall = clean1
End If
Else
Cleanall = clean
End If
End Function
Why doesnt it work???
Function Cleanall(data)
Dim rang As Range
Dim clean, clean1, clean2, clean3, clean4, clean5, clean6, clean7, clean8, clean9, clean10 As Integer
rang1 = Worksheets("Xref").Range("A:B")
clean = Left(data, InStr(1, data, " ") - 1)
clean1 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 1)
clean2 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 2)
clean3 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 3)
clean4 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 4)
clean5 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 5)
clean6 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 6)
clean7 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 7)
clean8 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 8)
clean9 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 9)
clean10 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 10)
proc = Application.WorksheetFunction.VLookup(clean, rang1, 2, 0)
proc1 = Application.WorksheetFunction.VLookup(clean1, rang1, 2, 0)
Proc2 = Application.WorksheetFunction.VLookup(clean2, rang1, 2, 0)
Proc3 = Application.WorksheetFunction.VLookup(clean3, rang1, 2, 0)
Proc4 = Application.WorksheetFunction.VLookup(clean4, rang1, 2, 0)
Proc5 = Application.WorksheetFunction.VLookup(clean5, rang1, 2, 0)
Proc6 = Application.WorksheetFunction.VLookup(clean6, rang1, 2, 0)
Proc7 = Application.WorksheetFunction.VLookup(clean7, rang1, 2, 0)
Proc8 = Application.WorksheetFunction.VLookup(clean8, rang1, 2, 0)
Proc9 = Application.WorksheetFunction.VLookup(clean9, rang1, 2, 0)
Proc10 = Application.WorksheetFunction.VLookup(clean10, rang1, 2, 0)
If IsError(proc) Then
If IsError(proc1) Then
If IsError(Proc2) Then
If IsError(Proc3) Then
If IsError(Proc4) Then
If IsError(Proc5) Then
If IsError(Proc6) Then
If IsError(Proc7) Then
If IsError(Proc8) Then
If IsError(Proc9) Then
Cleanall = clean10
Else
Cleanall = clean9
End If
Else
Cleanall = clean8
End If
Else
Cleanall = clean7
End If
Else
Cleanall = clean6
End If
Else
Cleanall = clean5
End If
Else
Cleanall = clean4
End If
Else
Cleanall = clean3
End If
Else
Cleanall = clean2
End If
Else
Cleanall = clean1
End If
Else
Cleanall = clean
End If
End Function