Macro to change numbers after decimal point

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
Hi would anyone have a macro that would randomly change the numbers after a decimal point,
for a worksheet called data and would apply to range c2:c20,
this is what column c looks like
2.PNG
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
How about this?

VBA Code:
Sub changeDec()
Dim ws As Worksheet:    Set ws = Sheets("data")
Dim r As Range:         Set r = ws.Range("C2:C20")
Dim AR() As Variant:    AR = r.Value2

For i = LBound(AR) To UBound(AR)
    AR(i, 1) = Int(AR(i, 1)) + Rnd()
Next i
    
r.Value2 = AR
End Sub
 

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
How about this?

VBA Code:
Sub changeDec()
Dim ws As Worksheet:    Set ws = Sheets("data")
Dim r As Range:         Set r = ws.Range("C2:C20")
Dim AR() As Variant:    AR = r.Value2

For i = LBound(AR) To UBound(AR)
    AR(i, 1) = Int(AR(i, 1)) + Rnd()
Next i
   
r.Value2 = AR
End Sub
sorry got it a bit wrong numbers are in percentage.
Could you adjust code for that please.
2.PNG
 

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
also is it possible if cell is blank it would remain blank?
2.PNG
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
121
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello Please Try this
VBA Code:
Sub random_number()
    
    Dim i As Double
    Dim r As Double
    Dim cell As Range
    
    For Each cell In Range("c2:c20").Cells
        r = cell.Value
        i = Int(cell.Value)
        r = r - i
        r = Rnd(r)
        cell.Value = i + r
    
    Next cell


End Sub
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
This should do it.

VBA Code:
Sub changeDec()
Dim ws As Worksheet:    Set ws = Sheets("data")
Dim r As Range:         Set r = ws.Range("C2:C20")
Dim AR() As Variant:    AR = r.Value2

For i = LBound(AR) To UBound(AR)
    AR(i, 1) = (Int(AR(i, 1) * 100) + Rnd()) / 100
Next i
    
r.Value2 = AR
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Borrowing lrobbo's code
VBA Code:
Sub changeDec()
Dim ws As Worksheet:    Set ws = Sheets("data")
Dim r As Range:         Set r = ws.Range("C2:C20")
Dim i As Long
Dim AR() As Variant:    AR = r.Value2
Randomize
For i = LBound(AR) To UBound(AR)
   If AR(i, 1) <> "" Then
      AR(i, 1) = (Int(AR(i, 1) * 100) + Rnd()) / 100
   End If
Next i
   
r.Value2 = AR
End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
Glad we (mainly lrobbo ;)) could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,832
Messages
5,598,361
Members
414,233
Latest member
WolverineNurse

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