Array numberformat: need to change

H

HankJ

Board Regular
Joined
Mar 5, 2016
Messages
89
Hi
I'm learning about splitting and joining.
I have a cell, which is a dose response of concentrations in scientific notation.
I have got to the step where my array is filled with each concentration.
I now need to change the format from 1.0E-6 to 0.000001
I've attempted:

HTML: 
WrdArray(i) = Format(WrdArray(i), "General")

and I get a Type mismatch error

HTML: 
WrdArray(i).NumberFormat = "General"

and I get invalid qualifier...

There must be a way.

Many thanks

Hankj
 
Sort by date Sort by votes

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
HankJ said:
We have Excel 2010, so I was hoping that would be enough...
Click to expand...
No, as stated, the requirement is Excel 2016. :)
But it doesn't matter does it - you said you already had a working code, or Rick's?

BTW, I think in Rick's code, this line could also be used?
Code: 
Parts(X) = Val(Parts(X))
 
Last edited:
Upvote 0
Hello

As I mentioned earlier I would to get a review on my developed code.

The aim is convert this:

1.0E-4,1.0E-5,1.0E-6,1.0E-7M
1.0E-4,1.0E-5,1.0E-6,1.0E-7
M
1.0E-4,1.0E-5,1.0E-6,1.0E-7M
1.0E-4,1.0E-5,1.0E-6,1.0E-7
M
1.0E-4,1.0E-5,1.0E-6,1.0E-7,1.0E-8M

<colgroup><col><col></colgroup><tbody>
</tbody>


into this:


100,10,1,0.1µM
100,10,1,0.1
µM
100,10,1,0.1µM
100,10,1,0.1µM
100,10,1,0.1,0.01µM

<colgroup><col><col></colgroup><tbody>
</tbody>

This code does it:

Code: 
Sub Break_String_Test()
    
    Dim WrdArray() As String
    Dim text_string As String
    
    Dim rCell As Range
    Dim rRng As Range
    Dim LR As Long ' length
    LR = Range("A50000").End(xlUp).Row

    Set rRng = Sheet1.Range("A1:A" & LR)

    For Each rCell In rRng.Rows
    If rCell.Offset(0, 1).Value <> "µM" Then
        text_string = rCell.Value
        WrdArray() = Split(text_string, ",")
        
        For i = LBound(WrdArray) To UBound(WrdArray)
        If rCell.Offset(0, 1).Value = "M" Then
            WrdArray(i) = WrdArray(i) * 1000000
            Else
        End If
        Next i
        
    text_string = Join(WrdArray, ",")
    rCell = text_string
    rCell.Offset(0, 1).Value = "µM"
        Else
        End If
           
       Next rCell

    End Sub

The line text_string = Join(WrdArray, ",") seems to automatically use a general format, which saves that little knot.

This code will actually replace lines and lines of codes in which I used left, mid, right to count and locate commas to then breakdown this type of string.

Many thanks to those who have helped to educate me.

Hankj
 
Upvote 0
HankJ said:
This code does it:

Code: 
Sub Break_String_Test()
    
    Dim WrdArray() As String
    Dim text_string As String
    
    Dim rCell As Range
    Dim rRng As Range
    Dim LR As Long ' length
    LR = Range("A50000").End(xlUp).Row

    Set rRng = Sheet1.Range("A1:A" & LR)

    For Each rCell In rRng.Rows
    If rCell.Offset(0, 1).Value <> "µM" Then
        text_string = rCell.Value
        WrdArray() = Split(text_string, ",")
        
        For i = LBound(WrdArray) To UBound(WrdArray)
        If rCell.Offset(0, 1).Value = "M" Then
            WrdArray(i) = WrdArray(i) * 1000000
            Else
        End If
        Next i
        
    text_string = Join(WrdArray, ",")
    rCell = text_string
    rCell.Offset(0, 1).Value = "µM"
        Else
        End If
           
       Next rCell

    End Sub
Click to expand...
This shorter macro appears to produce the same results as the code above does...
Code: 
[table="width: 500"]
[tr]
	[td]Sub Break_String_Test()
  Dim X As Long, Cell As Range, Rng As Range, Parts() As String
  Set Rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For Each Cell In Rng
    Parts = Split(Cell.Value, ",")
    For X = 0 To UBound(Parts)
      Parts(X) = 0 + Format(1000000 * Parts(X), "0.###############")
    Next
    Cell.Value = Join(Parts, ",")
  Next
  Rng.Offset(, 1) = "µM"
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Hank, all your samples in post 14 have an "M" in the next column. I'm guessing that in fact some might have "µM"? Your existing code just bypasses those cells but if such a row contained "1.0E-4,1.0E-5,1.0E-6,1.0E-7", shouldn't it be converted into "0.0001,0.00001,0.000001,0.0000001" along the lines of your example in post 1?

