View Full Version : Excel Functions in VBA

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
Cleanall = clean9
End If
Cleanall = clean8
End If
Cleanall = clean7
End If
Cleanall = clean6
End If
Cleanall = clean5
End If
Cleanall = clean4
End If
Cleanall = clean3
End If
Cleanall = clean2
End If
Cleanall = clean1
End If
Cleanall = clean
End If
End Function

Zack Barresse
12-20-2006, 05:23 PM
When using VBA, use the InStr() function instead. It is native.

As for the VLOOKUP, you still have access to that from..


12-20-2006, 05:30 PM
Can you also use:

Columns(Range("A").Find(What:="Text1", LookIn:=xlValues

to search or is InStr() the better way to go?

Zack Barresse
12-20-2006, 05:37 PM
The InStr() function is for looking for a value inside of a singular value, just as the SEARCH() function does (much like the FIND() function, but not case sensitive). What you have used (minus the "Columns(" portion, which is not needed) is the Find method, which is looking for a value over a range of more than one cell. Two very different actions. I will generally use the Find method instead of a Vlookup though. :)

12-20-2006, 05:42 PM
Good to know. Thanks for the clarification Zack.

Zack Barresse
12-20-2006, 05:53 PM
Renato, instead of editing your original thread, just use another post to amend the thread. Please specify exactly what it is you are trying to do. We want to know your methods and descriptions, not your formulas, those we will come up with. ;)

12-20-2006, 05:59 PM
OK Zack,

What Im trying to do is in the example:

11112.151.4 xxx yyy -- From this text, i want to retrieve only what it finds on the VLOOKUP.

11112.151.4 xxx yyy

My list is:

So it will retrieve only the 11112 from 11112.151.4 xxx yyy

Do you get, or should I explain better?

12-21-2006, 08:09 AM
Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 1)

Will return everything to the left of the first Space, (" "), ie "11112.151.4"

Try replacing " " with "."

Everything I know about VB I learned yesterday at VBAX

12-21-2006, 08:34 AM
I would suggest you remove your email address from the post. Otherwise some bot will pick it up and you will get tons of SPAM.
If you need to communicate it to someone, use a PM instead.


Zack Barresse
12-21-2006, 09:53 AM
Renato, please stop editing your posts or I will take that feature away from you. Just make another post.

Zack Barresse
12-21-2006, 09:56 AM
This will return your number...

Left(data, InStr(1, data, ".") - 1)

Then just use that as your first syntax in your Vlookup function.

12-21-2006, 11:24 AM
clean10 is declared as integer. Left is a string function.
Still, clean10 = Left(data, Len(Left(data, InStr(1, data, " ") - 1)) - 10)
doesn't go to error.

Zack Barresse
12-21-2006, 11:44 AM
The data format can be forced if it is recognized as such. It is not so efficient, kind of clumsy, but it won't give an error. You want an error? Use that function with a data without a space. ;)

12-21-2006, 11:56 AM
If you just want to retrieve the first number try this.

MsgBox Int(Val("11112.151.4 xxx yyy"))

Zack Barresse
12-21-2006, 12:11 PM
@Norie: .. only if there will always be a number starting any type of text string. Sure it's a handy byproduct of the Val() function, but I always hated banking on such a fluke.

Btw, let me know if you will have a need for error handling for a period not being there (as it will create an error.

12-21-2006, 12:21 PM

I realise that and was going to add a caveat but forgot.

Not sure what you mean about the period, it should work whenever the string starts with a number.

MsgBox Int(Val("11112ffff"))

MsgBox Int(Val(" 11112ffff"))

MsgBox Int(Val("11112 ffff"))

Zack Barresse
12-21-2006, 01:01 PM
I still find it messy.

Re the period, I was talking to the OP. Sorry for the confusion.

12-21-2006, 01:06 PM

Messy - have you seen the OP's code.:)

PS Realised you were probably addressing the OP, eventually.:oops: