CODE TAG Code Test [Archive] - Excel, Access, PowerPoint and Word VBA Macro Automation Help

DocAElstein

08-22-2014, 02:42 AM

'_________________________________________________ _____________

'_________________________________________________ _____________
'
' Slightly modified with lots of Comments
Sub RickRothsteinsConcatenatingBalls()
'NOTE: Change the ## A1 to the address of the top left cell of your
'existing data and change the '### G1 to the address of the top left cell where you want the converted table to go to.
Const ExistingTableAnyCellLocation As String = "A1" '##
Const NewTableLHCornerLocation As String = "G1" '###

Dim SourceTableRange As Range 'Give a name and allow all methods and properties of range object to it
Set SourceTableRange = Range(ExistingTableAnyCellLocation).CurrentRegion ' Give this a specific Range. CurrentRegiuon Property applied to a cell returns a complete range incorporating that cell and any ranges that it either is in or touches
Dim SourceTableRangeTableRowsCount As Byte ' For a small Table allow it to hve up to 255 Rows
Let SourceTableRangeTableRowsCount = SourceTableRange.Rows.Count ' This returns the number of rows in the original table

Dim FinalTableFirstColumnRange As Range
Set FinalTableFirstColumnRange = Range(NewTableLHCornerLocation).Resize(SourceTable RangeTableRowsCount) ' Here the resize Property applied to the Range G1 (or Cell G1 here) returns a range increased by the row number, that is to say a range equal to the first column

SourceTableRange.Columns(1).Resize(, 2).Copy Destination:=FinalTableFirstColumnRange ' This is one way of copying the first two columns of the original table to The final table
FinalTableFirstColumnRange.Columns(2).NumberFormat = "@" ' This gives a format to the second column in the final Table

'FinalTableFirstColumnRange.Offset(0, 1) = _
'Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & ")")
'FinalTableFirstColumnRange.Offset(0, 1) = _
'Evaluate(" " & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & "")
FinalTableFirstColumnRange.Offset(0, 1) = _
Evaluate(" " & SourceTableRange.Columns(2).Address & " " & "&"" - ""&" & " " & SourceTableRange.Columns(3).Address & " " & "&"" - ""&" & "" & SourceTableRange.Columns(4).Address & "")

SourceTableRange.Columns(5).Copy Destination:=FinalTableFirstColumnRange.Offset(, 2) 'Column 5 of Original table is copied to column 3 of the Final table by setting the destination to 2 colums offset from the first column

FinalTableFirstColumnRange.Cells(1, 0).Offset(0, 1).Value = "Numbers" ' The current heading in the second column is finally overwriten with "Numbers". This is done here by putting the value"Numbers" in the cell which is offset by 1 column to the first cell in the Final Table First Column
End Sub 'RickRothsteinsConcatenatingBalls()
'
''________________________________________________ _______________________________
'_________________________________________________ _________________________________

'_________________________________________________ _____________

' Replacing the line I do not fully understand with a 3 line loop
Sub RickRothsteinsConcatenatingBalls2()
Dim j As Byte
'NOTE: Change the ## A1 to the address of the top left cell of your
'existing data and change the '### G1 to the address of the top left cell where you want the converted table to go to.
Const ExistingTableAnyCellLocation As String = "A1" '##
Const NewTableLHCornerLocation As String = "G1" '###

Dim SourceTableRange As Range 'Give a name and allow all methods and properties of range object to it
Set SourceTableRange = Range(ExistingTableAnyCellLocation).CurrentRegion ' Give this a specific Range. CurrentRegiuon Property applied to a cell returns a complete range incorporating that cell and any ranges that it either is in or touches
Dim SourceTableRangeTableRowsCount As Byte ' For a small Table allow it to have up to 255 Rows
Let SourceTableRangeTableRowsCount = SourceTableRange.Rows.Count ' This returns the number of rows in the original table

Dim FinalTableFirstColumnRange As Range
Set FinalTableFirstColumnRange = Range(NewTableLHCornerLocation).Resize(SourceTable RangeTableRowsCount) ' Here the resize Property applied to the Range G1 (or Cell G1 here) returns a range increased by the row number, that is to say a range equal to the first column