If so, could I suggest this modification of Rick's code.
If you need further help, please provide a sample of data that shows the full variation that can occur with your data as well as the expected results.
Rich (BB code): 
Sub Break_String_Test_v2()
  Dim X As Long, Cell As Range, Rng As Range, Parts() As String
  Set Rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For Each Cell In Rng
    Parts = Split(Cell.Value, ",")
    For X = 0 To UBound(Parts)
      Parts(X) = IIf(Cell.Offset(, 1).Value = "M", 1000000, 1) * Parts(X)
    Next
    Cell.Value = Join(Parts, ",")
  Next
  Rng.Offset(, 1).Value = "µM"
End Sub
 
Last edited:
Upvote 0
Peter_SSs said:
Hank, all your samples in post 14 have an "M" in the next column. I'm guessing that in fact some might have "µM"? Your existing code just bypasses those cells but if such a row contained "1.0E-4,1.0E-5,1.0E-6,1.0E-7", shouldn't it be converted into "0.0001,0.00001,0.000001,0.0000001" along the lines of your example in post 1?

If so, could I suggest this modification of Rick's code.
If you need further help, please provide a sample of data that shows the full variation that can occur with your data as well as the expected results.
Rich (BB code): 
Sub Break_String_Test_v2()
  Dim X As Long, Cell As Range, Rng As Range, Parts() As String
  Set Rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For Each Cell In Rng
    Parts = Split(Cell.Value, ",")
    For X = 0 To UBound(Parts)
      Parts(X) = IIf(Cell.Offset(, 1).Value = "M", 1000000, 1) * Parts(X)
    Next
    Cell.Value = Join(Parts, ",")
  Next
  Rng.Offset(, 1).Value = "µM"
End Sub
Click to expand...

Hi Rick and Peter,

You're both correct with the two points you've made in the last posts. I suppose I started slowly, just trying to figure out how to change from scientific to decimal.

The system I work in has units of either µM or µg/ml and incoming worksheets up until recently were always decimal though the units could be non-standard. I worked out some very long winded method to deconstruct dose response concentrations for non standard unit, do the necessary maths and then join them all up through concatenation. Recently scientific dose responses started to appear and my code could not cope with them and breaking them down again just seemed too time consuming, so I can here and asked the questions.

So items like these:

1.0E-4,3.162E-5,1.0E-5,3.1625E-6M
1.0E-4,3.162E-5,1.0E-5,3.1625E-6M
1.0E-4,3.162E-5,1.0E-5,3.1625E-6M
1.0E-1,1.0E-2,1.0E-3,1.0E-4mM
1.0E4,1.0E3,1.0E2,1.0E1nM
10,1,0.1,0.01mM
1.0E-4,1.0E-5,1.0E-6,1.0E-7g/ml
1.0E-1,1.0E-2,1.0E-3,1.0E-4mg/ml
1.0E-4,3.162E-5,1.0E-5,3.1625E-6mg/ml
1.0E-4,3.162E-5,1.0E-5,3.1625E-6mg/ml
1.0E4,1.0E3,1.0E2,1.0E1ng/ml

<tbody>
</tbody>

could be changed to this:

100,31.62,10,3.1625µM
100,31.62,10,3.1625µM
100,31.62,10,3.1625µM
100,10,1,0.1µM
10,1,0.1,0.01µM
10000,1000,100,10µM
100,10,1,0.1µg/ml
100,10,1,0.1µg/ml
0.1,0.03162,0.01,0.0031625µg/ml
0.1,0.03162,0.01,0.0031625µg/ml
10,1,0.1,0.01µg/ml

<tbody>
</tbody>

with this:

