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

New Member
Joined
Feb 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
1
2
3
4
5
6123456ABCDEF<td></td><td></td>
7
8
Sheet1
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,305
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

EH53

New Member
Joined
Feb 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
1
2
3
4
5
6123456ABCDEF<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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,305
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,119
Messages
5,622,837
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top