Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-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]

Test

I see what you are saying its like “the 14th thing along” , like in if it was in a 1 dimensional or 1 “row” array,
This sort of thing
_number along{1 _ , 2 , 3 , 4 , 5 , 6 , 7 , 8 _ , 9 _ , 10_, 11_, 12_,13 , 14 __ , 15 , _16}
____INDEX({"Part",13,25,37,48,73,101,145,201,289,600,750,1009, "DMG1",4.1,2.85},1,14) = "DMG1"
Or similarly
__number along_____{1 _ , 2 , 3 , 4 , 5 , 6 , 7 , 8 _ , 9 _ , 10_, 11_, 12_,13 , 14 __ , 15 , _16}
__=MATCH("DMG1",{"Part",13,25,37,48,73,101,145,201,289,600,750,1009, "DMG1",4.1,2.85},0) = 14
(Note I must change the row separating ; before the to a column separating , **

I also get indexes mixed up and also I mix up item numbers and indexes. Sometimes they are the same, sometimes they have a similar meaning



( ** just passing interest, if in a range we talk about the “number along” on any range, then we are talking about the range item number, and this “number along” goes like all columns in a row, then next row and so on.
So example in both these ranges, the range item number is 14

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Part	13	25	37	48	73	101	145	201	289	600	750	1009	DMG1

Range("A1:N1").Item(14) = "DMG1"

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Part	13	25	37	48	73	101	145	201	289	600	750	1009
2	DMG1	$ 4.10	$ 2.85	$ 2.85	$ 1.90	$ 1.55	$ 1.35	$ 1.25	$ 0.95	$ 0.85	$ 0.75	$ 0.75	$ 0.65

Range("A1:M2").Item(14) = "DMG1"

Row\Col	O	P	Q	R	S	T	U	V	W
4	Part	13	25	37	48	73	101	145	201
5	289	600	750	1009	DMG1	$ 4.10	$ 2.85	$ 2.85	$ 1.90

Range("O4:W5").Item(14) = "DMG1"

Here is a very interesting article by a very clever person on all that https://excelfox.com/forum/showthrea...column-looping )

[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

[DocAElstein]

In support of this main Forum Thread
https://excelfox.com/forum/showthrea...earched-string

Input

					
"#VBA: keep only one searched string searched string searched string searched string.
#VBA: keep only searched string one searched string.
The list of search stings searchedCSV string searched string is given in sheet 2.
In sheet1, with #VBA check if a given string is CSV searched string found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with CSV searched string multiple column data in multiple rows as well as in a CSV or text file."				
				
"searched string searched string #VBA: keep only one searched string.
#VBA: keep only one searched string.
searched string searched string searched stringThe list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA in a paragraph/line. If found more than once then keep only one. #VBA #VBA #VBA #VBA 
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."				
				
"#VBA: keep only one searched string.
#VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file. #VBA: keep only one searched string.
#VBA: keep only one searched string. searched string searched string searched string searched string
The list of search stings is given in sheet 2. searched string searched string searched string searched string
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."				
				
				
				
				
	"#VBA: #VBA  keep only one searched string.
#VBA: #VBA #VBAkeep only one searched string.
              searched string searched string  searched string  searched string  searched string
In sheet1, with #VBA check if a given string is found more than #VBA once in a paragraph/line. If found more than #VBA once then keep only one.

#VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than #VBA once in a paragraph/line. If found more than #VBA once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."			
				
				
				"#VBA: keep only one searched string.


In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file. #VBA: keep only one searched string.


In sheet1, with #VBA check if a  paragraph/line given string is found more than once in a paragraph/line paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."

Output

				
"#VBA: keep only one searched string .
#VBA: keep only searched string one .
The list of search stings searchedCSV string searched string is given in sheet 2.
In sheet1, with #VBA check if a given string is CSV searched string found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with CSV searched string multiple column data in multiple rows as well as in a CSV or text file."				
				
"searched string  #VBA: keep only one searched string.
#VBA: keep only one searched string.
searched string searched stringThe list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one. 
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."				
				
"#VBA: keep only one searched string.
#VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file. #VBA: keep only one searched string.
#VBA: keep only one searched string.  
The list of search stings is given in sheet 2. searched string
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."				
				
				
				
				
	"#VBA:   keep only one searched string.
#VBA: keep only one searched string.
              searched string
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.

#VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."			
				
				
				"#VBA: keep only one searched string.


In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file. #VBA: keep only one searched string.


In sheet1, with #VBA check if a  paragraph/line given string is found more than once in a paragraph/line paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."

[DocAElstein]

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

Sheet1 Input in column A
_____ Workbook: VBA row to cell1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
2	234. digital marketing information: 2021-2022***….Keywrod1: 2021-2022***
3
4	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
5	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
6
7
8	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
9
10	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
11
12	Keywrod1:
13	Keyword2: 30000000 *&^%$QWERTY QWERTY
14
15
16	2344. digital marketing information: 2020-2021***….Keywrod1: *** 2020-2021 digital marketing information digital marketing information
17
18	Digital marketing: = also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
19	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
20
21	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
22	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
23
24	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
25	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
26	Keywrod1: *** 2020-2021
27	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
28	Keyword2
29
30	1. digital marketing information: 2021-2022***….Keywrod1: 2021-2022***
31
32	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
33
34
35	QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Worksheet: InputSheet1

"234. digital marketing information: 2021-2022***….Keywrod1: 
2021-2022***"

"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."
"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
"


"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."

"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."

Keywrod1: 
Keyword2: 30000000 *&^%$QWERTY QWERTY


"2344. digital marketing information: 2020-2021***….Keywrod1: *** 2020-2021
digital marketing information
digital marketing information"

"Digital marketing: =
also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."
"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
"

"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
"
"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."

"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
"
"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."
Keywrod1: *** 2020-2021
"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."
Keyword2

"1. digital marketing information: 2021-2022***….Keywrod1: 
2021-2022***"

"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."


QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Sheet2 Wanted output, next post

[DocAElstein]

Output required ( https://excelfox.com/forum/showthrea...ll=1#post16685 )

_____ Workbook: VBA row to cell1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
2	234. digital marketing information: 2021-2022***….Keywrod1: 2021-2022*** Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Keywrod1: Keyword2: 30000000 *&^%$QWERTY QWERTY
3	2344. digital marketing information: 2020-2021***….Keywrod1: *** 2020-2021 digital marketing information digital marketing information Digital marketing: = also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel." "Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel." Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel." Keywrod1: *** 2020-2021 Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Keyword2
4	1. digital marketing information: 2021-2022***….Keywrod1: 2021-2022*** Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Worksheet: OutputSheet2

"234. digital marketing information: 2021-2022***….Keywrod1: 
2021-2022***

Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.


Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.

Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.

Keywrod1: 
Keyword2: 30000000 *&^%$QWERTY QWERTY"
"2344. digital marketing information: 2020-2021***….Keywrod1: *** 2020-2021
digital marketing information
digital marketing information

Digital marketing: =
also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.""
""Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.


Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.

Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.""

Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.

Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.""
Keywrod1: *** 2020-2021
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Keyword2"
"1. digital marketing information: 2021-2022***….Keywrod1: 
2021-2022***

Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.


QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY"

[DocAElstein]

Further info for the last two posts

Input ( Sheet1)

_____ Workbook: VBA row to cell1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
30	1. digital marketing information: 2021-2022***….Keywrod1: 2021-2022***
31
32	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
33
34
35	QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Worksheet: InputSheet1

"1. digital marketing information: 2021-2022***….Keywrod1: 
2021-2022***"

"Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel."


QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Output Sheet 2

_____ Workbook: VBA row to cell1.xlsx ( Using Excel 2007 32 bit )

Row\Col

A

4

1. digital marketing information: 2021-2022***….Keywrod1: 2021-2022*** Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Worksheet: OutputSheet2

"1. digital marketing information: 2021-2022***….Keywrod1: 
2021-2022***

Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to 
connect with potential customers using the internet and other forms of digital communication. 
This includes not only email, social media, and web-based advertising, but also 
text and multimedia messages as a marketing channel.


QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY"

---

---

---

Test

Sept 14 https://www.youtube.com/watch?v=U76ZRIzBhOA https://bit.ly/3EuOh7B
May 14 https://www.youtube.com/watch?v=tPRv-ATUBe4 https://bit.ly/3rMAaTw
May 6 https://www.youtube.com/watch?v=7KPH...baD5Vd&index=5 https://bit.ly/3TtTlNV
May 4 https://www.youtube.com/watch?v=nDlGkDENCyM https://bit.ly/3MkNUhC
Apr 29 https://www.youtube.com/watch?v=xqBh...baD5Vd&index=6 https://bit.ly/3ejT2WX

[DocAElstein]

Reduced data

_____ Workbook: VBA row to cell1 reduced data.xls ( Using Excel 2007 32 bit )

Row\Col	A
1
2	234. *….Keywrod1: 2021-2022***
3
4	This also text channel.
5	Digital to connect communication.
6
7
8	Digital to connect communication. This also text channel.
9
10	Keywrod1:
11	Keyword2: QWERTY
12
13
14	2344. ….Keywrod1: *** 2020-2021 digital information digital information
15
16	Digital marketing: = also to connect communication. This also text channel.
17	Digital to connect communication.
18
19	Digital to connect communication. This also text channel.
20	Digital to connect communication. This also text channel.
21	Keywrod1: *** 2020-2021
22	Digital to connect communication. This also text channel.
23	Keyword2
24

Worksheet: InputSheet1

"234. *….Keywrod1: 
2021-2022***"

"This also 
text channel."
"Digital to 
connect communication. "


"Digital to 
connect communication. 
This also 
text channel."

Keywrod1: 
Keyword2:  QWERTY


"2344. ….Keywrod1: *** 2020-2021
digital information
digital information"

"Digital marketing: =
also to 
connect communication. 
This also 
text channel."
"Digital to 
connect communication. "

"Digital to 
connect communication. 
This also 
text channel."
"Digital to 
connect communication. 
This also 
text channel."
Keywrod1: *** 2020-2021
"Digital to 
connect communication. 
This also 
text channel."
Keyword2

---

Output
_____ Workbook: VBA row to cell1 reduced data.xls ( Using Excel 2007 32 bit )

Row\Col	A
2	234. *….Keywrod1: 2021-2022*** This also text channel. Digital to connect communication. Digital to connect communication. This also text channel. Keywrod1: Keyword2: QWERTY
3	2344. ….Keywrod1: *** 2020-2021 digital information digital information Digital marketing: = also to connect communication. This also text channel. Digital to connect communication. Digital to connect communication. This also text channel. Digital to connect communication. This also text channel. Keywrod1: *** 2020-2021 Digital to connect communication. This also text channel. Keyword2

Worksheet: Sheet2
Copy/ Paste

"234. *….Keywrod1: 
2021-2022***

This also 
text channel.
Digital to 
connect communication. 


Digital to 
connect communication. 
This also 
text channel.

Keywrod1: 
Keyword2:  QWERTY"
"2344. ….Keywrod1: *** 2020-2021
digital information
digital information

Digital marketing: =
also to 
connect communication. 
This also 
text channel.
Digital to 
connect communication. 

Digital to 
connect communication. 
This also 
text channel.
Digital to 
connect communication. 
This also 
text channel.
Keywrod1: *** 2020-2021
Digital to 
connect communication. 
This also 
text channel.
Keyword2"

Copy text from cel or cel .Value

234. *….Keywrod1: 
2021-2022***

This also 
text channel.
Digital to 
connect communication. 


Digital to 
connect communication. 
This also 
text channel.

Keywrod1: 
Keyword2:  QWERTY

2344. ….Keywrod1: *** 2020-2021
digital information
digital information

Digital marketing: =
also to 
connect communication. 
This also 
text channel.
Digital to 
connect communication. 

Digital to 
connect communication. 
This also 
text channel.
Digital to 
connect communication. 
This also 
text channel.
Keywrod1: *** 2020-2021
Digital to 
connect communication. 
This also 
text channel.
Keyword2

---

VBA row to cell1 reduced data.xls : https://app.box.com/s/qne60lkrfp30d50w444gedzjg6b7nyat

[DocAElstein]

s,mfbsabfsbfasf

[DocAElstein]

In support of this Forum Post
https://eileenslounge.com/viewtopic....a25802#p300075

I am not to sure if I have a possible solution here, but have some ideas.
This post has some background testing that might help me decide and / or help me get to a possible solution.

This is the problem

why Function isn't working and i also want to know how i can trouble shoot it in UDF

Option Explicit
Function sum_color(a As Range, b As Range) As Long
Dim v As Long,  c As Range
  For Each c In a
    If c.DisplayFormat.Interior.ColorIndex = b.DisplayFormat.Interior.ColorIndex Then
    v = v + c.Value
    End If
  Next c

sum_color = v
End Function

Part of Hans immediate answer was https://eileenslounge.com/viewtopic.php?p=300076#p300076] DisplayFormat cannot be used in a UDF [/url]

That spiked my interest and thoughts into things to do with UDFs not liking doing things with Cells other than the cell they are in.
DisplayFormat does not even work in my mostly older Excel versions, such as my older Excel 2007, so first on a Excel 2013 I checked that out. That is mainly what this post is about: trying it out in a slightly newer Excel 2013

Her is the initial results showing , as the OP said, that the UDF in the spreadsheet isn’t working:


I changed the OPs function a bit, as well as trying it as a simple sub routine, and tried calling both the function and the sub routine from coding
Here my coding versions:

Option Explicit
Sub CallEm()
 '   From  Adeel  spreadsheet    '  =sum_color(D6:G15;C17)    '    http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
Dim ForSubAnswer As Long
 Call Subsum_color(Range("D6:G15"), Range("C17"), ForSubAnswer)
Debug.Print ForSubAnswer       ' 54
 
Dim ForFunctionAnswer As Long
 Let ForFunctionAnswer = sum_color(Range("D6:G15"), Range("C17"))
Debug.Print ForFunctionAnswer  '  54
End Sub
Public Sub Subsum_color(ByVal a As Range, ByVal b As Range, ByRef RefBack As Long)
Dim v As Long, c As Range
    For Each c In a
        If c.DisplayFormat.Interior.ColorIndex = b.DisplayFormat.Interior.ColorIndex Then
         Let v = v + c.Value
        Else
        End If
    Next c
'Let sum_color = v
 Let RefBack = v
End Sub
Public Function sum_color(a As Range, b As Range) As Long
Dim v As Long, c As Range
    For Each c In a
        If c.DisplayFormat.Interior.ColorIndex = b.DisplayFormat.Interior.ColorIndex Then
         Let v = v + c.Value
        Else
        End If
    Next c
 Let sum_color = v
End Function

Both the sub routine and the function seem to work in normal coding.


Conclusion
As I was thinking, the problem seems to be something to do with a UDF not liking interacting with other cells in a worksheet