Excel VBA Formula Help

A

Aaron

Hi Experts,

With Range("d10,d11,k10,n10,p10,t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

This is part of a button driven macro.

The problem is the exit sub part is causing the rest of the macro to not be run (not shown)

I have tried to place another macro call after "Then" but I cant get it to work.

It just moans about the ".NumberFormat = "0.0"" part.

I just need it to protect the worksheet if it is going to exit sub because at the end of this macros code I protect the sheet. When it exits, It doesnt ever get to run the code part that protects the sheet at the end.

TIA,

Aaron.
 
D

Don Guillett

Hi Experts,

With Range("d10,d11,k10,n10,p10,t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

This is part of a button driven macro.

The problem is the exit sub part is causing the rest of the macro to not be run (not shown)

I have tried to place another macro call after "Then" but I cant get it to work.

It just moans about the ".NumberFormat = "0.0"" part.

I just need it to protect the worksheet if it is going to exit sub because at the end of this macros code I protect the sheet. When it exits, It doesnt ever get to run the code part that protects the sheet at the end.

TIA,

Aaron.
Click to expand...

You have




Hi Experts,

With Range("d10,d11,k10,n10,p10,t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

This is part of a button driven macro.

The problem is the exit sub part is causing the rest of the macro to not be run (not shown)

I have tried to place another macro call after "Then" but I cant get it to work.

It just moans about the ".NumberFormat = "0.0"" part.

I just need it to protect the worksheet if it is going to exit sub because at the end of this macros code I protect the sheet. When it exits, It doesnt ever get to run the code part that protects the sheet at the end.

TIA,

Aaron.
Click to expand...



Hi Experts,

With Range("d10,d11,k10,n10,p10,t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

This is part of a button driven macro.

The problem is the exit sub part is causing the rest of the macro to not be run (not shown)

I have tried to place another macro call after "Then" but I cant get it to work.

It just moans about the ".NumberFormat = "0.0"" part.

I just need it to protect the worksheet if it is going to exit sub because at the end of this macros code I protect the sheet. When it exits, It doesnt ever get to run the code part that protects the sheet at the end.

TIA,

Aaron.
Click to expand...



Hi Experts,

With Range("d10,d11,k10,n10,p10,t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

This is part of a button driven macro.

The problem is the exit sub part is causing the rest of the macro to not be run (not shown)

I have tried to place another macro call after "Then" but I cant get it to work.

It just moans about the ".NumberFormat = "0.0"" part.

I just need it to protect the worksheet if it is going to exit sub because at the end of this macros code I protect the sheet. When it exits, It doesnt ever get to run the code part that protects the sheet at the end.

TIA,

Aaron.
Click to expand...



Hi Experts,

With Range("d10,d11,k10,n10,p10,t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

This is part of a button driven macro.

The problem is the exit sub part is causing the rest of the macro to not be run (not shown)

I have tried to place another macro call after "Then" but I cant get it to work.

It just moans about the ".NumberFormat = "0.0"" part.

I just need it to protect the worksheet if it is going to exit sub because at the end of this macros code I protect the sheet. When it exits, It doesnt ever get to run the code part that protects the sheet at the end.

TIA,

Aaron.
Click to expand...

You are asking excel to leave
If you want the rest you need an additional IF
Tell us what you want?

With Range("d10,d11,k10,n10,p10,​t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub



.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With
 
A

Aaron

Hi Experts,

With Range("d10,d11,k10,n10,p10,t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

This is part of a button driven macro.

The problem is the exit sub part is causing the rest of the macro to not be run (not shown)

I have tried to place another macro call after "Then" but I cant get it to work.

It just moans about the ".NumberFormat = "0.0"" part.

I just need it to protect the worksheet if it is going to exit sub because at the end of this macros code I protect the sheet. When it exits, It doesnt ever get to run the code part that protects the sheet at the end.

TIA,

Aaron.
Click to expand...

I solved this myself.

After a weekend break from it I saw the logic.

With Range("d10,d11,k10,n10,p10,​t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then sheets("Extended").Protect
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

Thanks anyway.
 
D

Don Guillett

Hi Experts,

With Range("d10,d11,k10,n10,p10,t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

This is part of a button driven macro.

The problem is the exit sub part is causing the rest of the macro to not be run (not shown)

I have tried to place another macro call after "Then" but I cant get it to work.

It just moans about the ".NumberFormat = "0.0"" part.

I just need it to protect the worksheet if it is going to exit sub because at the end of this macros code I protect the sheet. When it exits, It doesnt ever get to run the code part that protects the sheet at the end.

TIA,

Aaron.
Click to expand...

You still have and end if without an if
 
G

GS

Aaron wrote :
I solved this myself.

After a weekend break from it I saw the logic.

With Range("d10,d11,k10,n10,p10,​t11,e12,v10")
If .NumberFormat = "0" Or .NumberFormat = "General" Then
sheets("Extended").Protect If .NumberFormat = "0" Or .NumberFormat =
"General" Then Exit Sub .NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With

Thanks anyway.
Click to expand...

This doesn't make any sense (or logic)! Why all the redundancy? Why
test NumberFormat 2x? If you're trying to correct/revise a NumberFormat
then why not just assign the desired format?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Increase & Decrease Decimal Place 6
VBA Coding Help for Beginner 0
Word macro that can call cells out of Excel for a find and replace 0
Custom Formatting 2
Snag in invoice generator. How to change the "series" 5
Make VBA ComboBox_Change work in 3 sheets 1
Date Stamp with protection 6
Help combining formatting in macro functions 2

Top