Code: 
Sub Break_String_Test()
    
    Dim WrdArray() As String
    Dim text_string As String
    
    ' code to deconstruct concentration range when units are not standard µM or µg/ml
    ' A Johnston with the helo of Rick and Peter
    ' https://www.mrexcel.com/forum/excel-questions/1026709-array-numberformat-need-change-2.html
    
    Dim i As Long
        
    Dim rCell As Range
    Dim rRng As Range
    Dim LR As Long ' length
    LR = Range("A50000").End(xlUp).Row

    Set rRng = Sheet1.Range("A1:A" & LR)

    For Each rCell In rRng.Rows
    If rCell.Offset(0, 1).Value <> "µM" Or rCell.Offset(0, 1).Value <> "µg/ml" Then         ' units have to be either µM or µg/ml
        text_string = rCell.Value                                                           ' convert cell value to string, needed?
        WrdArray() = Split(text_string, ",")                                                ' Spilt divides string with "," as the separator
        
        ' Molar concentrations to µM
        For i = LBound(WrdArray) To UBound(WrdArray)
        If rCell.Offset(0, 1).Value = "M" Then
            WrdArray(i) = WrdArray(i) * 1000000
        ElseIf rCell.Offset(0, 1).Value = "mM" Then
            WrdArray(i) = WrdArray(i) * 1000
        ElseIf rCell.Offset(0, 1).Value = "nM" Then
            WrdArray(i) = WrdArray(i) / 1000
            
        ' g/ml concentrations to µg/ml
        ElseIf rCell.Offset(0, 1).Value = "g/ml" Then
            WrdArray(i) = WrdArray(i) * 1000000
        ElseIf rCell.Offset(0, 1).Value = "mg/ml" Then
            WrdArray(i) = WrdArray(i) * 1000
        ElseIf rCell.Offset(0, 1).Value = "ng/ml" Then
            WrdArray(i) = WrdArray(i) / 1000
            Else
        End If
        Next i
       
        
    text_string = Join(WrdArray, ",")   ' Joins the contents of the array to the String
    rCell = text_string                 ' text string replaces the contents of the concentration range
    
    ' label units either µM or mg/ml
    If Right(rCell.Offset(0, 1), 1) = "M" Then
    rCell.Offset(0, 1).Value = "µM"
    Else
    rCell.Offset(0, 1).Value = "µg/ml"
    End If
        Else
        End If
           
       Next rCell

    End Sub

Needs more testing as I've notice the decimal mM line has not been multiplied by 1000 though it has converted the units to µM.

Hope all is good with you.

Thank you for your help.

Hankj
 
Upvote 0
It seems that there are more types of units to be converted than we previously knew about. :)
Below, I have also assumed that it may be possible that some rows already Have the correct units (green rows) so simply need their values converted from scientific notation.
If there are even more types of units you should be able to just add in more 'Case' sections.

Code: 
Sub Break_String_Test_v3()
  Dim a As Variant, Parts As Variant
  Dim i As Long, j As Long, Exponent As Long
  Dim NewUnits As String
  
  a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    Select Case a(i, 2)
      Case "M"
        Exponent = 6: NewUnits = "µM"
      Case "mM"
        Exponent = 3: NewUnits = "µM"
      Case "nM"
        Exponent = -3: NewUnits = "µM"
      Case "µM"
        Exponent = 0: NewUnits = "µM"
      Case "g/ml"
        Exponent = 6: NewUnits = "µg/ml"
      Case "mg/ml"
        Exponent = 3: NewUnits = "µg/ml"
      Case "ng/ml"
        Exponent = -3: NewUnits = "µg/ml"
      Case "µg/ml"
        Exponent = 0: NewUnits = "µg/ml"
    End Select
    Parts = Split(a(i, 1), ",")
    For j = 0 To UBound(Parts)
      Parts(j) = 10 ^ Exponent * Parts(j)
    Next j
    a(i, 1) = Join(Parts, ",")
    a(i, 2) = NewUnits
  Next i
  With Range("C1:D1").Resize(UBound(a))
    .Value = a
    .Columns.AutoFit
  End With
End Sub

Sample data in A:B, code results in C:D.


Book1
ABCD
11.0E-4,3.162E-5,1.0E-5,3.1625E-6M100,31.62,10,3.1625M
21.0E-4,3.162E-5,1.0E-5,3.1625E-6M100,31.62,10,3.1625M
31.0E-4,3.162E-5,1.0E-5,3.1625E-6M100,31.62,10,3.1625M
41.0E-1,1.0E-2,1.0E-3,1.0E-4mM100,10,1,0.1M
51.0E4,1.0E3,1.0E2,1.0E1nM10,1,0.1,0.01M
610,1,0.1,0.01mM10000,1000,100,10M
71.0E-4,1.0E-5,1.0E-6,1.0E-7g/ml100,10,1,0.1g/ml
81.0E-1,1.0E-2,1.0E-3,1.0E-4mg/ml100,10,1,0.1g/ml
91.0E-4,3.162E-5,1.0E-5,3.1625E-6mg/ml0.1,0.03162,0.01,0.0031625g/ml
101.0E-4,3.162E-5,1.0E-5,3.1625E-6mg/ml0.1,0.03162,0.01,0.0031625g/ml
111.0E4,1.0E3,1.0E2,1.0E1ng/ml10,1,0.1,0.01g/ml
121.0E4,1.0E3,1.0E2,1.0E1M10000,1000,100,10M
131.0E4,1.0E3,1.0E2,1.0E1g/ml10000,1000,100,10g/ml
Sheet2
 
Last edited:
Upvote 0
Peter_SSs said:
It seems that there are more types of units to be converted than we previously knew about. :)
Below, I have also assumed that it may be possible that some rows already Have the correct units (green rows) so simply need their values converted from scientific notation.
If there are even more types of units you should be able to just add in more 'Case' sections.



ABCD
11.0E-4,3.162E-5,1.0E-5,3.1625E-6M100,31.62,10,3.1625µM
21.0E-4,3.162E-5,1.0E-5,3.1625E-6M100,31.62,10,3.1625µM
31.0E-4,3.162E-5,1.0E-5,3.1625E-6M100,31.62,10,3.1625µM
41.0E-1,1.0E-2,1.0E-3,1.0E-4mM100,10,1,0.1µM
51.0E4,1.0E3,1.0E2,1.0E1nM10,1,0.1,0.01µM
610,1,0.1,0.01mM10000,1000,100,10µM
71.0E-4,1.0E-5,1.0E-6,1.0E-7g/ml100,10,1,0.1µg/ml
81.0E-1,1.0E-2,1.0E-3,1.0E-4mg/ml100,10,1,0.1µg/ml
91.0E-4,3.162E-5,1.0E-5,3.1625E-6mg/ml0.1,0.03162,0.01,0.0031625µg/ml
101.0E-4,3.162E-5,1.0E-5,3.1625E-6mg/ml0.1,0.03162,0.01,0.0031625µg/ml
111.0E4,1.0E3,1.0E2,1.0E1ng/ml10,1,0.1,0.01µg/ml
121.0E4,1.0E3,1.0E2,1.0E1µM10000,1000,100,10µM
131.0E4,1.0E3,1.0E2,1.0E1µg/ml10000,1000,100,10µg/ml

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​
Click to expand...

Hello Peter
Thank you for your reply and updated code.
I have to hope that the system does not throw out a mix of scientific concentrations and non M units as this can be a source of confusion.... but it is best to be prepared.

I ran your code with the following tests:

100,10,1,0.1mg/ml
100,10,1,0.1g/ml
100,10,1,0.1ng/ml

<colgroup><col><col></colgroup><tbody>
</tbody>

and got:

1.00E+17
µg/ml
100,000,000,100,000,000,000,000,000,000
µg/ml
0.1,0.01,0.001,0.0001
µg/ml

<colgroup><col><col></colgroup><tbody>
</tbody>


Which is odd as with Debug.print all the numbers seemed to be converted correctly.

100000,10000,1000,100
100000000,10000000,1000000,100000
0.1,0.01,0.001,0.0001

I was going to ask a question on the code but when I came to copy it I saw how the exponent = x works, that's nice.

Yours, with thanks again

Hank
 
Upvote 0
HankJ said:
and got:

1.00E+17
µg/ml
100,000,000,100,000,000,000,000,000,000
µg/ml
0.1,0.01,0.001,0.0001
µg/ml

<colgroup><col><col></colgroup><tbody>
</tbody>
Click to expand...
That will be due to Excel trying to decide itself what format to use. Try adding in this line of code where shown.
Rich (BB code): 
  With Range("C1:D1").Resize(UBound(a))
    .NumberFormat = "@"
    .Value = a
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Multi Dim array printing specific value to range depending on value in Col A
Replies
3
Views
429
Alex Blakenburg
A
J
  • Question
Another Find/Replace Question - Excel(vba) to Word
Replies
0
Views
411
jagrenet
J
N
  • Locked
  • Question
VBA code doesn't work
Replies
3
Views
527
Fluff
Fluff
A
  • Question
VBA scripting: looping through slicers and 1 item in each
Replies
0
Views
1K
AcidKid
A
C
  • Question
Replacing PowerPoint Input using Find and Replace Arrays from Excel through VBA
Replies
0
Views
1K
ckcs
C

Forum statistics

Threads
1,216,456
Messages
6,130,743
Members
449,588
Latest member
accountant606

Share this page

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
Back
Top