Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split values in cells to variables
I have a quick question
I need help with a basic macro that will enable me to search a cell that has lastname and first name seperated by a comma (lastname, firstname). All I need to do is create two variables that will only have either the first name or last name. For example Variable1 = Last name Variable2 = First name Once I am able to do this then I should be able to add it to my code Thank you for all of your help in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split values in cells to variables
It isn't clear if you need to (1) combine existing FirstName and LastName
variables before searching for the concatenation of variable, or (2) whether you need to split the found result into FirstName and LastName. For (1), use code like Dim FoundCell As Range Dim LastName As String Dim FirstName As String LastName = "pearson" FirstName = "chip" Set FoundCell = Range("A1:A10").Find(what:=LastName & ", " & FirstName, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If FoundCell Is Nothing Then Debug.Print "NOT FOUND" Else Debug.Print "FOUND AT: " & FoundCell.Address End If For (2), use code like: Dim FoundCell As Range Dim LastName As String Dim FirstName As String Dim VV As Variant Set FoundCell = Range("A1:A10").Find(what:="pearson, chip", _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If FoundCell Is Nothing Then Debug.Print "NOT FOUND" Else VV = Split(FoundCell.Text, ",") LastName = Trim(VV(0)) FirstName = Trim(VV(1)) Debug.Print "Last Name: " & LastName, _ "First Name: " & FirstName End If -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) wrote in message ... I have a quick question I need help with a basic macro that will enable me to search a cell that has lastname and first name seperated by a comma (lastname, firstname). All I need to do is create two variables that will only have either the first name or last name. For example Variable1 = Last name Variable2 = First name Once I am able to do this then I should be able to add it to my code Thank you for all of your help in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split values in cells to variables
Assuming you know how to loop through cells one possibility for the code
would be firstName = Mid(mystring, 1, InStr(mystring, ",") - 1) lastName = Mid(mystring, InStr(mystring, ",") + 1) -- Best regards, Edward " wrote: I have a quick question I need help with a basic macro that will enable me to search a cell that has lastname and first name seperated by a comma (lastname, firstname). All I need to do is create two variables that will only have either the first name or last name. For example Variable1 = Last name Variable2 = First name Once I am able to do this then I should be able to add it to my code Thank you for all of your help in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split values in cells to variables
I interpreted your question as wanting to parse ("search") a cell that you
know has the "last, first" string in it. Dim words() As String Dim variable1 As String Dim variable2 As String ' Replace 'ActiveCell.Value' with your cell reference words = Split(ActiveCell.Value, ",") ' Bounds check words() to be sure you have two words ' Trim() removes surrounding white space: " Bob" - "Bob" variable1 = Trim(words(0)) variable2 = Trim(words(1)) " wrote: I have a quick question I need help with a basic macro that will enable me to search a cell that has lastname and first name seperated by a comma (lastname, firstname). All I need to do is create two variables that will only have either the first name or last name. For example Variable1 = Last name Variable2 = First name Once I am able to do this then I should be able to add it to my code Thank you for all of your help in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split values in cells to variables
On Jan 14, 5:21*pm, CjimO wrote:
I interpreted your question as wanting to parse ("search") a cell that you know has the "last, first" string in it. Dim words() As String Dim variable1 As String Dim variable2 As String ' Replace 'ActiveCell.Value' with your cell reference words = Split(ActiveCell.Value, ",") ' Bounds check words() to be sure you have two words ' Trim() removes surrounding white space: *" Bob" - "Bob" variable1 = Trim(words(0)) variable2 = Trim(words(1)) " wrote: I have a quick question I need help with a basic macro that will enable me to search a cell that has lastname and first name seperated by a comma (lastname, firstname). *All I need *to do is create two variables that will only have either the first name or last name. *For example Variable1 = Last name Variable2 = First name Once I am able to do this then I should be able to add it to my code Thank you for all of your help in advance- Hide quoted text - - Show quoted text - Thank you. Everything worked out perfectly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA variables retaining their values | Excel Discussion (Misc queries) | |||
how to look up values with two independent variables | Excel Worksheet Functions | |||
Formula to Check Values between to variables | Excel Worksheet Functions | |||
Chart two variables with differing values | Charts and Charting in Excel | |||
Split values from one cell to dif. cells | Excel Discussion (Misc queries) |