Notes tests, text files, manipulation of text files in Excel and with Excel VBA CSV stuff

Printable View

Show 40 post(s) from this thread on one page

06-01-2022, 01:25 AM
DocAElstein

Some extra stuff for this post
https://eileenslounge.com/viewtopic.php?f=27&t=38243

later
06-13-2022, 02:09 PM
DocAElstein

In support of this main forum Thread
https://excelfox.com/forum/showthrea...nto-excel-cell
_1) create new number and place in cell B1 according to last serial number in csv file,

Before
ExcelFile:
_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

1
Serial# : TTR0000001

2
TTR0000002

3
TTR0000003

4

Worksheet: Sheet1

Text File:

Code:

TTR0000001 TTR0000002 TTR0000003

Run macro:

Code:

Sub NewSN() ' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "serial_number.csv" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Rem 2 determine a new number '2a Current number If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2) ' Take off last line feed Dim PosLstLineFeed As Long: Let PosLstLineFeed = InStrRev(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) Dim CrntNmbr As String: Let CrntNmbr = Mid(TotalFile, PosLstLineFeed + 2) Let CrntNmbr = Replace(CrntNmbr, "TTR", "", 1, -1, vbBinaryCompare) '2b creat new number Let CrntNmbr = CrntNmbr + 1 Let CrntNmbr = Format(CrntNmbr, "0000000") Let CrntNmbr = "TTR" & CrntNmbr Rem 3 Put new number in Excel file Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1) Dim LrB As Long: Let LrB = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row Let Ws1.Range("B" & LrB + 1 & "").Value = CrntNmbr End Sub

After:

_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

1
Serial# : TTR0000001

2
TTR0000002

3
TTR0000003

4
TTR0000004

5

Worksheet: Sheet1
06-13-2022, 02:43 PM
DocAElstein

In support of this main forum Thread
https://excelfox.com/forum/showthrea...nto-excel-cell
_2) save new serial number to csv file below last numbers.

Before :
text File

Code:

TTR0000001 TTR0000002 TTR0000003

Excel File
_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

1
Serial# : TTR0000001

2
TTR0000002

3
TTR0000003

4
TTR0000004

5

Worksheet: Sheet1

Run this macro

Code:

' https://excelfox.com/forum/showthread.php/2797-find-last-alphanumeric-row-of-txt-file-and-fill-into-excel-cell Sub SaveLatestSNinTextFile() Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "serial_number.csv" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Rem 2 get latest serial nimber from Excel file Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1) Dim LrB As Long: Let LrB = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row Dim CrntNmbr As String: Let CrntNmbr = Ws1.Range("B" & LrB & "").Value Rem 3 add latest serial number to text file ' 3a add a new line and the latest serial number to the string of the entire file If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2) Let TotalFile = TotalFile & vbCr & vbLf & CrntNmbr ' ' 3b replace the text file with the new string Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Open PathAndFileName For Output As #FileNum2 Print #FileNum2, TotalFile ' write out entire text file Close #FileNum2 End Sub

After:

Code:

TTR0000001 TTR0000002 TTR0000003 TTR0000004
09-14-2022, 12:07 PM
DocAElstein

In support of this main forum Thread post:
https://excelfox.com/forum/showthrea...ll=1#post16695

content in one line input reduced sample.txt https://app.box.com/s/grrxh1rl372pzp2exn6em00ovhj4qhih
content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos

https://i.postimg.cc/yYdStNqH/Before...ced-sample.jpg

What’s those two text files got in them?:
Using the Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv

, and using this Calling macro

Code:

' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16696&viewfull=1#post16696 Sub SantaComing() ' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "content in one line input reduced sample.txt" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) End Sub

Here is the results, for example from the immediate window :

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems " & vbCr & vbLf & "in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short " & vbCr & vbLf & "stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & vbCr & vbLf & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story " & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that " & vbCr & vbLf & "the story has somehow wormed"

Here are the corresponding results if I do the same to look at the after file ( content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos )

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed"

So, what do we conclude?:
_ 1) there are no tab characters
_ 2) It seems that the line separator is the commonly used 2 character pair of a carriage return and a line feed, ( In VBA coding, vbCr & vbLf )
_ 3) It seems like the character string we wish to remove, ( the extra new line forming characters ) are three character:
A space and a carriage return and a line feed. ( In VBA coding _ " " & vbCr & vbLf _ )
( _ 4) I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I will do that!)

