Copy from http://www.excelforum.com/showthread...11#post4555023 https://www.excelforum.com/developme...ml#post4555023


Function and demo Codes to Demonstrate Range Item Property
Introduction, what is this post about? :
This Post explains some preliminary ideas that covers the basics behind putting displayed text into a cell through using VBA to fill in one of the Formula Properties memories of the Range Object of that cell, that is to say passing a formula string ( .Formula ( .Formula is the column letter and row number convention formula type ) ) to the Range Object to which the displayed cell obtains the value ( .Value ) it displays. ( So it ends up looking like this in the Spreadsheet http://imgur.com/k9IYFUj . For that example we will learn that in VBA we need to do something like this "=" & """""""" & "Hello from C3" & """""""" to put those values in )
The code of which the first half is described in this post, ( and shown in the next post http://www.excelforum.com/showthread...24#post4555024 is a demo code routine ( not a Function ) to illustrate the basic ideas behind the Function in the over over next posts. But it makes a good parallel alternative explanation of the
Range Item Property
The Range Item Property is used to return a single cell Range Object starting from the top left cell of a Range Object of one or more contiguous ( all there ) cells (More correctly said a Range Object of one or more Range Objects from a contiguous spreadsheet rectangular area ). It applies specifically to one area. ( A Range Object can be that of only one rectangular area of contiguous spreadsheet rectangular, but strictly speaking the first area , which mostly we talk about, is just one of many possible. Strictly speaking a Range Object is a collection of one or more Range Objects from a contiguous spreadsheet rectangular areas. Typically we use just one and when we use a code part like Rng. VBA assumes we meant to write Rng.Areas.Item(1). )
Range Item Property and Demo code description
The Range Item Property follows for two argument case the usual spreadsheet ( row_coordinate , column_coordinate ) convention starting from top left and going ( for positive coordinates ) “down” the “rows” and “across to the right” of columns.
For the single argument case, ( integer item number or integer index ) , the Range Item Property follows the usual all columns “across to the right” , then next row “down” convention.
Rem 1) The first few code lines use the Cells Property to return a Range Object of all the cells ( Range Objects ) in a Worksheet.
( Lines 50 – 70 shows how it comes about that we typically see a code line such as code line 70. I would suggest that is one of the main uses of the Cells Property http://www.excelforum.com/showthread...11#post4552374 http://www.excelforum.com/showthread...11#post4553399 )
Quotes in Excel VBA
As an aside we need to tackle this tricky theme, as a quick review will help make the following explanations and code lines a little less daunting.
http://www.excelfox.com/forum/showth...page2#post8836
http://www.mrexcel.com/forum/excel-q...ml#post4283381
http://www.excelfox.com/forum/showth...=9517#post9517
http://www.eileenslounge.com/viewtop...196259#p196259
Our final goal in this sub section is to be able to confidentially put a formula string in a cell using VBA. That is to say ...putting displayed text into a cell through using VBA to fill in one of the Formula Properties memories of the Range Object of that cell, that is to say passing a formula string to the Range Object to which the displayed cell obtains the value ( .Value ) it displays...( and doing that such that literal quotes appear within the final “seen” cell displayed text )

Display text in cell from text in Range Object .Value
Generally in VBA a start and a stop quote “ “ signal that what between is a String. So a code line like_..
Range(“A1”).Value = “Some Text”
_.. or code lines like
Dim strText As String
__ strText = "Some Text"
__ Range("A1").Value = strText
_..will recognise __ Some Text __ as a literal string and give the Range Object of the first cell that value, __ Some Text __ The .Value Property will become this string value by virtue of the ( write ) lines 100 and 110. ( The .Value Property is the Range Object value that is displayed by default in the spreadsheet cell ). This can also be seen clearly by stepping past ( F8 Debug mode code progression ) the code line 115 and hitting Ctrl+G to reveal in the Immediate ( Debug ) window, what the code line Debug.Print has “printed out” to that Window. ( The Immediate ( Debug ) Window is a help window available to us ) . The code Ws.Range("A1").Value has effectively read what is held in the Range Object of cell A1 for its .Value
( In line 140, we “write” via .Formula our string formula in column letter and row number notation type to the Range Object Property which houses those type of strings. That sounds reasonable. But using .Value will also work. I expect there is a parallel or the same thing going on there to when you manually write in a cell = ... after this Excel is expecting a formula and assigns appropriately the various values Properties, and then finally displays in the cell the .Value, and in the formula bar the .Formula )

Adding quotes to strings
To have a quote displayed requires generally within the string a double quote. If there is a strict theory to that then I expect very few people are privy to that. Two possible explanations I can think off are
_ the complier or similar simply looks inside text after a single quote for pairs and makes the appropriate addition in the held string
_ a syntax of a pair of quotes is always required and as such allowed. Effectively as in typical HTML type code these “start and stop “Tags”” signal that what within is a literal text. As such a pair like “” is valid ( and signals an empty string ) , a double quote within like “””” or ” “” “ is also valid syntax . Possibly there is some sort of confusion or uncertainty in what the complier actual sees here. Somehow I am going in and out of a literal string but at the same time joining them with a valid literal character of no length. Possibly somehow simultaneously two strings are seen but accompanying or applied to the same memory space, such as seeing the blue and purple in the following at the same point of zero length “ ““ “ . Having recognised a literal “ at “zero” length, it therefore joins the two sides of that text string with a single “ between at that point. This “ being added at point of zero length is like “inserting in” , which then somehow “shifts” appropriately, effectively adding 1 to the length ( Ref A.E. Jan 2016 )

Display text and quotes in cell from text in Range Object .Formula
Just taking the above one step further as this will also help in the understanding of the code routines discussed here. The Range Object can be given a formula, (with VBA as in Line 140) . (This typically is then seen displayed in the Formula bar). Writing manually in a cell = is recognised by Excel as indicating that what comes after is a formula. Text can be included in a formula, and the same convention is used as in VBA to indicate this by enclosing the text in a quote pair “ “. ( We would write manually like = "Some Text" ).
( _ .. At this point, in an attempt to help minimise the inevitable confusion that arises from the whole quotes theme, I use a general convention of
_ ..Blue font showing all VBA stuff which includes the quotes and ampersands used to build formula expressions
_ ..and
Green font showing what one actually would write in a spreadsheet cell and includes the literal quotes and ampersands needed to assign to the Range Object to get the final wanted result in the spreadsheet cell
_.. and Purple font showing what one needs to write ( in our discussions quote pairs ) differently within a VBA String in order to get something similar ( In our discussions a single quote ) in a String text written to a cell. For most normal simple characters and numbers ( in other words mostly non punctuation ) the color Purple translates directly to the color green , so will be shown in green to help the necessary different Purple more apparent.

In a spreadsheet one would manually write something of this form
="Some Text” or ="Some " & "Text" – Note here that I often include extra coupling through some redundant &s , as this helps constancy and compatibility / modifying and building strings.
In a string built in VBA to paste out to a cell we need to produce a string which internally looks like those_.
_. This would then take these forms in VBA code ( Lines 170 and 180 )
strText = "=" & """Some Text""" or strText = "=" & """Some “”" & "&" & """Text"""

Note: In a Range Object the formula String values, such as .Formula and the “value” String values are related. We may therefore assign out formula String, strText to either the Range Object .Formula or .Value. Regardless we will see displayed the Formula in the Formula bar, and the value, .Value, in the cell Some_Text_Formula_Value.JPG http://imgur.com/XVEzS7H

' '2b)(i)
It can be confusing to go finally to the case of a VBA string which will produce a final seen string value in a cell which may also include a quote. But really it is just a careful progression of the steps and ideas discussed thus far.
For example, say, we finally want this displayed in a cell
Some Text and a Quote "
But we want that to come from a formula ( as seen in the Formula bar )
Based on what we have disused thus far, we would need to write manually in the cell something of this form
=”Some Text and a Quote “ & “”””
One way to achieve this would be produce this String in VBA to be written to the Range Object of that cell
strText = "= " & """Some Text and a Quote """ & "&" & """"""""""
Once again for clarity here: That last line needs to produce the string in Debug.Print ( or as seen by hovering with the mouse over strText after the code has passed that line in debug, F8 mode ) which looks like we would manually write in the cell
Code lines in ' '2b)(i) take us through the above

Lines in ' '2b)(ii) follow a similar development to finally put a Formula into the Range Object of cell C3 to produce a simple test string displayed in the spreadsheet cell. HelloFromC3.JPG http://imgur.com/k9IYFUj
Note here: Lines 240 – 260 shows how it comes about that we typically see a code line such as code line 260 which I suggest gives the false idea of a Cells( ) Property which takes arguments. In fact I believe we are looking at the default Property of the Range Item Property applied to the first area Range Object of contiguous cells of the Range Object returned by the Cells Property. ( The Cells Property applied to a Worksheet can only return a single area of contiguous cells , but a Range Object is organised into a collection such areas of contiguous cells )



_.......
That covers the basics behind putting displayed text into a cell through using VBA to fill in the Formula of the Range Object of that cell, that is to say passing a formula string to the Range Object to which the displayed cell obtains the value it displays. The reason for doing that will become apparent in the explanation of the second part of the code on the over next post is reviewed
_....