Macro Script to find a character and prefix
Solved/Closed
Pradeep
-
May 31, 2010 at 01:28 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 1, 2010 at 07:17 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 1, 2010 at 07:17 AM
Related:
- Macro Script to find a character and prefix
- Pipe character - Guide
- How to change chrome dino character - Guide
- Reverse text character - Guide
- +37 prefix - Guide
- Special character letter - Guide
5 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 31, 2010 at 01:18 PM
May 31, 2010 at 01:18 PM
try this
Sub FixMe()
Dim lMaxRows As Long
Dim lRow As Long
Dim sBlock As String
lMaxRows = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For lRow = 1 To lMaxRows
' if the string on row contains " Block: " then we have our new block
If (InStr(1, Cells(lRow, "A"), " Block: ") > 0) Then
sBlock = Mid(Cells(lRow, "A"), InStr(1, Cells(lRow, "A"), " Block: ") + Len(" Block: "))
sBlock = sBlock & "."
'if the first character of the cell on the row is % then, append the last block name
ElseIf (Left(Cells(lRow, "A"), 1) = "%") Then
Cells(lRow, "A") = sBlock & Cells(lRow, "A")
End If
Next lRow
End Sub
Hi riz,
Thanks for your response
I ran the macro script and it doesnt seem to do anything..
The excel sheet looks like as shown below all in the same row which is A
Program: STK4R01 C:\LM90\STK4R01 Block: _MAIN
05-31-10 14:45 GE FANUC SERIES 90-70 (v7.05) Page 2
%I00549 LTD LOS's # 1 - 6 OK
%I00550 LTD FWD CONTCTR CLOSED
%I00551 LTD REV CONTCTR CLOSED
%I00552 LTD FINAL EAST LS OK
%I00553 LTD FINAL WEST LS OK
Program: STK4R01 C:\LM90\STK4R01 Block: DPILE
05-31-10 14:45 GE FANUC SERIES 90-70 (v7.05) Page 54
%M07468 TRL CNV RO 3or4 FAULTED
%M07469 TRL CNV RO 5or6 FAULTED
%M07470 TRL CNV RO 1or2 BYPASS
%M07471 TRL CNV RO 3or4 BYPASS
%M07472 TRL CNV RO 5or6 BYPASS
%M07473 TRL CNV RS 1or2 FAULTED
%M07474 TRIPPER CHUTE PLUG FAULTED
So if the "Block:" name is _MAIN then it will add a prefix of _MAIN.%I00549 in the same row
if the "Block: " name is DPILE then it will add a prefix of DPILE.%M07468 in the same rows and it moves on .
Currently when i ran the script it doesnt seem to make a change.Please reply me on the same
Thanks for your response
I ran the macro script and it doesnt seem to do anything..
The excel sheet looks like as shown below all in the same row which is A
Program: STK4R01 C:\LM90\STK4R01 Block: _MAIN
05-31-10 14:45 GE FANUC SERIES 90-70 (v7.05) Page 2
%I00549 LTD LOS's # 1 - 6 OK
%I00550 LTD FWD CONTCTR CLOSED
%I00551 LTD REV CONTCTR CLOSED
%I00552 LTD FINAL EAST LS OK
%I00553 LTD FINAL WEST LS OK
Program: STK4R01 C:\LM90\STK4R01 Block: DPILE
05-31-10 14:45 GE FANUC SERIES 90-70 (v7.05) Page 54
%M07468 TRL CNV RO 3or4 FAULTED
%M07469 TRL CNV RO 5or6 FAULTED
%M07470 TRL CNV RO 1or2 BYPASS
%M07471 TRL CNV RO 3or4 BYPASS
%M07472 TRL CNV RO 5or6 BYPASS
%M07473 TRL CNV RS 1or2 FAULTED
%M07474 TRIPPER CHUTE PLUG FAULTED
So if the "Block:" name is _MAIN then it will add a prefix of _MAIN.%I00549 in the same row
if the "Block: " name is DPILE then it will add a prefix of DPILE.%M07468 in the same rows and it moves on .
Currently when i ran the script it doesnt seem to make a change.Please reply me on the same
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 31, 2010 at 09:21 PM
May 31, 2010 at 09:21 PM
I ran it on the data that you gave it works
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.
Hi Riz,
I have uploaded the sample file in below link .
https://authentification.site/files/22732556/Stk4r01.csv
Can you please try it now.
I have uploaded the sample file in below link .
https://authentification.site/files/22732556/Stk4r01.csv
Can you please try it now.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 31, 2010 at 10:23 PM
May 31, 2010 at 10:23 PM
It is because of leading white spaces.
Hi Riz,
Thanks for your response
Can you please what exactly has to be done so that the script will work.
Thanks for your response
Can you please what exactly has to be done so that the script will work.
Didn't find the answer you are looking for?
Ask a question
Hi Riz,
Thanks for your help.I did figure it out now and the scipt works perfectly fine.
Thanks for your help.I did figure it out now and the scipt works perfectly fine.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 1, 2010 at 07:17 AM
Jun 1, 2010 at 07:17 AM
This would work irrespective of leading white space
Sub FixMe()
Dim lMaxRows As Long
Dim lRow As Long
Dim sBlock As String
Dim BlockPos As Variant
Dim PctPos As Variant
lMaxRows = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For lRow = 1 To lMaxRows
BlockPos = InStr(1, Cells(lRow, "A"), " Block: ")
PctPos = InStr(1, Cells(lRow, "A"), "%")
' if the string on row contains " Block: " then we have our new block
If (BlockPos > 0) Then
sBlock = Mid(Cells(lRow, "A"), BlockPos + Len(" Block: "))
sBlock = Trim(sBlock) & "."
'if the first character of the cell on the row is % then, append the last block name
ElseIf (Left(Trim(Cells(lRow, "A")), 1) = "%") Then
If (PctPos > 1) Then
Cells(lRow, "A") = Left(Cells(lRow, "A"), PctPos - 1) & sBlock & Mid(Cells(lRow, "A"), PctPos)
Else
Cells(lRow, "A") = sBlock & Mid(Cells(lRow, "A"), PctPos)
End If
End If
Next lRow
End Sub
