ProbablyOriginally Posted by sandy666
do ya have : Attention deficit hyperactivity disorder (ADHD) ???
it is fixer's fault - he is driving me mad!
Probablydo ya have : Attention deficit hyperactivity disorder (ADHD) ???
it is fixer's fault - he is driving me mad!
….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!!
Next solution attempt for this:
https://excelfox.com/forum/showthrea...ll=1#post13219
Do not put a code line in the macro to open 2.csv!
Code:Sub Step14_DogShit() ' https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13219&viewfull=1#post13219 Rem 1 Worksheets info Dim w1 As Workbook, w2 As Workbook, w3 As Workbook Set w1 = Workbooks("1.xls") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xlsx") ' Do Not open 2.csv ' Set w2 = Workbooks("2.csv") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\document\2.csv") Set w3 = Workbooks("3.xlsx") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\files\3.xlsx") Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet Set Ws1 = w1.Worksheets.Item(1) ' Set Ws2 = w2.Worksheets.Item(1) Set Ws3 = w3.Worksheets.Item(1) Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row ' http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466 Making Lr dynamic ( using rng.End(XlUp) for a single column. ) ' Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column ' Dim Lc3Ltr As String ' Let Lc3Ltr = CL(Lc3) Rem 2 ' In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that much time of 3.xlsx first row of sheet3 to 2.csv Let Lenf1 = Lr1 - 1 ' 1.xls first row has headers so dont count that ' 2a) get range to be put into dog shit files Dim arrIn() As Variant: Let arrIn() = Ws3.Range("A1:K1").Value ' 2b) make a string fow a row, including a dog shit Tab seperator Dim cnt For cnt = 1 To UBound(arrIn(), 2) ' Column count in worksheet 3 row 1 Dim strLine As String Let strLine = strLine & arrIn(1, cnt) & vbTab Next cnt Let strLine = Left(strLine, (Len(strLine) - 1)) ' Take off last Tab ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(strLine) ' "NSE" & vbTab & vbTab & "6" & vbTab & vbTab & vbTab & "A" & vbTab & vbTab & vbTab & vbTab & vbTab & "GTT" ' 2c) repeat string to include (and include line breaks) to make complete string for do shit text files For cnt = 1 To Lenf1 ' row count of our dog shit text files Dim strTotalFile As String Let strTotalFile = strTotalFile & strLine & vbCr & vbLf Next cnt ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(strTotalFile) ' https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13218&viewfull=1#post13218 Rem 4 make dogshit files ' 4a) Dog Shit text Dim Highway1 As Long: Let Highway1 = FreeFile(0) 'range 1 – 255, inclusive - next free Open ThisWorkbook.Path & "\" & "DogShit.txt" For Append As #Highway1 ' Will be made if not there Print #Highway1, strTotalFile Close #Highway1 ' 4b) 2.csv Dim Highway2 As Long: Let Highway2 = FreeFile(0) 'range 1 – 255, inclusive - next free Open ThisWorkbook.Path & "\" & "2.csv" For Append As #Highway2 ' Will be made if not there Print #Highway2, strTotalFile Close #Highway2 Rem .... ' w1.Close ' w2.Save '' Let Application.DisplayAlerts = False '' w2.Close '' Let Application.DisplayAlerts = True ' w3.Close ' End Sub
….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!!
In support of these Post
https://excelfox.com/forum/showthrea...ll=1#post13246
http://www.eileenslounge.com/viewtop...268627#p268627
These are all text Files. The macro in the next post ( https://excelfox.com/forum/showthrea...ll=1#post13248 ) will make them
XXXXSeperatedValuesTextFiles.JPG : https://imgur.com/A2IebLK
Comma Seperated values
(Sometimes called English Comma Seperated Values )
zyxw123 , jhas , , rider , roger , anjus , sumanjjj Leonardo , umpsbug , kinjals , , tinamishra , kinjal124 , WigWam
Share ‘CommaSeperatedValues.txt’ : https://app.box.com/s/qcjpeu0vt875513gqawmtoufeba3xb28
Share ‘CommaSeperatedValues.csv’ : https://app.box.com/s/w2barpwasveltam4lutjwijks0zft0vq
Tab Seperated Values
zyxw123 jhas rider roger anjus sumanjjj Leonardo umpsbug kinjals tinamishra kinjal124 WigWam
Share ‘TabSeperatedValues.csv’ : https://app.box.com/s/ukgxcmxj8xhmy0gzvw5269zyjdmun28g
Share ‘TabSeperatedValues.txt’ : https://app.box.com/s/d24blwuejfixh9ofhrg387nbadxjvu15
NMOD Seperated Values
zyxw123 NMOD jhas NMOD NMOD rider NMOD roger NMOD anjus NMOD sumanjjj Leonardo NMOD umpsbug NMOD kinjals NMOD NMOD tinamishra NMOD kinjal124 NMOD WigWam
Share ‘NMODSeperatedValues.csv’ : https://app.box.com/s/ohxqrao160vapx5jozhx7ejc4t70v1wl
Share ‘NMODSeperatedValues.txt’ : https://app.box.com/s/46p14u9rfwvve0s4yv01zyy34f6qhmmz
Semi Colon Seperated Values
(Sometimes called German Comma Seperated values)
zyxw123 ; jhas ; ; rider ; roger ; anjus ; sumanjjj Leonardo ; umpsbug ; kinjals ; ; tinamishra ; kinjal124 ; WigWam
Share ‘SemiColonSeperatedValues.csv’ : https://app.box.com/s/kvqqfsjaebzj684rw8n0u1v4hqfi3hea
Share ‘SemiColonSeperatedValues.txt’ : https://app.box.com/s/qojzd9ogwgg2d2unh2k8dkvwzdpgh84e
GollyWobbles Seperated Values
zyxw123 GollyWobbles jhas GollyWobbles GollyWobbles rider GollyWobbles roger GollyWobbles anjus GollyWobbles sumanjjj Leonardo GollyWobbles umpsbug GollyWobbles kinjals GollyWobbles GollyWobbles tinamishra GollyWobbles kinjal124 GollyWobbles WigWam
Share ‘GollyWobblesSeperatedValues.txt’ : https://app.box.com/s/d0pktg8fadbkl8nfwnodfyle5766lghx
Share ‘GollyWobblesSeperatedValues.csv’ : https://app.box.com/s/5xbiy0wrc05txaofr7qknpot7cb3qdo3
Excel File With Wrong Extension
_____ Workbook: ExcelFileWithWrongExtension.csv ( Using Excel 2007 32 bit )
Worksheet: Tabelle1
Row\Col zyxw123 jhas rider roger anjus sumanjjj Leonardo umpsbug kinjals tinamishra kinjal124 fxe632
Share ‘ExcelFileWithWrongExtension.csv’ : https://app.box.com/s/esxlg0ovoux4gk29zxgklwog6zz6b7s1
….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!!
In support of these Post
http://www.eileenslounge.com/viewtopic.php?f=30&t=34629
These are all text Files. The macro in this post ( https://excelfox.com/forum/showthrea...ll=1#post13248 ) will make them
XXXXSeperatedValuesTextFiles.JPG : https://imgur.com/A2IebLK
PipeSeperatedValuesTextFiles.JPG : https://imgur.com/Y9676cg
Comma Seperated values
(Sometimes called English Comma Seperated Values )
zyxw123 , jhas , , rider , roger , anjus , sumanjjj Leonardo , umpsbug , kinjals , , tinamishra , kinjal124 , WigWam
Share ‘CommaSeperatedValues.txt’ : https://app.box.com/s/qcjpeu0vt875513gqawmtoufeba3xb28
Share ‘CommaSeperatedValues.csv’ : https://app.box.com/s/w2barpwasveltam4lutjwijks0zft0vq
Pipe Seperated Text Files
zyxw123 | jhas | | rider | roger | anjus | sumanjjj Leonardo | umpsbug | kinjals | | tinamishra | kinjal124 | WigWam
Share ‘PipeSeperatedValues.txt’ : https://app.box.com/s/47eo2pmeqlmnjj5h9hlxog8ts47nlgj7
Share ‘PipeSeperatedValues.csv’ : https://app.box.com/s/o7zculmorhyys3r9b6hwwuc3wry1mr6p
….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!!
easy, easy, this is a patience test
sandy
I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt
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!!
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 )here is the full single string of the text file, shown in two forms: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
_ as seen in a text editor
_ in a VBA code line form
here the same again, just shown slightly differently for easy of explanationCode: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"
we see the value seperator comma , ( character 44 ) and the line seperator, vbCr & vbLfCode:"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"
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
Here is the resultsCode: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
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,,,,,,Here again adjusted for clarityCode:"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
.csv text file is using commas , for the value separator, and for the line separate it has the typical convention of vbCr & vbLfCode:"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
….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!!
In Support of this forum question
https://eileenslounge.com/viewtopic....268481#p268481
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col Got missing number in column E CSE equivalent CSE equivalent eileenslounge Got one or more missing numbers eileenslounge1 Got missing number in column E eileenslounge2 Others eileenslounge eileenslounge1 eileenslounge2 Others
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col =IF(F7="","Got one or more missing numbers",IF(E8="","Got missing number in column E","")) CSE equivalent CSE equivalent eileenslounge =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge1 =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge2 =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) Others =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge1 =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge2 =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) Others =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
….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!!
Another attempt to geuss what fixer is askig for from here:
https://excelfox.com/forum/showthrea...ll=1#post13256
Code:Sub OpenTxtFiles_ValuesToBeSeperatedIntoExcelCells() ' Comma seperated values text files Call OpenA____SeperatedValuesTextFile("CommaSeperatedValues.csv", ",") Call OpenA____SeperatedValuesTextFile("CommaSeperatedValues.txt", ",") End Sub Sub OpenA____SeperatedValuesTextFile(ByVal Filname As String, ByVal Seprator As String) Rem 1 Get text file as long string. 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 & "\" & Filname ' 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 hs to be a string of exactly the right length Get #FileNum, , TotalFile Close #FileNum Rem 2 Put values in Excel Dim Ws1 As Worksheet Set Ws1 = ThisWorkbook.Worksheets.Item(1) Ws1.Cells.ClearContents '2b) Split Total File text into a 1 Dimensional array into rows Dim RwTxt() As String: Let RwTxt() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) Dim Clms() As String Let Clms() = Split(RwTxt(0), Seprator, -1, vbBinaryCompare) ' This will be the first row of data. Here we are doing it just to gat the column count. In the loop below, we will use it for every row, including initially this first row. We need it below to allow us to access each value seperately seperated via the seprator, seprator Dim HedClmsCnt As Long: Let HedClmsCnt = UBound(Clms) + 1 ' +1 is required , as , by default , a 1Dimensional array from split has first element indicie of 0 , so Ubound will be 1 less than the number of elements Dim arrOut() As String ' I must make this dynamic, since i must use the TReDim method to size it. This is because the Dim statement will not accept variables or non static values: It omly accepts actual integer hard coded numbers ReDim arrOut(1 To UBound(RwTxt) + 1, 1 To HedClmsCnt) ' +1 is required , as , by default , a 1Dimensional array from split has first element indicie of 0 , so Ubound will be 1 less than the number of elements Dim RwCnt As Long For RwCnt = 0 To UBound(RwTxt) '2c) Split each row into seperated values Let Clms() = Split(RwTxt(RwCnt), Seprator, -1, vbBinaryCompare) Dim ClmCnt As Long If Not UBound(Clms()) = -1 Then ' This might be the case fo extra rows in the text file with no seperators in s For ClmCnt = 1 To HedClmsCnt Let arrOut(RwCnt + 1, ClmCnt) = Clms(ClmCnt - 1) Next ClmCnt Else End If Next RwCnt Rem 2d) Put values from text file into first worksheet Ws1.Range("A1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)) = arrOut() End Sub
….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!!
In Support of this forum question
https://eileenslounge.com/viewtopic....268481#p268481
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col CSE equivalent CSE equivalent =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) eileenslounge =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge1 =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge2 =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) Others =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge1 =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge2 =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) Others =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col CSE equivalent CSE equivalent Got missing number in column F eileenslounge Got one or more missing numbers eileenslounge1 Got one or more missing numbers eileenslounge2 Others eileenslounge eileenslounge1 eileenslounge2 Others
….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!!
Posting Permissions
Reply With Quote
Bookmarks