T
Tony in Michigan
A co-worker, who has since moved on to greener pastures, coded the following
for me. I use it to explode the contents of partlists, where all the part
numbers of a particular type are stored in a cell. Now I am receiving the
lists from new sources, and need to update the code a bit, something I am
completely unfamiliar with.
In this statement If InStr(strNewNumber1, " ") > 0 Then how would I also
include (TAB) and other pseudo space charecters, so it will work on files
produced by people who refuse to use the space bar?
Function Explode()
Dim strRow As String
Dim strNewNumber1 As String
Dim strNewNumber2 As String
Dim intPosition As Integer
strRow = 2
Do Until Range("B" & strRow).Value = ""
Range("B" & strRow).Select
strNewNumber1 = Trim(Range("B" & strRow).Value)
If InStr(strNewNumber1, " ") > 0 Then
intPosition = InStr(strNewNumber1, " ")
strNewNumber2 = Left(strNewNumber1, intPosition - 1)
strNewNumber1 = Trim(Mid(strNewNumber1, intPosition))
Rows(strRow & ":" & strRow).Select
Selection.Copy
Rows(strRow + 1 & ":" & strRow + 1).Select
Selection.Insert Shift:=xlDown
Range("B" & strRow).Value = strNewNumber2
Range("B" & strRow + 1).Value = strNewNumber1
End If
strRow = strRow + 1
Loop
End Function
for me. I use it to explode the contents of partlists, where all the part
numbers of a particular type are stored in a cell. Now I am receiving the
lists from new sources, and need to update the code a bit, something I am
completely unfamiliar with.
In this statement If InStr(strNewNumber1, " ") > 0 Then how would I also
include (TAB) and other pseudo space charecters, so it will work on files
produced by people who refuse to use the space bar?
Function Explode()
Dim strRow As String
Dim strNewNumber1 As String
Dim strNewNumber2 As String
Dim intPosition As Integer
strRow = 2
Do Until Range("B" & strRow).Value = ""
Range("B" & strRow).Select
strNewNumber1 = Trim(Range("B" & strRow).Value)
If InStr(strNewNumber1, " ") > 0 Then
intPosition = InStr(strNewNumber1, " ")
strNewNumber2 = Left(strNewNumber1, intPosition - 1)
strNewNumber1 = Trim(Mid(strNewNumber1, intPosition))
Rows(strRow & ":" & strRow).Select
Selection.Copy
Rows(strRow + 1 & ":" & strRow + 1).Select
Selection.Insert Shift:=xlDown
Range("B" & strRow).Value = strNewNumber2
Range("B" & strRow + 1).Value = strNewNumber1
End If
strRow = strRow + 1
Loop
End Function