Setting Numberformat in If statement

[ArnMan]

Hello, I have a quick question. I have 2 cells I would like to use if elseif to set the numberformat in one cell based on a value in another cell. Here is what I have so far, which isnt doing anything for me.

If Range("J5").Value = "0" Then
      Range("LowCB2").NumberFormat = "0"
      Range("LowCB2").Value = "=D9-M9"
     ElseIf Range("J5").Value = "1" Then
        Range("LowCB2").NumberFormat = "0.0"
        Range("LowCB2").Value = "=D9-M9"
     ElseIf Range("J5").Value = "2" Then
        Range("LowCB2").NumberFormat = "0.00"
        Range("LowCB2").Value = "=D9-M9"
     ElseIf Range("J5").Value = "3" Then
        Range("LowCB2").NumberFormat = "0.000"
        Range("LowCB2").Value = "=D9-M9"
        
         End If

If I just put
LowCB2.Numberformat = "0.00"
LowCB2.Value = "=D9-M9"

in with the rest of the code it works, but if I try to use the if statement neither one of those LowCB2's show up.

Any help would be greatly appreciated

 

[dmt32]

Hi,
untested but see if this does what you want

Dim Formats As Variant
Dim v As Integer
         
 Formats = Array("0", "0.0", "0.00", "0.000")
 v = Val(Range("J5").Text)
 
 With Range("LowCB2")
        .NumberFormat = Formats(v)
        .Formula = "=D9-M9"
 End With

As your ranges are unqualified, I assume that code is run when sheet it applies to is active.

Dave

 

[Rick Rothstein]

Instead of what you posted, give this a try...

[table="width: 500"]
[tr]
	[td]Range("LowCB2").NumberFormat = "0" & IIf(Range("J5").Value, ".", "") & String(Range("J5").Value, "0")
Range("LowCB2").Formula = "=D9-M9"[/td]
[/tr]
[/table]

 

[ArnMan]

Instead of what you posted, give this a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Range("LowCB2").NumberFormat = "0" & IIf(Range("J5").Value, ".", "") & String(Range("J5").Value, "0")
Range("LowCB2").Formula = "=D9-M9"[/TD]
[/TR]
</tbody>[/TABLE]

Hello Thank you for answering I copy and pasted your code, and I get the error Method 'Range' of object'_WorkSheet' failed
Im still pretty new at vba. I used debug and it highlighted the first line of code.

So I wasnt sure how to fix it.

 

[ArnMan]

Hello Thank you for answering I copy and pasted your code, and I get the error Method 'Range' of object'_WorkSheet' failed
Im still pretty new at vba. I used debug and it highlighted the first line of code.

So I wasnt sure how to fix it.

Well I was able to remove the error by removing the Range("") from LowCB2 I guess because I had already previously Set LowCB2 as a range. Im know sure. It seems to work now I am trying to figure out how to setup the first line to change on different inputs from J5 (0,1,2,3,etc)

 

[ArnMan]

It seems to automatically apply the number format right off the bat, without any input from J5

 

[ArnMan]

Hello Thank you for answering pretty quick
I tried your code. I did have to remove the Range("") from LowCB2 to remove the error. I guess because I already had it Set as a range previously in the Sub.
Dim LowCB2 As Range
Set LowCB2 = Sheet1.Range("I9")

When I change J5 value, I9 doesnt change at all but still works if I just have LowCB2.NumberFormat = "0.0" all by itself

 

[ArnMan]

That last post was for dmt32 forgot to quote

Hi,
untested but see if this does what you want

Dim Formats As Variant
Dim v As Integer
         
 Formats = Array("0", "0.0", "0.00", "0.000")
 v = Val(Range("J5").Text)
 
 With Range("LowCB2")
        .NumberFormat = Formats(v)
        .Formula = "=D9-M9"
 End With

As your ranges are unqualified, I assume that code is run when sheet it applies to is active.

Dave

 

[dmt32]

Hello Thank you for answering pretty quick
I tried your code. I did have to remove the Range("") from LowCB2 to remove the error. I guess because I already had it Set as a range previously in the Sub.
Dim LowCB2 As Range
Set LowCB2 = Sheet1.Range("I9")

It would have been helpful if you had shared that information in your first post

try this update

Dim Formats As Variant
Dim v As Integer
Dim LowCB2 As Range


Set LowCB2 = Sheet1.Range("I9")
         
 Formats = Array("0", "0.0", "0.00", "0.000")
 v = Val(Range("J5").Text)
 
 With LowCB2
        .NumberFormat = Formats(v)
        .Formula = "=D9-M9"
 End With

Dave

 

[Rick Rothstein]

Hello Thank you for answering pretty quick
I tried your code. I did have to remove the Range("") from LowCB2 to remove the error. I guess because I already had it Set as a range previously in the Sub.
Dim LowCB2 As Range
Set LowCB2 = Sheet1.Range("I9")

I agree with Dave... it would have been nice had you shared this with us originally. What you wrote originally, Range("LowCB2"), would have been correct if you had assigned a Defined Name from Excel's Ribbon (Formula tab, Defined Names panel) to a range on the worksheet, so there was no way for us to know your original attempt at writing the range reference was not correct for what you were doing. Anyway, here is my code snippet modified to properly define LowCB2...

[table="width: 500"]
[tr]
	[td]Dim LowCB2 As Range
Set LowCB2 = Sheet1.Range("I9")
LowCB2.NumberFormat = "0" & IIf(Range("J5").Value, ".", "") & String(Range("J5").Value, "0")
LowCB2.Formula = "=D9-M9"[/td]
[/tr]
[/table]