Unable to keep the cell format in vba

advertisements

i try to keep the same cell format in my vba script but i can't.

My variable prixB is always a 'number' and my variable prixA is a standard type. I need to have them in money like they are in the base document.

There's my script!

Thank you for your help

Sub Intersection()

Dim nombre As Integer
Dim tableauDate(1 To 115) As Date
Dim tableauValeur(1 To 115) As String
Dim valeur As Variant
Dim incrementeurForeach As Integer
Dim dateA As Date
Dim dateB As Date
Dim prixA As Currency
Dim prixB As Currency

nombre = 115
incrementeurForeach = 1

For incrementeur = 2 To nombre

dateA = Range("A" & incrementeur).Value
dateB = Range("c" & incrementeur).Value
prixA = Range("B" & incrementeur).Value
prixB = Range("D" & incrementeur).Value

Select Case dateA
    Case Is = dateB
        tableauDate(incrementeur) = dateA

    Case Is > dateB
        tableauDate(incrementeur) = dateA

    Case Else:
        tableauDate(incrementeur) = dateB

End Select

If prixA >= prixB Then
    tableauValeur(incrementeur) = prixA
Else
    tableauValeur(incrementeur) = prixB
End If

Next incrementeur

Sheets.Add After:=ActiveSheet

For Each valeur In tableauDate

Range("A1").Offset(incrementeurForeach, 0).Value = tableauDate(incrementeurForeach)
Range("B1").Offset(incrementeurForeach, 0).Value = tableauValeur(incrementeurForeach)

incrementeurForeach = incrementeurForeach + 1

Next valeur

End Sub


To apply number formatting (and that's all it is in Excel) you can just:

Range("A1").NumberFormat = "$#" or Range("A1").NumberFormat = "$#.##", etc.

You may modify the number format as needed for your specific currency, decimal precision, etc.