SourceTableRange.Columns(1).Resize(, 2).Copy Destination:=FinalTableFirstColumnRange ' This is one way of copying the first two columns of the original table to The final table
FinalTableFirstColumnRange.Columns(2).NumberFormat = "@" ' This gives a format to the second column in the final Table

For j = 2 To SourceTableRangeTableRowsCount Step 1 ' For each column of data
FinalTableFirstColumnRange.Cells(1, 1).Offset(j - 1, 1) = Evaluate("" & SourceTableRange.Cells(j, 2).Address & "" & "&"" - ""&" & "" & SourceTableRange.Cells(j, 3).Address & "" & "&"" - ""&" & "" & SourceTableRange.Cells(j, 4).Address & "") ' the evaluate fuction can be used in VBA to give the reults from a formula in a normal Excel Spreadsheet. Hier we use it simply to give the results of a formula something of the form =B2 & " - " & C2 & " - " & D2. Just a convenient way of doung the concantenating. Further we here maks the addresses B2 C2 D2 variable in a loop. A very tricky syntax!!!
Next j

SourceTableRange.Columns(5).Copy Destination:=FinalTableFirstColumnRange.Offset(, 2) 'Column 5 of Original table is copied to column 3 of the Final table by setting the destination to 2 colums offset from the first column

