Mass way to modify number values

**no.18shirt** · 05-13-2013, 07:46 AM

Hi

I have a workbook with lots of analytic data from my company which I want to share with other people not associated with the company. Obviously I can't share private data so I'm wondering if theres a quick way to modify all the data I have with 'fake numbers' which means I could share it. With fake numbers no privacy rules will be breached etc.

Is there a quick way to randomly change all numbers by +/- 10-20%?

Thanks

**ARGK** · 05-13-2013, 07:55 AM

Hi
You could use a formula like:
=A1*1.2%
Then copy it down your column.
Then copy the column and use "Paste Special" to replace the formulae with just the values.
This is assuming your data is already numbers of course :-).
Hope this helps.
Good luck.
Tony

**Jakobshavn** · 05-13-2013, 08:12 AM

Select each sheet containing the numbers that you wish to adjust and run this small macro:

Sub AdjustNumbers()
    Dim rAdjust As Range, r As Range
    Set rAdjust = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
    For Each r In rAdjust
        r.Value = r.Value * (100 + Application.WorksheetFunction.RandBetween(-20, 20)) / 100
    Next
End Sub

**no.18shirt** · 05-14-2013, 07:12 AM

Originally Posted by Jakobshavn

Select each sheet containing the numbers that you wish to adjust and run this small macro:

Sub AdjustNumbers()
    Dim rAdjust As Range, r As Range
    Set rAdjust = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
    For Each r In rAdjust
        r.Value = r.Value * (100 + Application.WorksheetFunction.RandBetween(-20, 20)) / 100
    Next
End Sub

Thanks, How do I modify this to select the data from cell J2 to cell N25730? I just want to plug in the code and play, thanks.

**Pepe Le Mokko** · 05-13-2013, 08:19 AM

Alternatively enter 1.2 in an empty cell and copy it
Select all cells to be changed - Right click - Paste special - Multiply

**Jakobshavn** · 05-13-2013, 08:29 AM

Hi Pepe:

I think the OP wants each original value to be multiplied by a random value between 0.8 and 1.2 rather than 1.2

**Pepe Le Mokko** · 05-13-2013, 08:49 AM

Originally Posted by Jakobshavn

Hi Pepe:

I think the OP wants each original value to be multiplied by a random value between 0.8 and 1.2 rather than 1.2

Ah yes, thanks for pointing that out. I rest my case

**DonkeyOte** · 05-13-2013, 08:46 AM

Similar to Jakobshavn, however, using Evaluate to process by Area rather than Cell.

Public Sub Example()
    Dim rngArea As Range
    On Error Resume Next
    For Each rngArea In Selection.SpecialCells(xlCellTypeConstants, xlNumbers).Areas
        With rngArea
            .Value = .Parent.Evaluate(.Address & "*(100+RANDBETWEEN(-20,20))/100")
        End With
    Next rngArea
End Sub

**Jakobshavn** · 05-14-2013, 07:19 AM

Sub AdjustNumbers()
    Dim rAdjust As Range, r As Range
    Set rAdjust = Range("J2:N25730").SpecialCells(xlCellTypeConstants, xlNumbers)
    For Each r In rAdjust
        r.Value = r.Value * (100 + Application.WorksheetFunction.RandBetween(-20, 20)) / 100
    Next
End Sub