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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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
Back
Top