Anybody Real Good with Excel VBA? 1

[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 cut it out. 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.


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

 

[dsi]

Let me see if I understand what you want. All of your data is in Column A. You want to scan each cell in Column A to find the text that contains a space. If it contains a space, you want to leave everything to the right of the space.

 Before    |   After
    A      |     A
1  abc     |1  abc
2  def     |2  def
3  gh i    |3  i
4  jkl     |4  jkl

Sub KillSpaces()
  Dim iRow As Long
  Dim sVal As String
  Dim iPos As Integer
  iRow = 1    'first row of data
  Do While Len(Range("A" & iRow).Text) > 0
    sVal = Range("A" & iRow).Text
    iPos = InStr(1, sVal, " ")
    If iPos > 0 Then
      Range("A" & iRow).FormulaR1C1 = Right(sVal, Len(sVal) - iPos)
    End If
    iRow = iRow + 1
  Loop
End Sub

Hope this helps

 

[pcdaveh]

That works very well thank you. However, I need to get the content of what is left of the cell into let's say column B.
Sorry I wasn't very clear in my Requirements.

 

[dsi]

Just alter the If statment like this.

If iPos > 0 Then
  Range("A" & iRow).FormulaR1C1 = Right(sVal, Len(sVal) - iPos)
  Range("B" & iRow).FormulaR1C1 = Left(sVal, iPos - 1) 
End If

 

[pcdaveh]

I haven't tried it but from viewing your logic now I see.

Thanks!

That's what I needed!