VBA code to divide a range of cells by 1000 with command button

[marcapicella]

Hello,

I need help with dividing a range of cells (say G7:K16) by using a VBA command button. I will also be creating many of these command buttons in different sheets but they will serve the same purpose (dividing different ranges by 1000).

Thanks for your help!

 

[Gary McMaster]

Welcome aboard!

Have a look at this thread:

http://www.mrexcel.com/forum/excel-questions/94744-divide-all-figures-workbook-1000-a.html

 

[marcapicella]

Welcome aboard!

Have a look at this thread:

http://www.mrexcel.com/forum/excel-questions/94744-divide-all-figures-workbook-1000-a.html

Hi Gary,

I am getting a message box saying "unable to get the SpecialCells property of the Range class". Also the formula in that thread only cites one cell (A1) and not a range. I'm assuming I would just change it to fit my range. Any suggestions?

I'm also relatively new to using VBA.

 

[Gary McMaster]

I guess you're not interested in the pastespecial/divide technique. I believe the code shown in that thread was meant to do every cell in every sheet.

Try putting something simple in your command button and improve it from there, maybe this:

Dim oCell As Range

For Each oCell In Selection
    oCell.Value = oCell.Value / 1000
Next oCell

Select/Highlight the range you want to multiply then click the button.

 

[Rick Rothstein]

I guess you're not interested in the pastespecial/divide technique. I believe the code shown in that thread was meant to do every cell in every sheet.

Try putting something simple in your command button and improve it from there, maybe this:

Dim oCell As Range

For Each oCell In Selection
    oCell.Value = oCell.Value / 1000
Next oCell

Select/Highlight the range you want to multiply then click the button.

This non-looping macro should be faster...

Sub DivideSelectionBy1000()
Selection = Evaluate(Replace("IF(ISNUMBER(@),@/1000,IF(@="""","""",@))", "@", Selection.Address))
End Sub

 

[marcapicella]

Fantastic! Thank you!!

I guess you're not interested in the pastespecial/divide technique. I believe the code shown in that thread was meant to do every cell in every sheet.

Try putting something simple in your command button and improve it from there, maybe this:

Dim oCell As Range

For Each oCell In Selection
    oCell.Value = oCell.Value / 1000
Next oCell

Select/Highlight the range you want to multiply then click the button.