0

So the scenario I have is there are letter with a number:

enter image description here

Desired Output after Program (Note an underscore is visually used but I need a space:

____________F
__G
_____E
__G__E______F

Currently I have written code in the Number and First Letter Column to extract the number and first letter:

First Letter:

LEFT(A2,1)

Number:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Now my VBA Script can take the number and character to get the information and output for (For ONE Error Code):

Private Sub Code_Printer_Click()

Dim myFile As String, rng As Range, cellValue As Variant, I As Integer, j As Integer

myFile = "C:\Reformatted.txt"
Set rng = Selection

Open myFile For Output As #1

For I = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count

If j = rng.Columns.Count Then
    cellValue = Space(rng.Cells(I, 1)) + CStr(rng.Cells(I, 2).Value)
    Print #1, cellValue
End If

    Next j
    cellValue = ""
Next I

Close #1
Shell "C:\Windows\Notepad.exe C:\Reformatted.txt", 1

End Sub

RESULT:

Result

So please help me process multiple codes in the same event.

If an excel function needs to be done thats fine. If its easier to extract the number in VBA thats ok to. Non technical people will use so the more VBA the better. Please let me know if this is a slow or if there is a faster, easier way to do this! :)

-----------------------------Final Data--------------------------------------- FinalData

Improve this question
4
  • Is that list all in one cell or in 3 cells? Commented Jun 16, 2015 at 19:44
  • Usually an event/entry has one code is in its separate cell, BUT sometimes an event might have multiple cells all stuffed into a cell as shown with S,G,E. Commented Jun 16, 2015 at 19:47
  • @Raystafarian Hey man I was able to clarify the question a lot and included screen shots. Commented Jun 17, 2015 at 21:02
  • I'm confused by the 122 in your example. Commented Jun 18, 2015 at 9:37

2 Answers 2

Reset to default
1

This is how I'd do it -

Sub test()
Dim myFile As String
myFile = "C:\reformatted.txt"
Open myFile For Output As #1
Dim iPar As Integer
Dim sChar As String
Dim sBlank As Long
Dim cont As Boolean

Dim mystring As String

For Each c In Range("A:A")
If c <> "" Then
'Get first
        iPar = InStr(1, c, "(")
        If Mid(c, iPar - 1, 1) = "" Then
            If Mid(c, iPar - 2, 1) = "" Then
            sChar = Mid(c, iPar - 3, 1)
            Else: sChar = Mid(c, iPar - 2, 1)
            End If
        Else: sChar = Mid(c, iPar - 1, 1)
        End If
        If IsNumeric(Mid(c, iPar + 1, 2)) Then
            sBlank = Mid(c, iPar + 1, 2)
        Else: sBlank = Mid(c, iPar + 1, 1)
        End If
        mystring = Space(sBlank) & sChar
        cont = InStr(iPar + 1, c, "(")

    Do While cont = True

        iPar = InStr(iPar + 1, c, "(")
        If Mid(c, iPar - 1, 1) = "" Then
            If Mid(c, iPar - 2, 1) = "" Then
            sChar = Mid(c, iPar - 3, 1)
            Else: sChar = Mid(c, iPar - 2, 1)
            End If
        Else: sChar = Mid(c, iPar - 1, 1)
        End If
        If IsNumeric(Mid(c, iPar + 1, 2)) Then
            sBlank = Mid(c, iPar + 1, 2)
        Else: sBlank = Mid(c, iPar + 1, 1)
        End If

        If sBlank + 1 > Len(mystring) Then
            mystring = mystring & Space(sBlank - Len(mystring)) & sChar
        Else: mystring = Application.WorksheetFunction.Replace(mystring, sBlank + 1, 1, sChar)
        End If
        cont = InStr(iPar + 1, c, "(")

    Loop

       Print #1, mystring
     Else: Exit For
   End If
Next
Close #1
Shell "C:\Windows\Notepad.exe C:\reformatted.txt", 1

End Sub

So it searches for the first parentheses ( and tackles the character and spaces. Then it looks for another ( - if it finds one, it continues, otherwise it's done (and prints).

If it continues, it finds the next ( and does the same test finding the character and spaces, then checks the string to see if the length of the string is greater than the number of spaces. If it is, it replaces a blank character with your character. If not, it appends spaces and then inserts the character at the end.

Then it searches again for a ( to repeat the process.

Right now it is searching column A and printing in column B - adjust as needed. You can print mystring to file.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

How is the range set in this selection? How can I add this line of code: Set rng = Selection
this macro utility is for other people actually. The main idea is that they open up template, fill it, and select and click a button. So I have to do that legwork. lol.
I can't seem to make this work. Do you mind putting in with the rng selection and shell command? Specifically I am confused by while there is a "Exit Sub" command in an else clause
I see. Also I dont quite know how and when to print to the file. If you have time could u put in the print mystring and open and shell command for the file? I tried to add all those and the program wont run.
This for some reason doesn't print the characters. I made this revision and characters with ONE space works: Else: sChar = Mid(Cells(I, j).Value, iPar - 4, 1)
|
0

This is not a complete answer, but for the 2nd point of your pseudo-code, you can use the wildcard # to search a string for any numeric characters. More information is here MSDN.

Improve this answer

2 Comments

I clarified this question a lot. Please let me know if you have any more suggestions.
Raystafarians method of searching is a much better way to do this I believe, especially if you want to keep the spaces

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.