asked on

EXCEL VBA FORMULAR1C1 Write Formual as Text

I am working with Excel VBA and SQL to populate an Excel Spreadsheet. I am trying to have the VBA write a FORMULAR1C1 attribute. The actual text formula is written instead of the formula. The formula is a text field stored in the SQL View.

select StockNumber, Body, [UnitYear], Make, Description, Customer, NUD, Loc
	  ,' ' [Type] 
	  ,'=RC[-3] & RC[-2] & RC[-1]' as Combined
	  ,Status

	  ,SigningDate
from UnitSales

Open in new window

The VBA code determines that the first character is the "=" sign and adjusts to using the FormulaR1C1 attribute line.

     If Left(rs.Fields(i), 2) = "=" Then
        ActiveCell.Offset(0, i).FormulaR1C1 = rs.Fields(i)   
      Else
        ActiveCell.Offset(0, i).Value = rs.Fields(i)
      End If

Open in new window

The column attribute is set to General.

redmondb

Hi, ButlerTechnology.

If you just want to display the formula in the cell then the easiest way is to precede it by a single quote. (You could change the cell's format to "Text" but I wouldn't recommend that.)

In addition, your "-" test should only be taking the first character from the string, not the first two.

Combining the above...

ActiveCell.Offset(0, i).value  = iif(Left(rs.Fields(i), 1) = "=" ,"'","") & rs.Fields(i)

Open in new window

Regards,
Brian.

ButlerTechnology

ASKER

Brian:

Thanks for the feedback. I corrected the LEFT statement. The main issue is that it puts the formula as text instead of a formula. The column in the SQL view would have =rc[-1] + rc[-2] and when I attempt to assign the cell a value with .FORMULAR1C1 it simply puts the formula as text.

Tom

redmondb

Thanks, Tom.

Apologies, I had this the wrong way about. Please post a file with just that cell showing as text.

Thanks,
Brian.

ButlerTechnology

ASKER

Brian:

I have attached an excel workbook with the results. The E column is the text that is being passed from the SQL View. The F column show the formula being 'hard-coded' in the VBA for troubleshooting.

For i = 0 To rs.Fields.Count - 1
      If Left(rs.Fields(i), 1) = "=" Then
        ActiveCell.Offset(0, i).FormulaR1C1 = rs.Fields(i)
        ActiveCell.Offset(0, i + 1).FormulaR1C1 = "=(RC[-4]+RC[-3]+RC[-2])/3"
      Else
        ActiveCell.Offset(0, i).Value = rs.Fields(i)
      End If
    Next

Open in new window

R1C1-Formula-Issues.xlsx

redmondb

Thanks, Tom.

Yes, I see the same result in column E. This is consistent with what's showing inside the file - those cells are just strings.

After a bit of mucking about, I can only reproduce your result by first setting the cell's format to "Text", writing the formula and then changing the cell to "General". Assuming that you're not doing anything of the kind, please add the following two lines after line 2 of the code in your latest post...

debug.print "-" & rs.Fields(i) & "-"
stop

Open in new window

If you're not comfortable with Debug and Stop please let me know before you do this, and I'll explain. If you are OK with it then please try it out and copy and paste here exactly what's written to the Immediate Pane. (I only need the first output.)

Thanks,
Brian.

ButlerTechnology

ASKER

Brian:

Here's the immediate Window results:

-=(RC[-3]+RC[-2]+RC[-1])/3-

Open in new window

Here's any interesting tidbit -- I set up a message box after assigning the FormulaR1C1 and it returns an empty message box.

Tom

ASKER CERTIFIED SOLUTION

ButlerTechnology

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

redmondb

Glad you're sorted, Tom. Please accept your post as the answer.

(Whatever about the CSTR, I think the brackets is extraordinary.)

ButlerTechnology

ASKER

It appears that the value being assigned to FormulaR1C1 must be a string. In my scenario, the rs.fields(i) was being evaluated as not a string, so it was assigned using the VALUE attribute. The parenthesis tell the system to evaluate the rs.fields(i) first.

I would have expected that to be the case, but it could be proactive error checking when using the FORMULAR1C1 attribute.