I want to create VBA code which will create text in an Excel cell which begins with an 'equals sign' without it being recognised as a formula

[EH53]

I am developing VBA code in an Excel spreadsheet to create a character string which will subsequently be used in HTML code to define data in a website (MiniSheet provided at end of this post.). The character string I am creating is a combination of characters and a concatenation of a number of cells in a row in the spreadsheet. The following is an example of what I am trying to do -

• In cell A6 is the value 123456
• In cell B6 is the value ABCDEF
• In cell I6 I want the following character string -
  • =<td>123456</td><td>ABCDEF</td>
  • where the strings 123456 and ABCDEF are derived from the values in cells A6 and B6 respectively.

I have two issues.

1. how to create a character string which starts with an = sign in a cell without it wanting to treat the contents of the cell as a formula.
2. how to resolve the references to cells A6 and B6 in the character string in I6.

My initial test code (see attached spreadsheet) was this -

Sub ColumnI()
'
' Generate Character String
'
Dim String1 As String
Dim String2 As String
Dim String3 As String
Dim ConcatCell As String
'
String1 = "=<td>"
String2 = "</td><td>"
String3 = "</td>"

ConcatCell = String1 & A6 & String2 & B6 & String3

Range("I6").Select
ActiveCell.FormulaR1C1 = ConcatCell
End Sub

which fails with
Run-time error '1004':
Application-defined or object-defined error

By removing the = sign from the start of String1 the macro runs to completion but does not resolve the values in cells A6 and B6. The result in I6 is therefore -
<td></td><td></td>
This shows '<td></td><td></td> in the formula bar. Note the single quote as the first character indicating a character string.

The result I am expecting

• in the 'formula bar' is
  • ="<td>"&A6&"</td><td>"&B6&"</td>"
• in cell I6 is
  • <td>123456</td><td>ABCDEF</td>

I would appreciate any advice on this as I have been agonising over it for a number of days!!

Sample Spreadsheet.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5
6	123456	ABCDEF							<td></td><td></td>
7
8
Sheet1

 

[Fluff]

How about

Sub ColumnI()
'
' Generate Character String
'
Dim String1 As String
Dim String2 As String
Dim String3 As String
Dim ConcatCell As String
'
String1 = "=<td>"
String2 = "</td><td>"
String3 = "</td>"

ConcatCell = String1 & [A6] & String2 & [B6] & String3

With Range("I6")
   .NumberFormat = "@"
   .Value = ConcatCell
End With
End Sub

 

[EH53]

I am developing VBA code in an Excel spreadsheet to create a character string which will subsequently be used in HTML code to define data in a website (MiniSheet provided at end of this post.). The character string I am creating is a combination of characters and a concatenation of a number of cells in a row in the spreadsheet. The following is an example of what I am trying to do -

• In cell A6 is the value 123456
• In cell B6 is the value ABCDEF
• In cell I6 I want the following character string -
  • =<td>123456</td><td>ABCDEF</td>
  • where the strings 123456 and ABCDEF are derived from the values in cells A6 and B6 respectively.

I have two issues.

1. how to create a character string which starts with an = sign in a cell without it wanting to treat the contents of the cell as a formula.
2. how to resolve the references to cells A6 and B6 in the character string in I6.

My initial test code (see attached spreadsheet) was this -

Sub ColumnI()
'
' Generate Character String
'
Dim String1 As String
Dim String2 As String
Dim String3 As String
Dim ConcatCell As String
'
String1 = "=<td>"
String2 = "</td><td>"
String3 = "</td>"

ConcatCell = String1 & A6 & String2 & B6 & String3

Range("I6").Select
ActiveCell.FormulaR1C1 = ConcatCell
End Sub

which fails with
Run-time error '1004':
Application-defined or object-defined error

By removing the = sign from the start of String1 the macro runs to completion but does not resolve the values in cells A6 and B6. The result in I6 is therefore -
<td></td><td></td>
This shows '<td></td><td></td> in the formula bar. Note the single quote as the first character indicating a character string.

The result I am expecting

• in the 'formula bar' is
  • ="<td>"&A6&"</td><td>"&B6&"</td>"
• in cell I6 is
  • <td>123456</td><td>ABCDEF</td>

I would appreciate any advice on this as I have been agonising over it for a number of days!!

Sample Spreadsheet.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5
6	123456	ABCDEF							<td></td><td></td>
7
8
Sheet1

Excellent!

Thanks very much. That does exactly what I want it to do. I don't think I would have got there on my own.

 

[Fluff]

Glad to help & thanks for the feedback.