Macro Divide Cells In a Range by Value

[shawnjfulton]

Hi, and thank you in advance for your help. This is probably pretty simple for someone who knows what they are doing!

I have a range, K4:W63. I want to divide each cell, by 1024. For Example =K4/1024; and have that value placed in the cell of K4.

Thanks again for your help!

 

[hiker95]

shawnjfulton,

Welcome to the MrExcel forum.

Sample raw data (not all rows are shown for brevity):

Excel 2007
	K	L	M	N	O	P	Q	R	S	T	U	V	W
4	1	2	3	4	5	6	7	8	9	10	11	12	13
5	1	2	3	4	5	6	7	8	9	10	11	12	13
6	1	2	3	4	5	6	7	8	9	10	11	12	13
7	1	2	3	4	5	6	7	8	9	10	11	12	13
8	1	2	3	4	5	6	7	8	9	10	11	12	13
9	1	2	3	4	5	6	7	8	9	10	11	12	13
10	1	2	3	4	5	6	7	8	9	10	11	12	13
Sheet1

After the macro using an array in memory:

Excel 2007
	K	L	M	N	O	P	Q	R	S	T	U	V	W
4	0.000977	0.001953	0.00293	0.003906	0.004883	0.005859	0.006836	0.007813	0.008789	0.009766	0.010742	0.011719	0.012695
5	0.000977	0.001953	0.00293	0.003906	0.004883	0.005859	0.006836	0.007813	0.008789	0.009766	0.010742	0.011719	0.012695
6	0.000977	0.001953	0.00293	0.003906	0.004883	0.005859	0.006836	0.007813	0.008789	0.009766	0.010742	0.011719	0.012695
7	0.000977	0.001953	0.00293	0.003906	0.004883	0.005859	0.006836	0.007813	0.008789	0.009766	0.010742	0.011719	0.012695
8	0.000977	0.001953	0.00293	0.003906	0.004883	0.005859	0.006836	0.007813	0.008789	0.009766	0.010742	0.011719	0.012695
9	0.000977	0.001953	0.00293	0.003906	0.004883	0.005859	0.006836	0.007813	0.008789	0.009766	0.010742	0.011719	0.012695
10	0.000977	0.001953	0.00293	0.003906	0.004883	0.005859	0.006836	0.007813	0.008789	0.009766	0.010742	0.011719	0.012695
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub DivideBy1024()
' hiker95, 03/27/2013
' http://www.mrexcel.com/forum/excel-questions/693943-macro-divide-cells-range-value.html
Dim k As Variant, i As Long, ii As Long
k = Range("K4:W63").Value
For i = 1 To UBound(k, 1)
  For ii = 1 To UBound(k, 2)
    k(i, ii) = k(i, ii) / 1024
  Next ii
Next i
Range("K4:W63").Value = k
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the DivideBy1024 macro.

 

[shawnjfulton]

Thank you very much. It seems that this will work. However, when I run this, I receive an error. In this range I have a some cells that contain "N/A". Is there some code to insert to ignore the cells with text?

 

[jasmith4]

Argh!! Why all that coding? Here's the easy way:

1. Go to a blank cell and enter 1024.
2. Hit Ctrl-C to copy that one cell.
3. Select K4:W63.
4. On the Home ribbon drop-down the Paste button and choose Paste Special
5. Under Paste choose Values, and under Operation choose Divide.
6. Click OK.

 

[hiker95]

shawnjfulton,

In the future when asking for help you should provide screenshots of before and after so that we can see exactly what we are working with, and, so that we can get it right the first time.

What version of Excel are you using?


I will need to see your workbook/worksheet.


Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Even better:

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[shawnjfulton]

I would like to make this process easier as this will be a daily tracking sheet with a few more activities.

 

[jasmith4]

OK -- turn on macro recording before you folow my instructions, and turn it off when you're done. Then you should be able to change the code just a little to suit your needs. Macro recording is on the Developer ribbon, which is normally not shown, so you have to find it in Customize Ribbon.

 

[shawnjfulton]

Sorry I did not post in the correct format. However I have modified the code to work for my situation:

Dim k As Variant, i As Long, ii As Long
k = Range("K4:O63").Value
For i = 1 To UBound(k, 1)
  For ii = 1 To UBound(k, 2)
        If k(i, ii) <> "N/A" Then
            k(i, ii) = k(i, ii) / 1024
        End If
  Next ii
Next i
Range("K4:O63").Value = k

Thank you for your help

 

[shawnjfulton]

Thank you jasmith4. I have never used Macro recording before. I will try to use this in the future.

 

[hiker95]

shawnjfulton,

Nice catch and fix for my macro.

If you had explained that the range contained formulae, and, that some of the cells displayed errors (and what the errors were) then I could have posted the macro to work correctly for you from the start.

Thanks for the final feedback.

Glad I could get you started in the right direction for a solution.

Come back any time.