This initial macro seems to do the required:

Code:

Sub ReplaceInTextFileThreeCharacters__Space_vbCr_vbLf__WithA__Space__() ' https://excelfox.com/forum/showthread.php/2817-Make-all-text-file-content-in-one-line-if-a-space-found ' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "content in one line input reduced sample.txt" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Rem 2 remove " " & vbCr & vbLf ' Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, "", 1, -1, vbBinaryCompare) Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, " ", 1, -1, vbBinaryCompare) ' I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I do that! Rem 3 Make new file Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "new text file.txt" Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Open PathAndFileName For Output As #FileNum2 Print #FileNum2, TotalFile ' write out entire text file Close #FileNum2 End Sub

Here is the corresponding output from my function for the new text file made

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed" & vbCr & vbLf

That looks very similar to the output requested. Here is the new text file made:
new text file.txt https://app.box.com/s/w2zydwa20lr3jyxuk8z7ddjgpl0xrcvn

SantaClawsIsComing.xls https://app.box.com/s/7ken6nf050xd4xqwhxu3av1yebdb74dr
09-14-2022, 12:07 PM
DocAElstein

In support of this main forum Thread post:
https://excelfox.com/forum/showthrea...ll=1#post16695

content in one line input reduced sample.txt https://app.box.com/s/grrxh1rl372pzp2exn6em00ovhj4qhih
content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos

https://i.postimg.cc/yYdStNqH/Before...ced-sample.jpg

What’s those two text files got in them?:
Using the Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv

, and using this Calling macro

Code:

' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16696&viewfull=1#post16696 Sub SantaComing() ' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "content in one line input reduced sample.txt" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) End Sub

Here is the results, for example from the immediate window :

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems " & vbCr & vbLf & "in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short " & vbCr & vbLf & "stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & vbCr & vbLf & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story " & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that " & vbCr & vbLf & "the story has somehow wormed"

Here are the corresponding results if I do the same to look at the after file ( content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos )

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed"

So, what do we conclude?:
_ 1) there are no tab characters
_ 2) It seems that the line separator is the commonly used 2 character pair of a carriage return and a line feed, ( In VBA coding, vbCr & vbLf )
_ 3) It seems like the character string we wish to remove, ( the extra new line forming characters ) are three character:
A space and a carriage return and a line feed. ( In VBA coding _ " " & vbCr & vbLf _ )
( _ 4) I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I will do that!)

This initial macro seems to do the required:

Code:

Sub ReplaceInTextFileThreeCharacters__Space_vbCr_vbLf__WithA__Space__() ' https://excelfox.com/forum/showthread.php/2817-Make-all-text-file-content-in-one-line-if-a-space-found ' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "content in one line input reduced sample.txt" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Rem 2 remove " " & vbCr & vbLf ' Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, "", 1, -1, vbBinaryCompare) Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, " ", 1, -1, vbBinaryCompare) ' I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I do that! Rem 3 Make new file Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "new text file.txt" Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Open PathAndFileName For Output As #FileNum2 Print #FileNum2, TotalFile ' write out entire text file Close #FileNum2 End Sub

Here is the corresponding output from my function for the new text file made

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed" & vbCr & vbLf

That looks very similar to the output requested. Here is the new text file made:
new text file.txt https://app.box.com/s/w2zydwa20lr3jyxuk8z7ddjgpl0xrcvn

SantaClawsIsComing.xls https://app.box.com/s/7ken6nf050xd4xqwhxu3av1yebdb74dr
09-29-2022, 01:33 PM
DocAElstein

Some notes in support of this forum main post
https://excelfox.com/forum/showthrea...ll=1#post16712

An erroring formula: https://i.postimg.cc/nzbWtGTQ/An-erroring-formula.jpg
https://i.postimg.cc/mcjjYMvb/An-erroring-formula.jpg

Highlight a section, such as the Match section https://i.postimg.cc/5NxZ0pJB/Highlight-match.jpg
https://i.postimg.cc/K4H9pPRj/Highlight-match.jpg

