Formatting Data with VBA

Notadumbblonde

Board Regular
Joined
Nov 18, 2003
Messages
104
Hey Excels, Got a interesting problem.

I have a spreadsheet that all my staff keys into

I constantly do range value (sorry Lotus Term) copy special value, and then format the numbers.

Is there any way I can code:

1. To make the range I'm extracting numbers!! if currently Strings
2. Make them a predefined format, say 1 decimal point.

Any Help would greatly be appreciated.

I make use conditional formatting, but my users may be cutting and pasting from text files.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
Something like this would probably do....

Amend the range & number format to what suits you

Code:
Sub nonblondeformat()
For Each cell In Range("A1:A10").Cells
With cell
.Value = .Value * 1
.NumberFormat = "#,##0_);(#,##0)"
End With
Next cell
End Sub

Hope it helps
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
give us an example of the values that are not numeric then... I assumed (obviously incorrectly) that you had numbers formatted as text which you wanted to convert to numbers.... hence the line

Code:
.Value = .Value * 1
 

Notadumbblonde

Board Regular
Joined
Nov 18, 2003
Messages
104

ADVERTISEMENT

any Alpha charater Say "A" does not update.

I receive a type mismatch error.

thanks
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
So what do you want to do with a value of A ? Anything at all... or leave them as they are... it's not very clrear from your posts.

To make the range I'm extracting numbers!! if currently Strings
 

Notadumbblonde

Board Regular
Joined
Nov 18, 2003
Messages
104

ADVERTISEMENT

Sorry,

Let me start againg.

if the values are formulas, I would like to convert them to Values with fixed decimal place of 1

if the values are characters (say alpha or symbols), to highlight them as they are and cannot be changed.

if the values are numbers (values), jus to have them at a fixed decimal place or 1.


thanks again, it's been a long project of doing week after week.
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
Code:
Sub nonblondeformat()
For Each Cell In Range("A1:A20").Cells
 If IsNumeric(Cell.Value) Then
  With Cell
  .Value = .Value * 1
  .NumberFormat = "#,##0.0;(#,##0.0)"
  End With
 Else
  With Cell.Interior
  .ColorIndex = 6
  .Pattern = xlSolid
  End With
 End If
Next Cell
End Sub

Should do what you've described - highlights cells that are not numeric as a solid yellow background. Formulas become their values. etc
 

Notadumbblonde

Board Regular
Joined
Nov 18, 2003
Messages
104
THANKS!!!! it worked like a charm...but what had someone with a broken formula that bypassed your code

"#NAME?" how do you account for something like this?
 

Notadumbblonde

Board Regular
Joined
Nov 18, 2003
Messages
104
THANKS!!!! it worked like a charm...but what had someone with a broken formula that bypassed your code

"#NAME?" how do you account for something like this?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,323
Messages
5,769,454
Members
425,549
Latest member
Bartekelese

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top