VBA question
 

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

VBA question
 

You could try

InStr(strNewNumber1,chr(9))

for tabs.
--
HTH,

Barb Reinhardt



"Tony in Michigan" wrote:

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

VBA question
 

Dim strRow As String
strRow = 2
strRow = strRow + 1

As a side note, you might want to reconsider changing this to a Long
data type, and using Cells(Row,Column) instead.
= = = = =
Dana DeLouis



On 11/17/09 4:28 PM, Tony in Michigan wrote:
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