|
P: 9
|
Hello There,
I am trying to write a macro that will copy part of Text in a Cell & then paste in another cell.
For example,
assume cell A1="ABE_BC1 AbTS 11 bisTSport 2", I would like to copy/Paste "11" in cell A2 & 2 IN cell A3 .How do i achieve this with a macro instead?
Can anyone help?
CELL A1 = ADDRESS CELL B1 = BOARD CELL C1 = SLOT
CELL A2 = ABE_BC1 AbTS 11 bisTSport 2 CELL B2 = 11 CELL C2 = 12
CELL A3 = ABE_BC1 |AbTS 11 AbTSport 3 CELL B3 = 11 CELL C3 = 3
CELL A4 = ABE_BC1 |AbTS 11 AbTSport 3 CELL B4 = 11 CELL C4 = 3
CELL A5 = ABE_BC1 |AbTS 14 AbTSport 4 CELL B5 = 14 CELL C5 = 4
CELL A6 = UMH_BC1 |AbTS 2 AbTSport 5 CELL B6 = 2 CELL C6= 5
CELL A7 = UMH_BC1 |AbTS 2 AbTSport 5 CELL B7 = 2 CELL C7 = 5
| |
Share this Question
| Expert 5K+
P: 8,434
|
Hello There,
I am trying to write a macro that will copy part of Text in a Cell & then paste in another cell.
For example,
assume cell A1="ABE_BC1 AbTS 11 bisTSport 2", I would like to copy/Paste "11" in cell A2 & 2 IN cell A3 .How do i achieve this with a macro instead?
Can anyone help?
CELL A1 = ADDRESS CELL B1 = BOARD CELL C1 = SLOT
CELL A2 = ABE_BC1 AbTS 11 bisTSport 2 CELL B2 = 11 CELL C2 = 12
CELL A3 = ABE_BC1 |AbTS 11 AbTSport 3 CELL B3 = 11 CELL C3 = 3
CELL A4 = ABE_BC1 |AbTS 11 AbTSport 3 CELL B4 = 11 CELL C4 = 3
CELL A5 = ABE_BC1 |AbTS 14 AbTSport 4 CELL B5 = 14 CELL C5 = 4
CELL A6 = UMH_BC1 |AbTS 2 AbTSport 5 CELL B6 = 2 CELL C6= 5
CELL A7 = UMH_BC1 |AbTS 2 AbTSport 5 CELL B7 = 2 CELL C7 = 5
Try this. - Sub DoIt()
-
Dim I As Long
-
For I = 2 To 7
-
Cells(I, 2).Value = Word(3, Cells(I, 1).Value)
-
Cells(I, 3).Value = Word(5, Cells(I, 1).Value)
-
Next
-
End Sub
-
-
-
Private Function Word(DesiredWord As Long, FromString As String) As String
-
Dim I As Long, J As Long, Char As String * 1
-
Dim WordNum As Long, StartPos As Long, NextSpace As Long
-
Dim WhatWasFound As String
-
StartPos = 1
-
If FromString = "" Then Exit Function
-
Do
-
WordNum = WordNum + 1
-
NextSpace = InStr(StartPos, FromString, " ")
-
If NextSpace = 0 Then
-
If WordNum = DesiredWord Then
-
WhatWasFound = Mid$(FromString, StartPos)
-
End If
-
Exit Do
-
End If
-
If WordNum = DesiredWord Then
-
WhatWasFound = Mid$(FromString, StartPos, NextSpace - StartPos)
-
Exit Do
-
End If
-
StartPos = NextSpace + 1
-
Loop
-
Word = Trim$(WhatWasFound)
-
End Function
-
I have made a lot of assumptions, of course. But I tried to make it flexible where possible. For one thing, the Word() function lets you specify that you want the 1st word, 2nd word or whatever, from the input string. This may not match what you want to do.
My results... Before - ADDRESS BOARD SLOT
-
ABE_BC1 AbTS 11 bisTSport 2
-
ABE_BC1 |AbTS 11 AbTSport 3
-
ABE_BC1 |AbTS 11 AbTSport 3
-
ABE_BC1 |AbTS 14 AbTSport 4
-
UMH_BC1 |AbTS 2 AbTSport 5
-
UMH_BC1 |AbTS 2 AbTSport 5
After - ADDRESS BOARD SLOT
-
ABE_BC1 AbTS 11 bisTSport 2 11 2
-
ABE_BC1 |AbTS 11 AbTSport 3 11 3
-
ABE_BC1 |AbTS 11 AbTSport 3 11 3
-
ABE_BC1 |AbTS 14 AbTSport 4 14 4
-
UMH_BC1 |AbTS 2 AbTSport 5 2 5
-
UMH_BC1 |AbTS 2 AbTSport 5 2 5
| |
| Expert 5K+
P: 8,434
|
P.S. One of the assumption I made is that the 12 in C2 was a typo.
| |
|
P: 9
|
P.S. One of the assumption I made is that the 12 in C2 was a typo.
Thank you, The code works.
But i would like to know what the arguement (DesiredWord) in the Function (Word)
is for. Private Function Word(DesiredWord As Long, FromString As String) As String | |
| Expert 5K+
P: 8,434
|
Thank you, The code works.
But i would like to know what the arguement (DesiredWord) in the Function (Word)
is for.
Private Function Word(DesiredWord As Long, FromString As String) As String
You use that to say which word you want to pull out of the string. If I remember correctly, in the example I passed it "3" and "5" to extract the 3rd and 5th words. Try passing different numbers, to see what you get.
| |
|
P: 9
|
You use that to say which word you want to pull out of the string. If I remember correctly, in the example I passed it "3" and "5" to extract the 3rd and 5th words. Try passing different numbers, to see what you get.
Thanks for your help
| | | |  Question stats - viewed: 1592
- replies: 5
- date asked: Nov 1 '06
| |