Hit key F9 https://i.postimg.cc/7P1FwdFP/Hit-ke...esult-is-5.jpg
https://i.postimg.cc/Thw7c72B/Hit-ke...esult-is-5.jpg

This reveals that we have 5 , or in total , 5 + 1 = 6

So in the VLookUp we are looking at column 6 in the range given by bk
The names manager tells us which range we want:
Second formula section is the range bk https://i.postimg.cc/JhBFg23L/range-bk.jpg
https://i.postimg.cc/zL8pHpPt/range-bk.jpg

Similarly we can investigate the first section in the erroring formula
Highlight first formula section https://i.postimg.cc/902syY7j/Highli...la-section.jpg

Hit key F9 https://i.postimg.cc/wvLStG3V/Hiut-F...st-section.jpg

This reveals that the Look up value, the value that you are looking for is "DMG1"

Further investigations by trial and error , reveals that some character combinations in cell A2 cause the error. But I do not know why yet ?

Examples:
Not work:
https://i.postimg.cc/YqbTmg2J/d-not-work.jpg
https://i.postimg.cc/d0zbVYQ3/d-MG1-not-work.jpg
Works
https://i.postimg.cc/3xq6tTv1/MG1-works.jpg

In fact, it seems that some character combinations are not allowed as the Look Up value generally , for example try another cell, and I can find a character combination that does not work
https://i.postimg.cc/SK204shH/Not-wo...ters-in-A6.jpg
https://i.postimg.cc/Ff4Cgh1x/Not-wo...ters-in-A6.jpg

I am puzzled.

In fact if you look in detail at the results you are getting when it does appear to work, then they are not alwaysthe correct values.
https://postimg.cc/kBnt3Zzg
https://i.postimg.cc/kBnt3Zzg/Wrong-...m-VLook-Up.jpg https://i.postimg.cc/MKB0RJYV/Wrong-...m-VLook-Up.jpg

Very strange. I am even more puzzled
?????

Update Answer from Sandy https://excelfox.com/forum/showthrea...ll=1#post16717

Quote:

Originally Posted by sandy666

with formula =VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,0) value $1.55 is returned and so on
..just forgot comma on the end or define last argument 0, VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,) so he need to learn how to use VLOOKUP function
VLOOKUP require all arguments, even if last argument is omitted there should be defined place for it after last comma

https://i.postimg.cc/ncHVbyD8/ThatsIt.gif
https://i.postimg.cc/15VpN7Hj/ThatsIt.jpg https://i.postimg.cc/N5Yy34Z6/ThatsIt.jpg
( I thought I had checked that, but missed something somewhere, I don’t know why I missed that, maybe I think also I need to learn how to use VLoopUp properly! )
09-29-2022, 01:33 PM
DocAElstein

Some notes in support of this forum main post
https://excelfox.com/forum/showthrea...ll=1#post16712

An erroring formula: https://i.postimg.cc/nzbWtGTQ/An-erroring-formula.jpg
https://i.postimg.cc/mcjjYMvb/An-erroring-formula.jpg

Highlight a section, such as the Match section https://i.postimg.cc/5NxZ0pJB/Highlight-match.jpg
https://i.postimg.cc/K4H9pPRj/Highlight-match.jpg

Hit key F9 https://i.postimg.cc/7P1FwdFP/Hit-ke...esult-is-5.jpg
https://i.postimg.cc/Thw7c72B/Hit-ke...esult-is-5.jpg

This reveals that we have 5 , or in total , 5 + 1 = 6

So in the VLookUp we are looking at column 6 in the range given by bk
The names manager tells us which range we want:
Second formula section is the range bk https://i.postimg.cc/JhBFg23L/range-bk.jpg
https://i.postimg.cc/zL8pHpPt/range-bk.jpg

Similarly we can investigate the first section in the erroring formula
Highlight first formula section https://i.postimg.cc/902syY7j/Highli...la-section.jpg

Hit key F9 https://i.postimg.cc/wvLStG3V/Hiut-F...st-section.jpg

This reveals that the Look up value, the value that you are looking for is "DMG1"

Further investigations by trial and error , reveals that some character combinations in cell A2 cause the error. But I do not know why yet ?

