Results 1 to 10 of 604

Thread: Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    10,457
    Rep Power
    10
    It is character building.
    Actually, you are good at geussing what he wants...
    I will post just once more now in the main Thread , and then go and break some more rocks for relaxation for the rest of the day..
    C ya tomorrrow
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    KILL A MODERATOR!!

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    10,457
    Rep Power
    10
    Try number 12976436. Education in Text files

    In support of this Thread: http://www.eileenslounge.com/viewtopic.php?f=30&t=34629
    DF.txt
    Text file, DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
    Original uploaded DF.txt looked like this as seen for example using a Text Editor. ( Notepad is just one of many available text editors )
    10,18052020,9.23,0015378
    20,1018GS2026,GS,IN0020010081,0.00,0.00,10.00,0.00 ,0.00,10.00
    20,1025GS2021,GS,IN0020010040,0.00,0.00
    ……..etc.
    You are using a comma in DF.txt to separate the values. Because you are using a comma to separate your values , we sometimes call such a file a comma separated values file., and we often give a comma separated values text file the extension .csv. But you don’t have to. It’s is your choice. Both DF.txt or DF.csv is OK. You can use either for your text file.
    You have used DF.txt for your comma separated values text file. That is a bit unusual, but it is OK. Its your choice.


    This macro will allow us to examine that text file, ( for simplicity I am using a test file example of just 3 rows )
    Code:
    Sub WotsInDF_Text()  '  '  http://www.eileenslounge.com/viewtopic.php?p=268809#p268809   What is in   DF.txt :  https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic           https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13273&viewfull=1#post13273
    Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
    Dim PathAndFileName As String, TotalFile As String
     Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "DF_first 3 rows.txt" ' From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629     DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
    Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
    TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
    Get #FileNum, , TotalFile
    Close #FileNum
    ' What the fuck is in this string?
    Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) '    https://excelfox.com/forum/showthread.php/2302-quot-What%e2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=11016&viewfull=1#post11016     https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11818&viewfull=1#post11818
    
    End Sub
    
    here is the full single string of the text file, shown in two forms:
    _ as seen in a text editor
    _ in a VBA code line form
    Code:
    10,18052020,9.23,001537820,1018GS2026,GS,IN0020010081,0.00,0.00,10.00,0.00,0.00,10.0020,1025GS2021,GS,IN0020010040,0.00,0.00,10.00,0.00,0.00,10.00
    "10" & Chr(44) & "18052020" & Chr(44) & "9" & "." & "23" & Chr(44) & "0015378" & vbCr & vbLf & "20" & Chr(44) & "1018GS2026" & Chr(44) & "GS" & Chr(44) & "IN0020010081" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & vbCr & vbLf & "20" & Chr(44) & "1025GS2021" & Chr(44) & "GS" & Chr(44) & "IN0020010040" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00"
    here the same again, just shown slightly differently for easy of explanation
    Code:
    "10" & Chr(44) & "18052020" & Chr(44) & "9" & "." & "23" & Chr(44) & "0015378" & vbCr & vbLf
     & "20" & Chr(44) & "1018GS2026" & Chr(44) & "GS" & Chr(44) & "IN0020010081" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & vbCr & vbLf 
    & "20" & Chr(44) & "1025GS2021" & Chr(44) & "GS" & Chr(44) & "IN0020010040" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00"
    we see the value seperator comma , ( character 44 ) and the line seperator, vbCr & vbLf


    In support of this Thread: https://excelfox.com/forum/showthrea...3427#post13427
    Alert 24 Mai..csv Alert 24 MaiDotDotcsv.jpg : https://imgur.com/0HsAOLj

    We analyse using the same macro as above, with this changed code line
    Code:
     Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "Alert 24 Mai..csv" '  https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files     Share ‘Alert 24 Mai..csv’  :  https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt
    
    Here is the results
    Code:
    NSE,236,6,>,431555,A,,,,,GTTNSE,25,6,>,431555,A,,,,,GTTNSE,15083,6,>,431555,A,,,,,GTTNSE,17388,6,>,431555,A,,,,,GTTNSE,100,6,>,431555,A,,,,,GTTNSE,22,6,>,431555,A,,,,,GTT,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Entire row of row 3 & row 4 both will be deleted after runing the macro,,,,,,
    Code:
    "NSE" & Chr(44) & "236" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "25" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "15083" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "17388" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "100" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "22" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vb
    Lf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "Entire" & " " & "row" & " " & "of" & " " & "row" & " " & "3" & " " & "&" & " " & "row" & " " & "4" & " " & "both" & " " & "will" & " " & "be" & " " & "deleted" & " " & "
    after" & " " & "runing" & " " & "the" & " " & "macro" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf
    Here again adjusted for clarity
    Code:
    "NSE" & Chr(44) & "236" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf &
     "NSE" & Chr(44) & "25" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & 
    "NSE" & Chr(44) & "15083" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & 
    "NSE" & Chr(44) & "17388" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & 
    "NSE" & Chr(44) & "100" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & 
    "NSE" & Chr(44) & "22" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf &
     Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
    Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
    Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
    Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
    Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
    Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
    Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & 
    Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "Entire" & " " & "row" & " " & "of" & " " & "row" & " " & "3" & " " & "&" & " " & "row" & " " & "4" & " " & "both" & " " & "will" & " " & "be" & " " & "deleted" & " " & "after" & " " & "runing" & " " & "the" & " " & "macro" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf
    
    
    
    .csv text file is using commas , for the value separator, and for the line separate it has the typical convention of vbCr & vbLf
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    KILL A MODERATOR!!

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    10,457
    Rep Power
    10
    In Support of this forum question
    https://eileenslounge.com/viewtopic....268481#p268481

    _____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
    Row\Col
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    6
    #VALUE!
    Got missing number in column E CSE equivalent CSE equivalent
    7
    Title 5
    Title 6
    Title 7
    Title 8
    Title 9
    Title 10
    8
    eileenslounge
    1000.00
    1000
    Got one or more missing numbers
    9
    1
    eileenslounge1
    4.00
    4
    Got missing number in column E
    10
    1
    2
    eileenslounge2
    9.00
    9
    11
    2
    3
    Others
    16.00
    16
    12
    3
    4
    eileenslounge
    1000.00
    1000
    13
    4
    5
    eileenslounge1
    36.00
    36
    14
    5
    6
    eileenslounge2
    49.00
    49
    15
    6
    7
    Others
    64.00
    64
    16
    7
    8
    17
    8
    Worksheet: data

    _____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
    Row\Col
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    6
    =IF(G6="eileenslounge",1000,F7*E8)
    =IF(F7="","Got one or more missing numbers",IF(E8="","Got missing number in column E","")) CSE equivalent CSE equivalent
    7
    Title 5
    Title 6
    Title 7
    Title 8
    Title 9
    Title 10
    8
    eileenslounge
    =IF(G8="eileenslounge",1000,F9*E10)
    =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)
    =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
    9
    1
    eileenslounge1
    =IF(G9="eileenslounge",1000,F10*E11)
    =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)
    =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
    10
    1
    2
    eileenslounge2
    =IF(G10="eileenslounge",1000,F11*E12)
    =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)
    =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
    11
    2
    3
    Others
    =IF(G11="eileenslounge",1000,F12*E13)
    =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)
    =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
    12
    3
    4
    eileenslounge
    =IF(G12="eileenslounge",1000,F13*E14)
    =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)
    =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
    13
    4
    5
    eileenslounge1
    =IF(G13="eileenslounge",1000,F14*E15)
    =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)
    =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
    14
    5
    6
    eileenslounge2
    =IF(G14="eileenslounge",1000,F15*E16)
    =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)
    =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
    15
    6
    7
    Others
    =IF(G15="eileenslounge",1000,F16*E17)
    =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)
    =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
    16
    7
    8
    17
    8
    Worksheet: data
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    KILL A MODERATOR!!

Similar Threads

  1. Testing Concatenating with styles
    By DocAElstein in forum Test Area
    Replies: 2
    Last Post: 12-20-2020, 02:49 AM
  2. testing
    By Jewano in forum Test Area
    Replies: 7
    Last Post: 12-05-2020, 03:31 AM
  3. Replies: 18
    Last Post: 03-17-2019, 06:10 PM
  4. Concatenating your Balls
    By DocAElstein in forum Excel Help
    Replies: 26
    Last Post: 10-13-2014, 02:07 PM
  5. Replies: 1
    Last Post: 12-04-2012, 08:56 AM

Posting Permissions

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