Results 1 to 16 of 16

Thread: (excel) Someone made me a macro, but it only works sometimes, locate the error? How?

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2015
    Posts
    7

    (excel) Someone made me a macro, but it only works sometimes, locate the error? How?

    I am not well versed in vb, so everything might be over my head.

    I receive several files each week in .xls format with specific information (ie, the column headings are the same, the information in them is different, depending on who sends it). Unfortunately, that xls format will not go into the software that it needs to. So, it all gets entered by hand. The only way to process it 'electronically' is by converting it into a .txt with specific spacing. Someone created a macro that converts the xls into a .txt. However, it is very buggy. It works for some lines of the workbook but not others. I seem to get different errors each time so I don't know how to fix the problem. If there is anyone who would be willing to look at it (I could also post it here as it isn't very long) and maybe tell me where the errors are coming from? Also, I realize that if the people sending the excel file change things around, this might be causing the problem?

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    Sure, post the code, and ideally some sample data that works and some that doesn't.

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2015
    Posts
    7

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    Quote Originally Posted by vbfbryce View Post
    Sure, post the code, and ideally some sample data that works and some that doesn't.

    This is the code of the macro


    Sub sadek()

    Dim myFile As String, textline As String
    myFile = Sheets("Macro").Range("C6").Value
    Open myFile For Output As #1
    For i = Sheets("Macro").Range("C4").Value To Sheets("Macro").Range("C5").Value
    Print #1, PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("A" & i).Value, 20, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("B" & i).Value, 15, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("C" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("D" & i).Value, 40, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("E" & i).Value, 20, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("F" & i).Value, 21, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("G" & i).Value, 2, " ") _
    & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("H" & i).Value, 9, " ") & PadRight(Format(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Valu e).Range("I" & i).Value, "yyyymmdd"), 8, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("J" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("K" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("L" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("M" & i).Value, 9, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("N" & i).Value, 1, " ") _
    & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("O" & i).Value, 15, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("P" & i).Value, 10, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("Q" & i).Value, 10, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("R" & i).Value, 11, " ") & PadRight(Format(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Valu e).Range("S" & i).Value, "yyyymmdd"), 8, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("T" & i).Value, 1, " ") & PadRight(Format(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Valu e).Range("U" & i).Value, "yyyymmdd"), 8, " ") _
    & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("V" & i).Value, 1, " ") & PadRight(Format(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Valu e).Range("W" & i).Value, "0.0"), 3, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("X" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("Y" & i).Value, 30, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("Z" & i).Value, 30, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("AA" & i).Value, 20, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("AB" & i).Value, 2, " ") _
    & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("AC" & i).Value, 9, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("AD" & i).Value, 10, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Rang e("AE" & i).Value, 12, " ")
    Sheets("Macro").Range("C8").Value = (i - Sheets("Macro").Range("C4").Value) / (Sheets("Macro").Range("C5").Value - Sheets("Macro").Range("C4").Value)
    Next i
    Close #1
    MsgBox ("Done ")
    End Sub

    Function PadLeft(text As Variant, totalLength As Integer, padCharacter As String) As String
    PadLeft = String(totalLength - Len(CStr(text)), padCharacter) & CStr(text)
    End Function

    Function PadRight(text As Variant, totalLength As Integer, padCharacter As String) As String
    If Len(CStr(text)) > totalLength Then PadRight = CStr(text) Else PadRight = CStr(text) & String(totalLength - Len(CStr(text)), padCharacter)
    End Function



    Not sure how to attach the sample stuff. It seems to work up to a point. Either, it can only do the first few lines (example, say the excel file has 27 lines, so you would in the range of line 2 to line 27, but it will show only a certain percentage complete, you can keep backing out and trying line 2 to 20, for example, and see where it gets stuck.

    Otherwise, it might do all the lines you ask of it, but after the first few columns, the rest do not line up as they should on the output txt file.

  4. #4
    New Member
    Join Date
    Jul 2014
    Location
    Russia
    Posts
    2

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    Quote Originally Posted by Susy View Post
    Not sure how to attach the sample stuff.
    Name:  GA.jpg
Views: 253
Size:  20.6 KBName:  att.jpg
Views: 245
Size:  20.6 KB
    Quote Originally Posted by Susy View Post
    Also, I realize that if the people sending the excel file change things around, this might be causing the problem?
    please check you excel workbook for commas in cells. i get same problem with same code and workbook contained cells with commas
    Last edited by Surrogate; Apr 26th, 2015 at 02:15 PM. Reason: with addictions

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    I'm really having trouble following that. First off, put the code in code tags (click the # symbol, which gives you the begin and the end tags, code goes in between). After I got it somewhat cleaned up, I still can't tell what the intent is exactly. Give us some more detail, like "I need to create the text file from the Excel file which has X columns (number of rows varies), and each field needs to be Y characters wide." Again, sample data would help as well.

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2015
    Posts
    7

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    Quote Originally Posted by vbfbryce View Post
    I'm really having trouble following that. First off, put the code in code tags (click the # symbol, which gives you the begin and the end tags, code goes in between). After I got it somewhat cleaned up, I still can't tell what the intent is exactly. Give us some more detail, like "I need to create the text file from the Excel file which has X columns (number of rows varies), and each field needs to be Y characters wide." Again, sample data would help as well.
    Code:
    Sub sadek()
    
    Dim myFile As String, textline As String
    myFile = Sheets("Macro").Range("C6").Value
    Open myFile For Output As #1
    For i = Sheets("Macro").Range("C4").Value To Sheets("Macro").Range("C5").Value
        Print #1, PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("A" & i).Value, 20, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("B" & i).Value, 15, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("C" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("D" & i).Value, 40, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("E" & i).Value, 20, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("F" & i).Value, 21, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("G" & i).Value, 2, " ") _
         & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("H" & i).Value, 9, " ") & PadRight(Format(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("I" & i).Value, "yyyymmdd"), 8, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("J" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("K" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("L" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("M" & i).Value, 9, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("N" & i).Value, 1, " ") _
          & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("O" & i).Value, 15, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("P" & i).Value, 10, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("Q" & i).Value, 10, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("R" & i).Value, 11, " ") & PadRight(Format(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("S" & i).Value, "yyyymmdd"), 8, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("T" & i).Value, 1, " ") & PadRight(Format(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("U" & i).Value, "yyyymmdd"), 8, " ") _
           & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("V" & i).Value, 1, " ") & PadRight(Format(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("W" & i).Value, "0.0"), 3, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("X" & i).Value, 1, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("Y" & i).Value, 30, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("Z" & i).Value, 30, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("AA" & i).Value, 20, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("AB" & i).Value, 2, " ") _
            & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("AC" & i).Value, 9, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("AD" & i).Value, 10, " ") & PadRight(Workbooks(Sheets("Macro").Range("C2").Value).Sheets(Sheets("Macro").Range("C3").Value).Range("AE" & i).Value, 12, " ")
        Sheets("Macro").Range("C8").Value = (i - Sheets("Macro").Range("C4").Value) / (Sheets("Macro").Range("C5").Value - Sheets("Macro").Range("C4").Value)
    Next i
    Close #1
    MsgBox ("Done :D")
    End Sub
    
    Function PadLeft(text As Variant, totalLength As Integer, padCharacter As String) As String
        PadLeft = String(totalLength - Len(CStr(text)), padCharacter) & CStr(text)
    End Function
    
    Function PadRight(text As Variant, totalLength As Integer, padCharacter As String) As String
        If Len(CStr(text)) >= totalLength Then PadRight = Left(CStr(text), totalLength) Else PadRight = CStr(text) & String(totalLength - Len(CStr(text)), padCharacter)
    End Function

    It is meant to take an excel file with 31 columns (A through AE)
    Each column should be of a particular length (some are 1 space, some are 20 spaces, etc.) and it should take this excel file and change it to a txt file that keeps the spacing required.

    And again, sorry for my ignorance. I can't seem to attach an xls file?

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    We have limits on file types unfortunately (due to potential viruses etc).

    If you put it into a .zip file, you can upload it.

  8. #8
    New Member
    Join Date
    Jun 2014
    Posts
    2

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    I use the below code to scrub unwanted characters and retain only the numbers and text. I use this mainly for fieldnames but you could drop (comment) any of the tests for text you want to keep.

    Code:
    Function ScrubData(strFldName As String, Optional iMaxCharacters As Integer = 64) As String
    ' Remove illegal characters from string.  Fieldname Max Char = 64
    '''    Non-printable characters
    '''    Grave Accent (`), Exclamation mark (!)
    '''    Period (.), bracket ([])
    '''    Leading Space (Include any space to ease VBA coding)
    '''    question mark (?), at sign (@), quotation mark(")
    '''    apostrophe('), number sign (#), percent sign (%)
    '''    a tilde (~), semicolon (;), bracket ([])
    '''    brace ({}), parenthesis (()), vertical bar(|).
    '''    Greater than sign (>), Less than sign (<), Minus sign(-)
    '''    Plus sign (+), Period (.), Asterisk (*),Caret (^)
    '''    Colon (:), Backslash (\), Slash mark (/)
    '''    Ampersand (&), Equal sign (=)
    Dim newString As String
    Dim lpcnt As Integer
    newString = strFldName
    For lpcnt = 0 To 47 ' Non-Printables Space ! " # $ % & ' ( ) * - + ,
        newString = Replace(newString, Chr(lpcnt), "")
    Next
    For lpcnt = 58 To 64 ' ; : < = > ? @
        newString = Replace(newString, Chr(lpcnt), "")
    Next
    For lpcnt = 91 To 96 ' [ \ ] ^ `
        newString = Replace(newString, Chr(lpcnt), "")
    Next
    For lpcnt = 123 To 126 ' { | } ~
        newString = Replace(newString, Chr(lpcnt), "")
    Next
    For lpcnt = 128 To 255 ' Extended Ascii Characters
        newString = Replace(newString, Chr(lpcnt), "")
    Next
      ScrubData = Left(newString, iMaxCharacters)
    End Function

  9. #9

    Thread Starter
    New Member
    Join Date
    Apr 2015
    Posts
    7

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    file that did not work.zip

    This is one that did not work, but then works sometimes? This is an example of the files that come in.


    This one is what it should look like (the format) once it runs through the macro, or the macro runs through it, I guess.


    DUMMY_FILE3.zip

    Hopefully, I did the attachments correctly.

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    I'm not seeing how that code will work with that "file that did not work" since there is no sheet called "Macro" in it, for starters. Where is that?

  11. #11

    Thread Starter
    New Member
    Join Date
    Apr 2015
    Posts
    7

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    Name:  screenshot.jpg
Views: 233
Size:  18.4 KB



    There is a sheet that you use to put in the information. That sheet is called "macro" I have included a screenshot. I am not sure how to get the code of that sheet?

  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    If you hit Alt+F11 to open the code editor, you should get something similar to what I've attached. If you double click the "Macro" sheet object in the upper left (my version in blue), you should see the code tied to that button.
    Attached Images Attached Images  

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    you can test this to see if it works correctly for you, it seemed correct with your file that did not work
    test with multiple data files

    i added a macro sheet to the same workbook, though i assume that it would normally be in some other workbook, which should still work fine
    Code:
    Sub sadek()
    
    Dim myFile As String, textline As String, shtmac As Worksheet, l()
    Dim shtdata As Worksheet, rw As Long, col As Integer, s As String, stxt As String, strdata As String
    l = Array(20, 15, 1, 40, 20, 21, 2, 9, 8, 1, 1, 1, 9, 1, 15, 10, 10, 11, 8, 1, 8, 1, 3, 1, 30, 30, 20, 2, 9, 10, 12)
    Set shtmac = ThisWorkbook.Sheets("Macro")
    Set shtdata = Workbooks(shtmac.Range("c2").Value).Sheets(shtmac.Range("c3").Value)
    myFile = shtmac.Range("c6")
    shtmac.Range("C8").NumberFormat = "0%"
    For rw = shtmac.Range("C4").Value To shtmac.Range("C5").Value
        For col = 1 To 31
            stxt = shtdata.Cells(rw, col)
            Select Case col
                Case 9, 19, 21: stxt = Format(stxt, "yyyymmdd")
                Case 23: stxt = Format(stxt, "0.0")
            End Select
            s = Space(l(col - 1))
            Mid(s, 1) = stxt
            strdata = strdata & s
        Next
        strdata = strdata & vbNewLine
        shtmac.Range("c8").Value = (((rw - shtmac.Range("c4").Value) / (shtmac.Range("c5").Value - shtmac.Range("c4").Value)))
        
        DoEvents
    Next
    Open myFile For Output As #1
    Print #1, strdata
    Close #1
    MsgBox ("Done ")
    End Sub
    i found some conflict between the cell addresses from the macro sheet, i used from c2 to c8 based on your image
    i found an anomaly in column W
    the fixed length shows as 3, but the format of "0.0" would return longer strings for 2 digit values in sample file eg. 14 would return "14.0"
    the above code truncates the string to 3 characters "14."
    if this is not the desired result you would need to specify how you would like it handled

    the progress percent was not possible to track as it updated and finished too fast to see
    in a big file it should only update every 200 to 500 rows
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    New Member
    Join Date
    Apr 2015
    Posts
    7

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    Quote Originally Posted by westconn1 View Post
    you can test this to see if it works correctly for you, it seemed correct with your file that did not work
    test with multiple data files

    i added a macro sheet to the same workbook, though i assume that it would normally be in some other workbook, which should still work fine
    Code:
    Sub sadek()
    
    Dim myFile As String, textline As String, shtmac As Worksheet, l()
    Dim shtdata As Worksheet, rw As Long, col As Integer, s As String, stxt As String, strdata As String
    l = Array(20, 15, 1, 40, 20, 21, 2, 9, 8, 1, 1, 1, 9, 1, 15, 10, 10, 11, 8, 1, 8, 1, 3, 1, 30, 30, 20, 2, 9, 10, 12)
    Set shtmac = ThisWorkbook.Sheets("Macro")
    Set shtdata = Workbooks(shtmac.Range("c2").Value).Sheets(shtmac.Range("c3").Value)
    myFile = shtmac.Range("c6")
    shtmac.Range("C8").NumberFormat = "0%"
    For rw = shtmac.Range("C4").Value To shtmac.Range("C5").Value
        For col = 1 To 31
            stxt = shtdata.Cells(rw, col)
            Select Case col
                Case 9, 19, 21: stxt = Format(stxt, "yyyymmdd")
                Case 23: stxt = Format(stxt, "0.0")
            End Select
            s = Space(l(col - 1))
            Mid(s, 1) = stxt
            strdata = strdata & s
        Next
        strdata = strdata & vbNewLine
        shtmac.Range("c8").Value = (((rw - shtmac.Range("c4").Value) / (shtmac.Range("c5").Value - shtmac.Range("c4").Value)))
        
        DoEvents
    Next
    Open myFile For Output As #1
    Print #1, strdata
    Close #1
    MsgBox ("Done ")
    End Sub
    i found some conflict between the cell addresses from the macro sheet, i used from c2 to c8 based on your image
    i found an anomaly in column W
    the fixed length shows as 3, but the format of "0.0" would return longer strings for 2 digit values in sample file eg. 14 would return "14.0"
    the above code truncates the string to 3 characters "14."
    if this is not the desired result you would need to specify how you would like it handled

    the progress percent was not possible to track as it updated and finished too fast to see
    in a big file it should only update every 200 to 500 rows

    Thanks so much, I will try this!

    As for the code on that 'front' page. Curious thing, but even double clicking or right clicking and asking to view the code turns up a blank page. Is there a way that it could be hidden?

  15. #15

    Thread Starter
    New Member
    Join Date
    Apr 2015
    Posts
    7

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    Name:  capture of form.jpg
Views: 212
Size:  17.5 KB

    I double clicked on the area shown by the arrow, and also tried to right click and selected 'view code', but it just opens up that blank screen with nothing in it?

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H

    where is the Sadek procedure? that is the code that should run when the go button is clicked
    right click the Go button >veiw code (or assign macro)

    on testing my code with 20,000 rows, i made some changes, like

    Code:
    Dim myFile As String, textline As String, shtmac As Worksheet, l()
    Dim shtdata As Worksheet, rw As Long, col As Integer, s As String, stxt As String, strdata As String
    l = Array(20, 15, 1, 40, 20, 21, 2, 9, 8, 1, 1, 1, 9, 1, 15, 10, 10, 11, 8, 1, 8, 1, 3, 1, 30, 30, 20, 2, 9, 10, 12)
    Set shtmac = ThisWorkbook.Sheets("Macro")
    Set shtdata = Workbooks(shtmac.Range("c2").Value).Sheets(shtmac.Range("c3").Value)
    myFile = shtmac.Range("c6")
    t = Timer
    Open myFile For Output As #1
    shtmac.Range("C8").NumberFormat = "0%"
    For rw = shtmac.Range("C4").Value To shtmac.Range("C5").Value
        For col = 1 To 31
            stxt = shtdata.Cells(rw, col)
            Select Case col
                Case 9, 19, 21: stxt = Format(stxt, "yyyymmdd")
                Case 23: stxt = Format(stxt, "0.0")
            End Select
            s = Space(l(col - 1))
            Mid(s, 1) = stxt
            strdata = strdata & s
        Next
    '    strdata = strdata & vbNewLine
        Print #1, strdata
        strdata = ""
        If rw Mod 200 = 0 Or rw = shtmac.Range("c5") Then
            shtmac.Range("c8").Value = (((rw - shtmac.Range("c4").Value) / (shtmac.Range("c5").Value - shtmac.Range("c4").Value)))
            DoEvents
        End If
    Next
    Close #1
    MsgBox ("Done in " & Timer - t & " secs")
    End Sub
    you may need a small modification like, if last row is 0 work till end of file (last row of data, but as i have no idea how many rows your data is likely to contain, i am not sure if it would be required

    in case it is not clear l is an array containing the fixed string length for each column

    processing time of about 4.2 seconds

    with some further optimizing i was able to reduce processing time by about .3 of a second
    Last edited by westconn1; May 2nd, 2015 at 06:59 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width