Anybody Real Good with Excel VBA? 1

[pcdaveh]

Anybody Real Good with Excel VBA?

 

[huzza]

It depends what you want. Post the details.

Richard

 

[pcdaveh]

I'm trying to parse a file. All data is in Column A.
I'm trying to find the first occurance of a space. Select everything to the left of the space and then move it to let say column B. The following bit of code works to find the space but I can't seem to select data to the left. Is this possible to do. When you run the recorder and select just a portion of the text in a cell then cut and paste, the code acts as though you selected the entire contents of the cell. I need to get the content of what is left of the space into let's say column B


Option Explicit
Sub mytest()


Dim li_Location As Integer
Dim ls_CellVall As String
Dim ls_Addvalue As String

li_Location = 1


Range("A" & li_Location).Select
ls_CellVall = ActiveCell.Value
ls_Addvalue = InStr(1, ls_CellVall, " &quot
MsgBox ("The first blank is in position" & " " & ls_Addvalue)

End Sub

 

[kadam]

Hi - why don't you just copy the entire cell, and then do a Find (" &quot and Replace ("&quot to get rid of all the spaces.

Another way to find and move all cells that begin with a space is to use Text to Columns (or a macro recording text to columns) that has the space as the deliminator. This also gets rid of the leading space, and is a better solution if there are multiple spaces in the data that you want to keep.

Hope this helps.

Kadam.

 

[pcdaveh]

I'm trying to take a file that contains Company Name address, Tel#, ... and Parse it. The file is not delimited by comma or tab, so it is irregular. So, I need to read the file the determine what to put in what columns.
I've used find and replace but it won't work in this situation.

 

[Durkin]

Try using the Left and Right functions something like the following:

Option Explicit
Sub mytest()


Dim li_Location As Integer
Dim ls_CellVall As String
Dim ls_Addvalue As String

li_Location = 1


Range("A" & li_Location).Select
ls_CellVall = ActiveCell.Value
ls_Addvalue = InStr(1, ls_CellVall, " &quot
Range("B" & li_Location).Value = Right(ls_CellVall, Len(ls_CellVall) - ls_Addvalue)
Range("A" & li_Location).Value = Left(ls_CellVall, ls_Addvalue - 1)
MsgBox ("The first blank is in position" & " " & ls_Addvalue)

End Sub Durkin
alandurkin@bigpond.com

 

[MoGryph]

'Maybe not all that elegant, but here's what I'd do... or something similar
' I just threw it togethor right now. I might work on it more in the future
' so that it's more efficient and robust.

'(You MUST first save the sheet- this will refresh Last Used Cell marker.)

Sub MoGryphs_Parser()
Dim i As Long
Dim x As Long
ReDim A(1 To 50) As String ' Increase if there's more than 50 _
space delim'd columns
Application.ScreenUpdating = False

' Go from row 1 to last USED row on the sheet.
For x = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
MoGryphs_Tokenizer Cells(x, 1).Value, A 'Call my custom function
i = 1 ' Re-Initialize
Do
If Len(A(i)) = 0 Then
Exit Do
End If
Cells(x, i + 1).Value = A(i)
i = i + 1
Loop
ReDim A(1 To 50) ' SEE NOTE ABOVE REGARDING ReDim A(1 To 50)
Next x
Application.ScreenUpdating = True
End Sub

Sub MoGryphs_Tokenizer(StrIn As String, StrOut() As String)
Dim i As Long
Dim idx As Integer
Dim LastSpace As Long
LastSpace = 0
idx = 0
If Len(StrIn) = 0 Then Exit Sub
For i = 1 To Len(StrIn)
If Mid(StrIn, i, 1) = " " Then
idx = idx + 1
If LastSpace > 0 Then
StrOut(idx) = Mid(StrIn, LastSpace + 1, i - LastSpace - 1)
Else
StrOut(idx) = Left(StrIn, i - 1 + 1)
End If
LastSpace = i
End If
Next i
idx = idx + 1
StrOut(idx) = Mid(StrIn, LastSpace + 1)
End Sub


I haven't taken into account Multiple spaces next to each other- in that case my Tokenizer routine would fail. You can modify how you want- I"m just too tired to fool-proof it all the way.

Good luck, and Keep Coding!

 

[MoGryph]

BTW- The previous code will parse out EVERY space, and move that section of data over another column... ie..
"This is a whole sentence".
COLUMN
(B) : This
(C) : is
(D) : a
(E) : whole
(F) : sentence

 

[pcdaveh]

Now that's what I call a house party! Thanks! That works great!