Creating Simple Macros

  • Thread starter Thread starter Lory
  • Start date Start date
L

Lory

Hello,

I have stuff in Columns A, B, C and in approx 10,000 rows...
and the stuff stored in Column A looks like this:

AB-CD-EFG abcabd abd adf

1) In Column A, I would like to write a macro that extracts only th
first "word" (in the above row example would be AB::CD:EFG) and replac
the contents of that cell. I would like the macro to do it for all o
Column A.

Column B looks like this:
ABC 5, BCD 6

2) In Column B, I would like to write a macro that extracts only th
first number (in the above example would be 5) and replace the content
by it, then extracts the second number (6) and replace Column C by tha
number. I would like to do this for all of the rows.

How can I do this? I'm a novice in Excel.

Thanks in advance!
- Lor
 
Lory

Try this on a back up copy



Sub dddd()
Dim iPos(1 To 3) As Integer
Dim Rng As Range

For Each Rng In Range("a1:a" & _
Range("A" & Rows.Count).End(xlUp).Row)
iPos(1) = InStr(1, Rng.Value, "-")
If iPos(1) > 0 Then
Rng.Value = Left(Rng.Value, iPos(1) - 1)
End If
Next

For Each Rng In Range("b1:b" & _
Range("b" & Rows.Count).End(xlUp).Row)
iPos(1) = InStr(1, Rng.Value, " ")
iPos(2) = InStr(1, Rng.Value, ",")
If iPos(1) > 0 And iPos(2) > 0 Then
iPos(3) = InStr(iPos(2) + 2, Rng.Value, " ")
If iPos(3) > 0 Then
Rng.Offset(0, 1).Value = _
Mid(Rng.Value, iPos(3) + 1)
End If
Rng.Value = Mid(Rng.Value, _
iPos(1) + 1, (iPos(2) - iPos(1)) - 1)
End If
Next
End Su
 
You may be able to accomplish what you're trying to do without having to use
macros........check out the LEFT, RIGHT, and MID functions in the Help File

Vaya con Dios,
Chuck, CABGx3
 
Back
Top