FinalTableFirstColumnRange.Cells(1, 0).Offset(0, 1).Value = "Numbers" ' The current heading in the second column is finally overwriten with "Numbers". This is done here by putting the value"Numbers" in the cell which is offset by 1 column to the first cell in the Final Table First Column
End Sub 'RickRothsteinsConcatenatingBalls2()
'

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457 (http://www.eileenslounge.com/viewtopic.php?p=324457#p324457)
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064 (http://www.eileenslounge.com/viewtopic.php?p=324064#p324064)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C)
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg (s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41784)
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966 (http://www.eileenslounge.com/viewtopic.php?p=323966#p323966)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894 (http://www.eileenslounge.com/viewtopic.php?p=323894#p323894)
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843 (http://www.eileenslounge.com/viewtopic.php?p=323843#p323843)
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547 (http://www.eileenslounge.com/viewtopic.php?p=323547#p323547)
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516 (http://www.eileenslounge.com/viewtopic.php?p=323516#p323516)
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517 (http://www.eileenslounge.com/viewtopic.php?p=323517#p323517)
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449 (http://www.eileenslounge.com/viewtopic.php?p=323449#p323449)
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226 (http://www.eileenslounge.com/viewtopic.php?p=323226#p323226)
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150 (http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150)
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085 (http://www.eileenslounge.com/viewtopic.php?p=323085#p323085)
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955 (http://www.eileenslounge.com/viewtopic.php?p=322955#p322955)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41659)
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462 (http://www.eileenslounge.com/viewtopic.php?p=322462#p322462)
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356 (http://www.eileenslounge.com/viewtopic.php?p=322356#p322356)
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984 (http://www.eileenslounge.com/viewtopic.php?p=321984#p321984)
https://eileenslounge.com/viewtopic.php?f=30&t=41610 (https://eileenslounge.com/viewtopic.php?f=30&t=41610)
https://eileenslounge.com/viewtopic.php?p=322176#p322176 (https://eileenslounge.com/viewtopic.php?p=322176#p322176)
https://eileenslounge.com/viewtopic.php?p=322238#p322238 (https://eileenslounge.com/viewtopic.php?p=322238#p322238)
https://eileenslounge.com/viewtopic.php?p=322270#p322270 (https://eileenslounge.com/viewtopic.php?p=322270#p322270)
https://eileenslounge.com/viewtopic.php?p=322300#p322300 (https://eileenslounge.com/viewtopic.php?p=322300#p322300)
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150 (http://www.eileenslounge.com/viewtopic.php?p=322150#p322150)
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111 (http://www.eileenslounge.com/viewtopic.php?p=322111#p322111)
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086 (http://www.eileenslounge.com/viewtopic.php?p=322086#p322086)
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851 (https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851)
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084 (http://www.eileenslounge.com/viewtopic.php?p=322084#p322084)
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822 (http://www.eileenslounge.com/viewtopic.php?p=321822#p321822)
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424 (http://www.eileenslounge.com/viewtopic.php?p=322424#p322424)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

DocAElstein

09-19-2014, 04:24 PM

'_________________________________________________ _____________

'_________________________________________________ _____________
'
' Slightly modified with lots of Comments
Sub RickRothsteinsConcatenatingBalls()
'NOTE: Change the ## A1 to the address of the top left cell of your
'existing data and change the '### G1 to the address of the top left cell where you want the converted table to go to.
Const ExistingTableAnyCellLocation As String = "A1" '##
Const NewTableLHCornerLocation As String = "G1" '###

Dim SourceTableRange As Range 'Give a name and allow all methods and properties of range object to it
Set SourceTableRange = Range(ExistingTableAnyCellLocation).CurrentRegion ' Give this a specific Range. CurrentRegiuon Property applied to a cell returns a complete range incorporating that cell and any ranges that it either is in or touches
Dim SourceTableRangeTableRowsCount As Byte ' For a small Table allow it to hve up to 255 Rows
Let SourceTableRangeTableRowsCount = SourceTableRange.Rows.Count ' This returns the number of rows in the original table

Dim FinalTableFirstColumnRange As Range
Set FinalTableFirstColumnRange = Range(NewTableLHCornerLocation).Resize(SourceTable RangeTableRowsCount) ' Here the resize Property applied to the Range G1 (or Cell G1 here) returns a range increased by the row number, that is to say a range equal to the first column

SourceTableRange.Columns(1).Resize(, 2).Copy Destination:=FinalTableFirstColumnRange ' This is one way of copying the first two columns of the original table to The final table
FinalTableFirstColumnRange.Columns(2).NumberFormat = "@" ' This gives a format to the second column in the final Table

'FinalTableFirstColumnRange.Offset(0, 1) = _
'Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & ")")
'FinalTableFirstColumnRange.Offset(0, 1) = _
'Evaluate(" " & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & "")
FinalTableFirstColumnRange.Offset(0, 1) = _
Evaluate(" " & SourceTableRange.Columns(2).Address & " " & "&"" - ""&" & " " & SourceTableRange.Columns(3).Address & " " & "&"" - ""&" & "" & SourceTableRange.Columns(4).Address & "")

SourceTableRange.Columns(5).Copy Destination:=FinalTableFirstColumnRange.Offset(, 2) 'Column 5 of Original table is copied to column 3 of the Final table by setting the destination to 2 colums offset from the first column

FinalTableFirstColumnRange.Cells(1, 0).Offset(0, 1).Value = "Numbers" ' The current heading in the second column is finally overwriten with "Numbers". This is done here by putting the value"Numbers" in the cell which is offset by 1 column to the first cell in the Final Table First Column
End Sub 'RickRothsteinsConcatenatingBalls()
'
''________________________________________________ _______________________________
'_________________________________________________ _________________________________

'_________________________________________________ _____________

' Replacing the line I do not fully understand with a 3 line loop
Sub RickRothsteinsConcatenatingBalls2()
Dim j As Byte
'NOTE: Change the ## A1 to the address of the top left cell of your
'existing data and change the '### G1 to the address of the top left cell where you want the converted table to go to.
Const ExistingTableAnyCellLocation As String = "A1" '##
Const NewTableLHCornerLocation As String = "G1" '###

Dim SourceTableRange As Range 'Give a name and allow all methods and properties of range object to it
Set SourceTableRange = Range(ExistingTableAnyCellLocation).CurrentRegion ' Give this a specific Range. CurrentRegiuon Property applied to a cell returns a complete range incorporating that cell and any ranges that it either is in or touches
Dim SourceTableRangeTableRowsCount As Byte ' For a small Table allow it to have up to 255 Rows
Let SourceTableRangeTableRowsCount = SourceTableRange.Rows.Count ' This returns the number of rows in the original table

Dim FinalTableFirstColumnRange As Range
Set FinalTableFirstColumnRange = Range(NewTableLHCornerLocation).Resize(SourceTable RangeTableRowsCount) ' Here the resize Property applied to the Range G1 (or Cell G1 here) returns a range increased by the row number, that is to say a range equal to the first column

SourceTableRange.Columns(1).Resize(, 2).Copy Destination:=FinalTableFirstColumnRange ' This is one way of copying the first two columns of the original table to The final table
FinalTableFirstColumnRange.Columns(2).NumberFormat = "@" ' This gives a format to the second column in the final Table

For j = 2 To SourceTableRangeTableRowsCount Step 1 ' For each column of data
FinalTableFirstColumnRange.Cells(1, 1).Offset(j - 1, 1) = Evaluate("" & SourceTableRange.Cells(j, 2).Address & "" & "&"" - ""&" & "" & SourceTableRange.Cells(j, 3).Address & "" & "&"" - ""&" & "" & SourceTableRange.Cells(j, 4).Address & "") ' the evaluate fuction can be used in VBA to give the reults from a formula in a normal Excel Spreadsheet. Hier we use it simply to give the results of a formula something of the form =B2 & " - " & C2 & " - " & D2. Just a convenient way of doung the concantenating. Further we here maks the addresses B2 C2 D2 variable in a loop. A very tricky syntax!!!
Next j

SourceTableRange.Columns(5).Copy Destination:=FinalTableFirstColumnRange.Offset(, 2) 'Column 5 of Original table is copied to column 3 of the Final table by setting the destination to 2 colums offset from the first column

FinalTableFirstColumnRange.Cells(1, 0).Offset(0, 1).Value = "Numbers" ' The current heading in the second column is finally overwriten with "Numbers". This is done here by putting the value"Numbers" in the cell which is offset by 1 column to the first cell in the Final Table First Column
End Sub 'RickRothsteinsConcatenatingBalls2()

DocAElstein

09-19-2014, 04:25 PM

Great. I have the code in code tags in colour. Nice!
(Thanks to Peter_SS at MrExcel for help in this one)

DocAElstein

02-07-2015, 09:03 PM

'3) Part 3)--transfer range objects from dictionary to array of ranges in one go, typical Array assignment "One Liner" - The quick way to place data into an array is to dimension a variable ( for example rResults ) ....
Dim rResults() As Variant '... As a Variant and then the "Let rResults = " code line Using a variant allows BOTH .-1) for capturing Objects, text, numbers, blanks errors etc. from those cells and also 2) allows rResults to be an array variable.....
Let rResults() = dicLookupTable.Items() '... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses rResults()
'End part 3)--- NOTE: this gives automatically the 0 to .. convention in rResults Array! and the output held in the Array is Variant type and is an OBJECT of the sort like a Range. (This compares with a similar code utilising An Array of Ranges rather than the MicrosoftScriptimeRuntimeDictionary. (In the Array of Ranges case we could Dim as Range or Variant here. - Important however in that case is that the Dynamic rResults() Array and the (in that Array of Ranges case non dynamic) arrIn() Array are of the same type. - Either both Range to return an Array of element Type Range or both Variant to Return an Array of element Type Of Objects of the Range Sort : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html

[CODE] Dim TempCell As Range: Set TempCell = wksLE.Cells(1, Columns.Count): Dim TempCellOffset As Long: Let TempCellOffset = 0 'We choose a cell (or through the later use of the offset a column) to use for Duplicate or Empty cells. We use the last column in the sheet. (This is genarally a good practice as it will not effect attempts with .End(XltoLeft) to find last column
'2a) Part2a) Looping to put Range Objects in MRSD
'For i = StartColumnTableOutput To LastColumnTableOutput Step 1
For rws = sr To lr Step 1
If wksLE.Cells(rws, 1).Value <> "" Then 'If cell is not empty then...
If Not dicLookupTable.Exists(wksLE.Cells(rws, 1).Value) Then 'check that the unique value does not already exist. ##NOTE
dicLookupTable.Add wksLE.Cells(rws, 1).Value, wksLE.Cells(rws, 1) 'it is easier to understand as well as kind of explicit the first argument does a CStr and the Second Takes anything
Else 'If the key exists, that is to say we have a Range with a Duplicate value, we give the key a slightly modified (unique value) , still give the Range Object as an item, but make an indication, here by highlighting the cell in Pink
Let TempCellOffset = TempCellOffset + 1
Let TempCell.Offset(TempCellOffset, 0).Value = "Duplicate at " & rws & " | " & 1 & ""
wksLE.Cells(rws, 1).Interior.Color = 10987519
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, wksLE.Cells(rws, 1) 'In case of duplicate we need a unique key, but we stillinclude the Duplicate Range
End If
Else 'Case fo an empty cell - inform of empty cell by writing message in that cell via the Tempory cell
Let TempCellOffset = TempCellOffset + 1 'Go to next free tempory cell in tempory column
Let TempCell.Offset(TempCellOffset, 0).Value = "Empty Cell at " & rws & " | " & 1 & ""
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, TempCell.Offset(TempCellOffset, 0)
End If
Next rws
'Next i
'End Part 2-----------------------------------------------------------

'3) Part 3)--transfer range objects from dictionary to array of ranges in one go, typical Array assignment "One Liner" - The quick way to place data into an array is to dimension a variable ( for example rResults ) ....
Dim rResults() As Variant '... As a Variant and then the "Let rResults = " code line Using a variant allows BOTH .-1) for capturing Objects, text, numbers, blanks errors etc. from those cells and also 2) allows rResults to be an array variable.....
Let rResults() = dicLookupTable.Items() '... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses rResults()
'End part 3)--- NOTE: this gives automatically the 0 to .. convention in rResults Array! and the output held in the Array is Variant type and is an OBJECT of the sort like a Range. (This compares with a similar code utilising An Array of Ranges rather than the MicrosoftScriptimeRuntimeDictionary. (In the Array of Ranges case we could Dim as Range or Variant here. - Important however in that case is that the Dynamic rResults() Array and the (in that Array of Ranges case non dynamic) arrIn() Array are of the same type. - Either both Range to return an Array of element Type Range or both Variant to Return an Array of element Type Of Objects of the Range Sort : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html

DocAElstein

09-16-2015, 04:39 PM

Kyle Extension test: Alt A

Sub EvalutingQuotes()
Dim v As Variant
Let v = "3" ' Results in a Variant variable containing a string value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )

Range("I1").Value = Evaluate("=" & Range("A1").Address & "") 'Explicit Version
Range("I1").Value = Evaluate("" & Range("A1").Address & "") 'Implicit Default

Range("I1").Value = Evaluate(" " & Range("A1").Address & " ") '

Range("I1").Value = Evaluate(Range("A1").Address) 'Common but dangerous variation

Dim strEval As String '
strEval = "=A1" & "&" & "A1": Debug.Print strEval 'gives =A1&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 11

strEval = "=A1" & "&"";""&" & "A1": Debug.Print strEval 'gives =A1&";"&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;1

strEval = "=A1" & "&"";""": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";" & """": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";" ""
Range("I1").Value = Evaluate("" & strEval & "") 'error
strEval = "=A1" & "&"";"";""" & """": Debug.Print strEval 'gives=A1&";";""
Range("I1").Value = Evaluate("" & strEval & "") 'error

strEval = "=A1" & "&"";""" & """" & """": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! :)

strEval = "=A1" & "&"";""""""": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! :)

' strEval = "=A1" & "&"";""""": Debug.Print strEval 'gives =A1&";""
' Range("I1").Value = Evaluate("" & strEval & "") 'error

End Sub

Not working today??

...................................

Normal Code Tags

Sub EvalutingQuotes()
Dim v As Variant
Let v = "3" ' Results in a Variant variable containing a string value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )

Range("I1").Value = Evaluate("=" & Range("A1").Address & "") 'Explicit Version
Range("I1").Value = Evaluate("" & Range("A1").Address & "") 'Implicit Default

Range("I1").Value = Evaluate(" " & Range("A1").Address & " ") '

Range("I1").Value = Evaluate(Range("A1").Address) 'Common but dangerous variation

Dim strEval As String '
strEval = "=A1" & "&" & "A1": Debug.Print strEval 'gives =A1&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 11

strEval = "=A1" & "&"";""&" & "A1": Debug.Print strEval 'gives =A1&";"&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;1

strEval = "=A1" & "&"";""": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";" & """": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";" ""
Range("I1").Value = Evaluate("" & strEval & "") 'error
strEval = "=A1" & "&"";"";""" & """": Debug.Print strEval 'gives=A1&";";""
Range("I1").Value = Evaluate("" & strEval & "") 'error

strEval = "=A1" & "&"";""" & """" & """": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! :)

strEval = "=A1" & "&"";""""""": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! :)

' strEval = "=A1" & "&"";""""": Debug.Print strEval 'gives =A1&";""
' Range("I1").Value = Evaluate("" & strEval & "") 'error

End Sub

...........................

Old Alan Methd

Sub EvalutingQuotes()
Dim v As Variant
Let v = "3" ' Results in a Variant variable containing a string value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )

Range("I1").Value = Evaluate("=" & Range("A1").Address & "") 'Explicit Version
Range("I1").Value = Evaluate("" & Range("A1").Address & "") 'Implicit Default

Range("I1").Value = Evaluate(" " & Range("A1").Address & " ") '

Range("I1").Value = Evaluate(Range("A1").Address) 'Common but dangerous variation

Dim strEval As String '
strEval = "=A1" & "&" & "A1": Debug.Print strEval 'gives =A1&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 11

strEval = "=A1" & "&"";""&" & "A1": Debug.Print strEval 'gives =A1&";"&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;1

strEval = "=A1" & "&"";""": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";" & """": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";" ""
Range("I1").Value = Evaluate("" & strEval & "") 'error
strEval = "=A1" & "&"";"";""" & """": Debug.Print strEval 'gives=A1&";";""
Range("I1").Value = Evaluate("" & strEval & "") 'error

strEval = "=A1" & "&"";""" & """" & """": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! :)

strEval = "=A1" & "&"";""""""": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! :)

' strEval = "=A1" & "&"";""""": Debug.Print strEval 'gives =A1&";""
' Range("I1").Value = Evaluate("" & strEval & "") 'error

End Sub

............................
EDIT : Try again...

change my default
From:

General Settings
Message Editor Interface
Enhanced Interface - Full WYSIWYG Editing

To:

General Settings
Message Editor Interface
Standard Editor - Extra formatting controls

Alt A

Sub EvalutingQuotes()
Dim v As Variant
Let v = "3" ' Results in a Variant variable containing a string value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )

Range("I1").Value = Evaluate("=" & Range("A1").Address & "") 'Explicit Version
Range("I1").Value = Evaluate("" & Range("A1").Address & "") 'Implicit Default

Range("I1").Value = Evaluate(" " & Range("A1").Address & " ") '

Range("I1").Value = Evaluate(Range("A1").Address) 'Common but dangerous variation

Dim strEval As String '
strEval = "=A1" & "&" & "A1": Debug.Print strEval 'gives =A1&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 11

strEval = "=A1" & "&"";""&" & "A1": Debug.Print strEval 'gives =A1&";"&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;1

strEval = "=A1" & "&"";""": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";" & """": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";" ""
Range("I1").Value = Evaluate("" & strEval & "") 'error
strEval = "=A1" & "&"";"";""" & """": Debug.Print strEval 'gives=A1&";";""
Range("I1").Value = Evaluate("" & strEval & "") 'error

strEval = "=A1" & "&"";""" & """" & """": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!!

strEval = "=A1" & "&"";""""""": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!!

' strEval = "=A1" & "&"";""""": Debug.Print strEval 'gives =A1&";""
' Range("I1").Value = Evaluate("" & strEval & "") 'error

End Sub

Kyle

09-16-2015, 05:16 PM

Sub EvalutingQuotes()
Dim v As Variant
Let v = "3" ' Results in a Variant variable containing a string value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )

Range("I1").Value = Evaluate("=" & Range("A1").Address & "") 'Explicit Version
Range("I1").Value = Evaluate("" & Range("A1").Address & "") 'Implicit Default

Range("I1").Value = Evaluate(" " & Range("A1").Address & " ") '

Range("I1").Value = Evaluate(Range("A1").Address) 'Common but dangerous variation

Dim strEval As String '
strEval = "=A1" & "&" & "A1": Debug.Print strEval 'gives =A1&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 11

strEval = "=A1" & "&"";""&" & "A1": Debug.Print strEval 'gives =A1&";"&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;1

strEval = "=A1" & "&"";""": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";" & """": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";" ""
Range("I1").Value = Evaluate("" & strEval & "") 'error
strEval = "=A1" & "&"";"";""" & """": Debug.Print strEval 'gives=A1&";";""
Range("I1").Value = Evaluate("" & strEval & "") 'error

strEval = "=A1" & "&"";""" & """" & """": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!!

strEval = "=A1" & "&"";""""""": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!!

' strEval = "=A1" & "&"";""""": Debug.Print strEval 'gives =A1&";""
' Range("I1").Value = Evaluate("" & strEval & "") 'error

End Sub