Using NumberFormat with Offset

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
Hi guys,

sorry again for a simple question but I cant seem to get it to work.

Code:
        If Weekday(rngCel) = 1 Then rngCel.Offset(0, 1).Value = 45
            .NumberFormat = "0.00"

Code:
        If Weekday(rngCel) = 1 Then rngCel.Offset(0, 1).Value = Format(45, "Currency")

I have tried with this code above and I am getting the right look of it but it is saved as text in the cell which is not that helpful .(

how do I need to combine this code with numberformating so I am getting € 45,00 for example.

Thank you

Albert
 
Last edited:

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can do this:

Code:
If Weekday(rngCel) = 1 Then 
    With rngCel.Offset(0, 1)
        .Value = 45
        .NumberFormat = "€ 0.00"
    End With
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
I'm not a code expert but by playing around with the macro recorder I came up with this . . .

Code:
If Weekday(rngCel) = 1 Then rngCel.Offset(0, 1).Value = 45.NumberFormat = "[$€-2] #,##0.00"
 
Last edited:

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
HI Thanks guys for your help!

Chees it works fine.. I sometimes forget about the makro recorder :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,029
Messages
5,526,340
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top