VBA Run-Time Error 1004: Range(“ ”).Formula

Question

Basically, I would like to change the significant figure of a cell based on the number of significant figure of another cell.

I got a formula that works in worksheet cell, the source of magic formula: https://stackoverflow.com/a/41891803/9111492

And now, I would like to move that formula to vbe so that the formula is insert to a cell when the button is pressed. But I am facing an error.

Here's the code that I had edited and got an

error 1004

Dim NRC As Range
Range(NRC.Offset(-1, 35), NRC.Offset(-1, 38)).Formula = _
        "=TEXT(IF(" & NRC.Offset(-1, 39).Address & "<0,""-"","""")&LEFT(TEXT(ABS(" & NRC.Offset(-1, 39).Address & "),""0.""&REPT(""0""," & NRC.Offset(-1, 41).Address & "-1)&""E+00"")," & NRC.Offset(-1, 41).Address & "+1)*10^FLOOR(LOG10(TEXT(ABS(" & NRC.Offset(-1, 39).Address & "),""0.""&REPT(""0""," & NRC.Offset(-1, 41).Address & "-1)&""E+00"")),1),(""""&(IF(OR(AND(FLOOR(LOG10(TEXT(ABS(" & NRC.Offset(-1, 39).Address & "),""0.""&REPT(""0""," & NRC.Offset(-1, 41).Address & "-1)&""E+00"")),1)+1=" & NRC.Offset(-1, 41).Address & ",RIGHT(LEFT(TEXT(ABS(" & NRC.Offset(-1, 39).Address & "),""0.""&REPT(""0""," & NRC.Offset(-1, 41).Address & "-1)&""E+00"")," & NRC.Offset(-1, 41).Address & "+1)*10^FLOOR(LOG10(TEXT(ABS(" & NRC.Offset(-1, 39).Address & "),""" & _
        "T(""0""," & NRC.Offset(-1, 41).Address & "-1)&""E+00"")),1),1)=""0""),LOG10(TEXT(ABS(" & NRC.Offset(-1, 39).Address & "),""0.""&REPT(""0""," & NRC.Offset(-1, 41).Address & "-1)&""E+00""))<=" & NRC.Offset(-1, 41).Address & "-1),""0."",""#"")&REPT(""0"",IF(" & NRC.Offset(-1, 41).Address & "-1-(FLOOR(LOG10(TEXT(ABS(" & NRC.Offset(-1, 39).Address & "),""0.""&REPT(""0""," & NRC.Offset(-1, 41).Address & "-1)&""E+00"")),1))>0," & NRC.Offset(-1, 41).Address & "-1-(FLOOR(LOG10(TEXT(ABS(" & NRC.Offset(-1, 39).Address & "),""0.""&REPT(""0""," & NRC.Offset(-1, 41).Address & "-1)&""E+00"")),1)),0)))))"

NRC is the name for a range

NRC.Offset(-1, 39) is the cell that contain the number that I would like to change the significant number

NRC.Offset(-1, 41) Is the cell that contain the number of significant figure

Any help is appreciated!

ashleedawg · Accepted Answer · 2017-12-18T04:19:41.863

Copy the entire formula from the answer that you are trying to duplicate. Here's an exact copy of it:

=TEXT(IF(A1<0,"-","")&LEFT(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00"),sigfigs+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1),(""&(IF(OR(AND(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1)+1=sigfigs,RIGHT(LEFT(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00"),sigfigs+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1),1)="0"),LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00"))<=sigfigs-1),"0.","#")&REPT("0",IF(sigfigs-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1))>0,sigfigs-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1)),0)))))

Create a new workbook with sample data:
- File → New → Blank Workbook. On the new worksheet:
- enter number 12.345 into cell A1
- enter number 6 into cell B1 (and hit Enter)
Create a named cell sigfigs:
- Right-click cell B1, click Define Name...
- Type sigfigs and press Enter
Next, Create a new VBA module: Alt+F11 Alt+I Alt+M
In the new module, start with the first line: Option Explicit _{(which should always be used at the top of every module you're working in, especially while learning or troubleshooting code.)}
Skip a (blank) line and then start a new sub like:

Sub PrecisionTest()

... and then hit Enter and the VBE will add the End Sub line below.
In between the lines Sub... and End Sub: Paste the formula into the VBA Editor: Ctrl+V
Replace each set of quotes with two sets of quotes:
- Ctrl+H
- Find What: "
- Replace With: ""
- Click Replace All. (It should say 62 replacements were made.)
- Click OK and click the X to close the Replace window.
At the beginning of the pasted line of code, enter: Range("C1").Formula="
At the end of the pasted line of code, enter one more set of quotes: " _{^{(or just click on different line and it will be added automatically since it's missing from the end.)}}

Your module should look now like this:

Option Explicit

Sub PrecisionTest()

    Range("C1").Formula = "=TEXT(IF(A1<0,""-"","""")&LEFT(TEXT(ABS(A1),""0.""&REPT(""0"",sigfigs-1)&""E+00""),sigfigs+1)*10^FLOOR(LOG10(TEXT(ABS(A1),""0.""&REPT(""0"",sigfigs-1)&""E+00"")),1),(""""&(IF(OR(AND(FLOOR(LOG10(TEXT(ABS(A1),""0.""&REPT(""0"",sigfigs-1)&""E+00"")),1)+1=sigfigs,RIGHT(LEFT(TEXT(ABS(A1),""0.""&REPT(""0"",sigfigs-1)&""E+00""),sigfigs+1)*10^FLOOR(LOG10(TEXT(ABS(A1),""0.""&REPT(""0"",sigfigs-1)&""E+00"")),1),1)=""0""),LOG10(TEXT(ABS(A1),""0.""&REPT(""0"",sigfigs-1)&""E+00""))<=sigfigs-1),""0."",""#"")&REPT(""0"",IF(sigfigs-1-(FLOOR(LOG10(TEXT(ABS(A1),""0.""&REPT(""0"",sigfigs-1)&""E+00"")),1))>0,sigfigs-1-(FLOOR(LOG10(TEXT(ABS(A1),""0.""&REPT(""0"",sigfigs-1)&""E+00"")),1)),0)))))"

End Sub

Click somewhere inside the sub and execute it by pressing F5. Worksheet cell C1 will be populated with the formula, the result of which will be a string: 12.3450.

Copying other people's posted (working) code snippets is very common among coders of all experience levels, however it's important to first try to copy the code exactly to duplicate it.

Only after your copy is verified to be working as expected (correct results; no errors) should you begin to add your own modifications as needed. Make one small change at a time, executing the code to test it after each change to ensure it's still working... (especially at the beginning of the VBA learning-curve.)

Also, Excel cells are referred to with Letters (Columns) and Numbers (Rows) so there is no position with a negative number like you were trying to do (and you didn't show any code to declare NRC as an object, nor to Set is as a Range, so you weren't using Offset properly.

Here is a link showing syntax and examples of the Range object, and here is one about the Offset method. I'd also recommend that you check out:

Not sure if this will help OP or not; I actually remembered my mom in this post lol and so I'll up vote. :) haha — L42, Dec 18 '17 at 05:20
Thanks -- there's a "communication disconnect" I cant identify so I gave it my best shot. (At the least it might help others!) ... Your Mom was a coder? Mine sure wasn't; the Microwave was hard enough to master. — ashleedawg, Dec 18 '17 at 05:58
Nah she was not. But that is the way she would've given me instruction when she wants to be sure that I do something correctly. Sort of "nagging" at me. haha peace! :p — L42, Dec 18 '17 at 06:43
@ashleedawg Thanks for the detailed explanation and the step by step guide. It works perfectly. Apologies for making so many confusions as I am new to this community and also VBA. I will learn how to be clear in the question and I'll give more details in the post in future. Thanks a lot and have a great day ahead! — LieTheng, Dec 18 '17 at 06:43
@L42 hope you can comment something that is useful for every person here. And your vote should be based on the quality of the answer, not because you think it remind you of your mom! — LieTheng, Jun 03 '19 at 08:18
@LieTheng That was for ashlee as a way of praising her efforts in adding a very detailed answer. No harm intended. And when she answered this, she's just beginning her journey in contributing to the community so I kinda welcome her because seriously, she put up a lot of effort in this post. I actually can't comment anything more because she'd covered it all. — L42, Jun 07 '19 at 09:54
@ashleedawg I hope my comments doesn't imply any harm or misunderstanding in a way. I'll delete all my comments here once you've responded. — L42, Jun 07 '19 at 09:55

VBA Run-Time Error 1004: Range(“ ”).Formula

1 Answers1

Your module should look now like this: