Array numberformat: need to change

[HankJ]

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:

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

and I get a Type mismatch error

WrdArray(i).NumberFormat = "General"

and I get invalid qualifier...

There must be a way.

Many thanks

Hankj

 

[oldbrewer]

maybe put the 1.0E-6 into a helper cell that is formatted to 6 dec places, then copy and paste that cell back into your original cell ?

 

[HankJ]

maybe put the 1.0E-6 into a helper cell that is formatted to 6 dec places, then copy and paste that cell back into your original cell ?

Hi OldBrewer
Thank you for this.
I was hoping to do this step without copying and pasting into a cell. I am still a novice at this and I have columns of deconstructed dose responses doing just this.

Yours

Hankj

 

[Peter_SSs]

Could we see the code of how WrdArray is declared and how it is filled from the worksheet?

 

[HankJ]

Could we see the code of how WrdArray is declared and how it is filled from the worksheet?

The aim is convert a string from M units to µM and the format to be general instead of Sci

The string in A1 is:

1.0E-4,1.0E-5,1.0E-6,1.0E-7 <colgroup><col width="174"></colgroup><tbody> </tbody>

<colgroup><col width="174"><col width="64"></colgroup><tbody>
</tbody>

and B1:

M

<colgroup><col width="64"></colgroup><tbody>
</tbody>

and the code... is underdevelopment!!

Sub Break_String_Test()
    Dim WrdArray() As String
    Dim text_string As String
    text_string = Range("A1").Value
    WrdArray() = Split(text_string, ",")
      For i = LBound(WrdArray) To UBound(WrdArray)
      strg = strg & vbNewLine & "Part No. " & i & " - " & WrdArray(i)
      Debug.Print WrdArray(i)
      Cells(1, 3 + i).Value = WrdArray(i)
      If Cells(0 + 1, 2).Value = "M" Then
      Cells(1, 3 + i).Value = Cells(1, 3 + i).Value * 1000000
      WrdArray(i).NumberFormat = "General"
      Debug.Print WrdArray(i)
      WrdArray(i) = Join(WrdArray(i), ",")
      Debug.Print WrdArray(i)
      Else
      End If
      Next i
    'MsgBox strg
    End Sub

Many thanks Peter

Hankj

 

[oldbrewer]

1.0E-4,1.0E-5,1.0E-6,1.0E-7	27
0.0001000
0.0000100			this macro produced the 4 numbers below
0.0000010
0.0000001			easy to automate the "print out" part
			Sub Macro4()
			'
			' Macro4 Macro
			' Macro recorded 12/10/2017 by bob
			'
			'
			Dim holder(20)
			For j = 1 To Cells(1, 2)
			If Mid(Cells(1, 1), j, 1) = "," Then Sum = Sum + 1: holder(Sum) = j - 1
			Next j
			Cells(5, 1) = Mid(Cells(1, 1), 1, holder(1))
			Cells(6, 1) = Mid(Cells(1, 1), holder(1) + 2, holder(2) - holder(1) - 1)
			Cells(7, 1) = Mid(Cells(1, 1), holder(2) + 2, holder(3) - holder(2) - 1)
			Cells(8, 1) = Mid(Cells(1, 1), holder(3) + 2, 15)
			End Sub
			A5 down are formatted to 7 decimal places

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Does this macro do what you want...

Sub Break_String_Test()
  Dim X As Long, Parts() As String
  Parts = Split(Range("A1").Value, ",")
  For X = 0 To UBound(Parts)
    Parts(X) = Format(Parts(X), "#.###############")
  Next
  Range("A1").Value = Join(Parts, ",")
End Sub

 

[HankJ]

Does this macro do what you want...

Sub Break_String_Test()
  Dim X As Long, Parts() As String
  Parts = Split(Range("A1").Value, ",")
  For X = 0 To UBound(Parts)
    Parts(X) = Format(Parts(X), "#.###############")
  Next
  Range("A1").Value = Join(Parts, ",")
End Sub

Hi Rick

It really is. I managed to come up with the same code myself 20 minutes ago, the penultimate line is the one really new to me and so was just what I wanted.

You've confirmed I was going in the right direction.

Many thanks for your time and help.

Yours

Hank

 

[Peter_SSs]

I managed to come up with the same code myself 20 minutes ago,

May not be important to you but to match the format stated in post 1, the Format pattern would need to be "0.###############"

If interested, and you have Excel 2016 through Office 365, you could also try this one-liner which should do the job for 2-20 joined items in A1.

Sub ConvertFormat()
  Range("A1").Value = Evaluate("textjoin("","",true,iferror(mid(substitute(A1,"","",rept("" "",200)),{" & Join(Application.Transpose(Evaluate("row(1:20)")), ",") & "}*200-199,200)+0,""""))")
End Sub

 

[HankJ]

May not be important to you but to match the format stated in post 1, the Format pattern would need to be "0.###############"

If interested, and you have Excel 2016 through Office 365, you could also try this one-liner which should do the job for 2-20 joined items in A1.

Sub ConvertFormat()
  Range("A1").Value = Evaluate("textjoin("","",true,iferror(mid(substitute(A1,"","",rept("" "",200)),{" & Join(Application.Transpose(Evaluate("row(1:20)")), ",") & "}*200-199,200)+0,""""))")
End Sub

Hello Peter
Thank you for this. I tried it and got a "#NAME?" in A1.

I hope sometime later to post what I have done and then hopefully get a review.

I find I can spend hours getting something work, which is fine but I am pretty sure that there are better ways to do things.

Yours in code

Hank