Format numbers with VBA Macro

[blelli]

Dears,

I have a spreadsheet where I would like to write a frequency in the cell A1, and the system will insert this value, formated, in the cell A2.
No matter the format I write, the system will always respect the following format "1XX.XX"

Example:
If I write 118.95, the system will write 118.95
If I write 18.95, the system will write 118.95
If I write 1895, the system will write 118.95
If I write 1189, the system will write 118.90

How can I do it?

Thanks a lot

 

[Scott Huish]

In maintaining the same pattern why didn't the last one become 111.89 in the same fashion as adding a 1 before 1895?

 

[igold]

I was looking at this and I believe it is because the first and second digits are both already "1"...

 

[igold]

Code error.

 

[blelli]

Dears,

It's because we are talking about aviation radio's frequencies and the last character should be 0 or 5.

But i've already solved my issue...
Thanks a lot guys

You are the best!

 

[igold]

@blelli,

Out of curiosity what was your solution? Was it VBA or something else. If it was VBA, please post it, I would like to see how it compares to the solution I was about to post..

 

[blelli]

It was not an elegant solution... but it's working perfectly!
thanks a lot!!!

Sub ImputsRadio()


Dim Frequencia As String
Dim FrequenciaArr() As String




Frequencia = Cells(21, 2).value




If InStr(Frequencia, ".") > 0 Then


FrequenciaArr = Split(Frequencia, ".", , vbTextCompare)


FInteiro = FrequenciaArr(0)
FDecimal = FrequenciaArr(1)


If Len(FDecimal) = 1 Then
FDecimal = FDecimal & "0"
Else
FDecimal = FDecimal
End If


If Len(FInteiro) = 2 Then
FInteiro = "1" & FInteiro
Else
FInteiro = FInteiro
End If

Frequencia = FInteiro & "." & FDecimal
Cells(21, 2).value = Frequencia



Else
Numero = Len(Frequencia)


Select Case Numero


Case Is = 5


Frequencia = Left(Frequencia, 3) & "." & Right(Frequencia, 2)
Cells(21, 2).value = Frequencia


Case Is = 3


Frequencia = 1 & Left(Frequencia, 2) & "." & Right(Frequencia, 1) & "0"
Cells(21, 2).value = Frequencia


Case Is = 4


If Right(Frequencia, 1) = "5" Then


Frequencia = 1 & Left(Frequencia, 2) & "." & Right(Frequencia, 2)
Cells(21, 2).value = Frequencia
Else
Frequencia = Left(Frequencia, 3) & "." & Right(Frequencia, 1) & "0"
Cells(21, 2).value = Frequencia
End If


Cells(21, 2).value = Frequencia




End Select






End If
End Sub

 

[igold]

Thanks for sharing. No less elegant than mine...

Sub format6()


    Dim lets(1 To 6)
    Dim nlets(1 To 6)
    Dim x As Single
    Dim i As Integer
    If Len(Cells(1, 1)) = 6 Then
        Cells(2, 1) = Cells(1, 1)
        Exit Sub
    End If
    x = [A1]
    For i = 1 To 6
        lets(i) = Mid(x, i, 1)
    Next
    nlets(1) = 1
    If lets(2) <> 1 Then
        nlets(2) = lets(1)
        nlets(3) = lets(2)
        nlets(4) = "."
        nlets(5) = lets(3)
        nlets(6) = lets(4)
    End If
    If nlets(6) <> "" Then GoTo done
    If lets(5) = "" Then
        nlets(2) = lets(2)
        nlets(3) = lets(3)
        nlets(4) = "."
        nlets(5) = lets(4)
        nlets(6) = 0
        GoTo done
    End If
    If nlets(6) = "" Then nlets(6) = 0
done:
    Cells(2, 1) = Val(nlets(1) & nlets(2) & nlets(3) & nlets(4) & nlets(5) & nlets(6))
    Cells(2, 1).NumberFormat = "0.00"
    
End Sub

 

[Scott Huish]

As a formula, I think this would do that:
=MID(1&SUBSTITUTE(A1,".","")&0,2,5)/100

And as VBA:

Sub test()
Range("A2") = Mid(1 & Replace(Range("A1"), ".", "") & 0, 2, 5) / 100
Range("A2").NumberFormat = "0.00"
End Sub

 

[igold]

@Scott

I was hoping to see something along these lines that was short and sweet but in my tests, both the code and formula fail on 18.95 and 1895.