Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

[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


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


Hit key F9 https://i.postimg.cc/7P1FwdFP/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




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


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


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

---

---

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

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/15VpN7Hj/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! )

---

---

[DocAElstein]

In support of this forum thread
https://excelfox.com/forum/showthrea...ll=1#post16718

In worksheet String to search is the following:

_____ Workbook: VBA delete everything between two newlines where the string appears.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
1
2	paragraph/line
3
4	searched string
5	#VBA

Worksheet: String to search

Here is the given input examples ( The OP gave 4 cells , but for demo here I will put them all in the same code box and separate the 4 examples with a line. Black is the before/input , and Blue the after/ wanted output

 in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"

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.
"Looking for help in VBA"searched*string

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.
"Looking for help in VBA"




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.
"Looking for help in #VBA"
if the given string is found in any excel cell then delete paragraph/line everything between two newlines where the string appears.
in VBA if the given string is found then delete paragraph/line everything between two newlines where the string appears.
"Looking for help in VBA"
if the given string is found in any excel cell then delete everything between two newlines where the string appears.
searched*string
 in VBA if the given string is found then delete everything between two newlines where the string appears.


"Looking for help in VBA"
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.
"Looking for help in VBA"

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.
"Looking for help in VBA"searched*string

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.
"Looking for help in VBA"




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.
"Looking for help in #VBA"
if the given string is found in any excel cell then delete paragraph/line everything between two newlines where the string appears.
in VBA if the given string is found then delete paragraph/line everything between two newlines where the string appears.
"Looking for help in VBA"
if the given string is found in any excel cell then delete everything between two newlines where the string appears.
searched*string
 in VBA if the given string is found then delete everything between two newlines where the string appears.


"Looking for help in VBA"
if the given string is found in any excel cell then delete everything between two newlines where the string appears.

OK so it looks quite clear what is needed. The OPs explanation does confirm to this, …. if the given string is found in any excel cell then delete everything between two newlines where the string appears.
… How can I delete sentences with a specific keyword in excel cells in all sheets?
…..but without the example it was much more difficult to understand and may have been interpreted to mean something else..

---

Putting the explanation of what is wanted a bit clearer, …
There is text in some cells. The text is split into paragraphs or lines, that is to say we see multi-line text, something like

How are you?

Well I am OK Bro today
Enjoying a wank just now.

What we want to do is: Modify the text in cells in this way: If certain words are found in a paragraph or line, then all the text in that line needs to be deleted. Lets say in that example, Bro was one of the words to search for. In that case, the text should be modified so as to look like this

How are you?


Enjoying a wank just now.

We remove all the text, Well I am OK Bro today, but it looks like the lines stay there.


We are manipulating text and some form of line separation is involved, so its usually a good idea to look carefully at the actual characters: Almost immediately I think of Splitting and Joining text where the thing to split by or use to join together will be a text line separator, which can vary. Hence important to take a look, so I will, here, later
https://excelfox.com/forum/showthrea...ll=1#post16727