range.NumberFormat = "0.0E+00"... not working for me.

[HankJ]

In a cell AB2 I have the value 10.
In cell AB3 I would like to have the value as 1.0E-05.

I have tried any number of ways of doing this and just not getting it.

My last attempt was:

' Top concentration, assuming 1 Section
Dim TC As String                ' Top Conc
Dim TC1 As Long                 ' the value rather than the string
Dim TC2 As Double
If Q1.Range("C1").Value = 1 Then
Q1.Range("B18").FormulaR1C1 = "=LEFT(R[1]C,LEN(R[1]C)-3)"
TC1 = Q1.Range("B18").Value
TC = ">" & Format(TC1 / 1000000, "0.0E+00")
Else
End If      ' end if for there is only 1 section

' Stamp Cust file with TopConc
IC50Cust.Range("AB1").Value = TC
IC50Cust.Range("AB2").Value = TC1
IC50Cust.Range("AB3").NumberFormat = "0.0E+00"
IC50Cust.Range("AB3").FormulaR1C1 = "=R[-1]C / 1e6"
TC2 = IC50Cust.Range("AB3").Value

TC2 is always coming out as 0.00001, which is not what I need for a filtercriteria at a later stage.

Many thanks for reading.

Yours

Hj

 

[RoryA]

Variables don't have formatting, they are just values. If you need it for an autofilter, you can probably use a string instead:

' Top concentration, assuming 1 Section
Dim TC As String                ' Top Conc
Dim TC1 As Long                 ' the value rather than the string
Dim TC2 As String
If Q1.Range("C1").Value = 1 Then
Q1.Range("B18").FormulaR1C1 = "=LEFT(R[1]C,LEN(R[1]C)-3)"
TC1 = Q1.Range("B18").Value
TC = ">" & Format(TC1 / 1000000, "0.0E+00")
Else
End If      ' end if for there is only 1 section

' Stamp Cust file with TopConc
IC50Cust.Range("AB1").Value = TC
IC50Cust.Range("AB2").Value = TC1
IC50Cust.Range("AB3").NumberFormat = "0.0E+00"
IC50Cust.Range("AB3").FormulaR1C1 = "=R[-1]C / 1e6"
TC2 = IC50Cust.Range("AB3").Text

 

[HankJ]

RoryA
Thank you for your reply.
I'm finding this really frustrating.
I have two columns of data in the sheet I am working on, for compound and enzyme concentrations. They both appear to be in Sci format but in the actual cell the compound is in decimal and the enzyme in Sci format.
My problem is that the filter drop-downs for the compound concentration are in Sci format, 1.0E-05.
I've used range.numberformat = "0.0E+00" to define the format of the cell before running the formula to populate the cell and still it gets decimal rather than sci format...
I have a work around that concatenates to create a string but it would not be too flexible, if for example the concentration was 3.2E-05.

Any thougths/suggestions would be most welcomed.

Hj