Home

VBA code needed: if a number, then apply a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-841843%22%20slang%3D%22en-US%22%3EVBA%20code%20needed%3A%20if%20a%20number%2C%20then%20apply%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841843%22%20slang%3D%22en-US%22%3E%3CP%3ESo%2C%20I%20have%20this%20ridiculous%20formula%20I%20found%20online%20that%20takes%20a%20number%20(in%20cell%20A2%2C%20for%20example)%20and%20provides%20a%20number%20with%203%20significant%20figures.%20It%20actually%20works%20well.%20Typically%2C%20I%20just%20use%20a%20neighboring%20column%20and%20apply%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20presume%20that%20there's%20an%20easier%20way%20using%20VBA%20to%20simply%20take%20any%20number%20and%20send%20it%20though%20this%20formula%20and%20replace%20the%20number%20in%20the%20cell.%20Could%20you%20think%20of%20a%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esomething%20like%2C%20for%20every%20cell%20in%20the%20sheet%2C%26nbsp%3B%3C%2FP%3E%3CP%3E-%20isnumber%20%3D%20TRUE%20then%20apply%20formula%2C%20replace%20the%20original%20number%3C%2FP%3E%3CP%3E-%20isnumber%20%3D%20FALSE%20then%20next%20(i.e.%20just%20leave%20the%20cell%20alone.%20In%20many%20cases%2C%20there%20will%20be%20text%20in%20cells)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20sig%20fig%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXT(TEXT(A2%2C%22.%22%26amp%3BREPT(%220%22%2C3)%26amp%3B%22E%2B000%22)%2C%0A%220%22%26amp%3BREPT(%22.%22%2C(3-(1%2BINT(LOG10(ABS(%0A1*TEXT(A2%2C%22.%22%26amp%3BREPT(%220%22%2C3)%26amp%3B%22E%2B000%22)%0A)))))%26gt%3B0)%26amp%3B%20REPT(%220%22%2C(3-(1%2BINT(LOG10(ABS(%0A1*TEXT(A2%2C%22.%22%26amp%3BREPT(%220%22%2C3)%26amp%3B%22E%2B000%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-841843%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
gms4b
Contributor

So, I have this ridiculous formula I found online that takes a number (in cell A2, for example) and provides a number with 3 significant figures. It actually works well. Typically, I just use a neighboring column and apply it. 

 

I presume that there's an easier way using VBA to simply take any number and send it though this formula and replace the number in the cell. Could you think of a way to do this?

 

something like, for every cell in the sheet, 

- isnumber = TRUE then apply formula, replace the original number

- isnumber = FALSE then next (i.e. just leave the cell alone. In many cases, there will be text in cells)

 

Here is the sig fig formula:

=TEXT(TEXT(A2,"."&REPT("0",3)&"E+000"),
"0"&REPT(".",(3-(1+INT(LOG10(ABS(
1*TEXT(A2,"."&REPT("0",3)&"E+000")
)))))>0)& REPT("0",(3-(1+INT(LOG10(ABS(
1*TEXT(A2,"."&REPT("0",3)&"E+000")

 

Any ideas? 

 

Thanks,

 

Greg

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies