PDA

View Full Version : Excel Functions in VBA



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

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..

Application.WorksheetFunctions.Vlookup()

CodeMakr
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. :)

CodeMakr
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. ;)

RenatoM
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.

Example2:
11112.151.4 xxx yyy

My list is:
12345
123456
11112

So it will retrieve only the 11112 from 11112.151.4 xxx yyy


Do you get, or should I explain better?

SamT
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

CBrine
12-21-2006, 08:34 AM
RenatorM,
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.

Cal

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.

JimmyTheHand
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.
Why?

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. ;)

Norie
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.

Norie
12-21-2006, 12:21 PM
firefytr

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.

Norie
12-21-2006, 01:06 PM
firefytr

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

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