Examples:
Not work:
https://i.postimg.cc/YqbTmg2J/d-not-work.jpg
https://i.postimg.cc/d0zbVYQ3/d-MG1-not-work.jpg
Works
https://i.postimg.cc/3xq6tTv1/MG1-works.jpg

In fact, it seems that some character combinations are not allowed as the Look Up value generally , for example try another cell, and I can find a character combination that does not work
https://i.postimg.cc/SK204shH/Not-wo...ters-in-A6.jpg
https://i.postimg.cc/Ff4Cgh1x/Not-wo...ters-in-A6.jpg

I am puzzled.

In fact if you look in detail at the results you are getting when it does appear to work, then they are not alwaysthe correct values.
https://postimg.cc/kBnt3Zzg
https://i.postimg.cc/kBnt3Zzg/Wrong-...m-VLook-Up.jpg https://i.postimg.cc/MKB0RJYV/Wrong-...m-VLook-Up.jpg

Very strange. I am even more puzzled
?????

Update Answer from Sandy https://excelfox.com/forum/showthrea...ll=1#post16717

Quote:

Originally Posted by sandy666

with formula =VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,0) value $1.55 is returned and so on
..just forgot comma on the end or define last argument 0, VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,) so he need to learn how to use VLOOKUP function
VLOOKUP require all arguments, even if last argument is omitted there should be defined place for it after last comma

https://i.postimg.cc/ncHVbyD8/ThatsIt.gif
https://i.postimg.cc/15VpN7Hj/ThatsIt.jpg https://i.postimg.cc/N5Yy34Z6/ThatsIt.jpg
( I thought I had checked that, but missed something somewhere, I don’t know why I missed that, maybe I think also I need to learn how to use VLoopUp properly! )
10-02-2022, 06:21 PM
DocAElstein

This is post https://excelfox.com/forum/showthrea...6727#post16727
#post16727

In support of this forum Thread: https://excelfox.com/forum/showthrea...6726#post16726
https://excelfox.com/forum/showthrea...6718#post16718

I want to analyse the text in cells A2, A5, B7, and B10.
I can use my Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
In conjunction with this short macro

Code:

Option Explicit ' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16727&viewfull=1#post16727 ' https://excelfox.com/forum/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears Sub WotsEeGot() Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A2").Value2) ' https://pastebin.com/raw/eutzzxHv Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A5").Value2) Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B7").Value2) Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B10").Value2) End Sub

Here are some of the results

Code:

"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string" "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string"

So it looks like most important info to glean is that the line separator is as conventional in an Excel cell, vbLf
10-02-2022, 06:21 PM
DocAElstein

This is post https://excelfox.com/forum/showthrea...ll=1#post19610
#post19610
It was copied initially before I edited it from the post above, #post16727 , and that #post16727 stayes yellow highlighted after the copy

Some note in support of this main forum post
https://eileenslounge.com/viewtopic....303644#p303644

„WieGehtsYouTubeServerChrome.txt“ https://app.box.com/s/a7k2izgyzqhd7f98hlaq9csw0l4tyyl6
10-02-2022, 06:21 PM
DocAElstein

This is post https://excelfox.com/forum/showthrea...6727#post16727
#post16727

In support of this forum Thread: https://excelfox.com/forum/showthrea...6726#post16726
https://excelfox.com/forum/showthrea...6718#post16718

I want to analyse the text in cells A2, A5, B7, and B10.
I can use my Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
In conjunction with this short macro

Code:

Option Explicit ' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16727&viewfull=1#post16727 ' https://excelfox.com/forum/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears Sub WotsEeGot() Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A2").Value2) ' https://pastebin.com/raw/eutzzxHv Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A5").Value2) Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B7").Value2) Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B10").Value2) End Sub

Here are some of the results

Code:

"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string" "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string"

So it looks like most important info to glean is that the line separator is as conventional in an Excel cell, vbLf

_Row\^Col	A	B
1	Serial# :	TTR0000001
2		TTR0000002
3		TTR0000003
4

_Row\^Col	A	B
1	Serial# :	TTR0000001
2		TTR0000002
3		TTR0000003
4		TTR0000004
5

_Row\^Col	A	B
1	Serial# :	TTR0000001
2		TTR0000002
3		TTR0000003
4		TTR0000004
5

Show 40 post(s) from this thread on one page