In support of this main forum post
https://excelfox.com/forum/showthrea...-Excel-Formula

Assuming this is data and wanted results from column D

_____ Workbook: DogsNutsFormulas.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
1
 Code Values Code Value1 Value2 Value3 Value4
2
1001
2101
1001
2101
5205
2605
9285
3
1001
5205
2604
4256
7458
3555
4
1001
2605
5
1001
9285
6
2604
4256
7
2604
7458
8
2604
3555
9
Worksheet: SimpleUniqueVLookUp



Enter this In D2 via CSE, then drag it down:
=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)),"")
Row\Col
D
2
=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)),"")
3
=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0)),"")


Alternative formula for earlier versions of Excel:
=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)))
Row\Col
D
2
=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)))
3
=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0)))



Hi Amelynn
Quote Originally Posted by Amelynn View Post
…..
  • If after TextBox(n) it is necessary to put the .text or .value
  • If not putting anything is wrong (although it works)
  • What is the difference between the three things……
I can’t answer most of your question here, unfortunately, because
_ I have no experience with Textboxes
_ I am not too familiar with the .Text property.

I can only tell you the small part that I know about: - what I know about is
Range__
Range__.Value
Range__.Value2

Range__ object
Range__ is an object with an extremely large number of properties, methods and various things.
Range__ is all to do with how Excel organises and uses cells. Understanding the Range__ object is probably one of the most important things to know about in Excel and VBA, especially if you are interested in spreadsheet things.
But many times, we are only interested in the value that is in a cell. Because most people are often only interested in the value in a cell, Microsoft have made .Value the default of what you get if you just use Range__. So most of the time if you choose to write just Range__ , then in fact , Excel will not see that, instead it will see and use Range__.Value
It is just personal choice if you choose to use Range__.Value or Range__. Usually there are no problems if you just use Range__ , but I have seen occasions when this caused problems as there may be occasions when Excel tries to refer to the Range__ object instead of the value.
So personally I prefer to always include the .Value if I am interested in a value. I will only leave out the .Value if I am doing something that wants me to reference the Range__ object. Just personal choice.

So, in your example, when you used Worksheets("Sheet1").Range("B10") , Excel did not see and use that.
Instead, Excel saw and used this: Worksheets("Sheet1").Range("B10").Value


Quote Originally Posted by Amelynn View Post
....After the range, should I put .text, .value or just nothing? ...
So in your examples you could probably just use nothing , but I personally would recommend that you include .Value ( or .Value2 )
But that is just my personally recommendation

.Value or .Value2
.Value is almost the simplest cell value. But not quite. If you are interested in dates or currency, then .Value will show you the date or currency in a date or currency format.
.Value2 is the most simplest cell value as Excel has it held before any formatting is done.

Personally I will use .Value2 most of the time, because it may work a little faster or may be less likely to problems caused by awkward cell formatting issues. I think theoretically it is also a bit more efficient to use .Value2

So….
Quote Originally Posted by Amelynn View Post
.....If not putting anything is wrong (although it works).....
It is not wrong to put nothing. But it is bad practice, as it may cause problems in other situations in Excel VBA
(More than half of people put nothing, and they will often get a problem later that they don’t understand )


Quote Originally Posted by Amelynn View Post
....Should I necessarily declare that, for example, "Niebla" is a variable of type Str?
The way that you are using "Niebla" in VBA coding is perfectly alright, because: Most of the time in VBA coding, if VBA sees something enclosed in quotes, _ "__" _ , like
"xyz"
, then VBA will take the value of _ xyz _ to be a string.
Even if , in your coding, you did this
"3"
, the VBA would not take the FONT=Courier New]"3"[/FONT] as a number. It would see it as a string, just as it would see this as a string
"I have 3 Apples"


Note that VBA is very user friendly with numbers and strings. For example if you pass it a string like "3" in a function wanting a number, the VBA will not error, but instead it will take a number 3 instead.

In many other computer languages you must be much more careful in defining precisely variable types.



That is as close as I can come to answering your question.
But I do know about Range__ , Range__.Value , Range__.Value2 quite well.
So I am happy to give you any further clarity on those things. Those things are all to do with range referencing in Excel and VBA, which is a very important thing to know about.


Alan






Ref
https://fastexcel.wordpress.com/2011...w-to-avoid-it/