SOLVED
Home

How To Multiply A Range Of Cells By Same Number In Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-656458%22%20slang%3D%22en-US%22%3EHow%20To%20Multiply%20A%20Range%20Of%20Cells%20By%20Same%20Number%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656458%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20Recorded%20the%20Macro%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Conversion()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Conversion%20Macro%3CBR%20%2F%3E'%3C%2FP%3E%3CP%3E'%3CBR%20%2F%3ERange(%22Y1%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22B11%3AAE11%2CB13%3AAE13%2CB15%3AAE15%2CB17%3AAE17%2CB19%3AAE19%2CB21%3AAE21%2CB23%3AAE23%2CB25%3AAE25%2CB25%3AAE25%2CB27%3AAE27%2CB29%3AAE29%2CB31%3AAE31%2CB33%3AAE33%2CB35%3AAE35%2CB37%3AAE37%2CB39%3AAE39%2CB41%3AAE41%2CB43%3AAE43%2CB45%3AAE45%2CB47%3AAE47%2CB49%3AAE49%2CB51%3AAE51%2CB53%3AAE53%2CB55%3AAE55%2CB57%3AAE57%2CB59%3AAE59%2CB61%3AAE61%2CB63%3AAE63%22).Select%3CBR%20%2F%3E'%20Range(%22B11%3AAE11%2CB13%3AAE13%2CB15%3AAE15%2CB17%3AAE17%2CB19%3AAE19%2CB21%3AAE21%2CB23%3AAE23%2CB25%3AAE25%2CB25%3AAE25%2CB27%3AAE27%2CB29%3AAE29%2CB31%3AAE31%2CB33%3AAE33%2CB35%3AAE35%2CB37%3AAE37%2CB39%3AAE39%2CB41%3AAE41%2CB43%3AAE43%2CB45%3AAE45%2CB47%3AAE47%2CB49%3AAE49%2CB51%3AAE51%2CB53%3AAE53%2CB55%3AAE55%2CB57%3AAE57%2CB59%3AAE59%2CB61%3AAE61%2CB63%3AAE63%2CB65%3AAE65%2CB67%3AAE67%2CB69%3AAE69%2CB71%3AAE71%2CB73%3AAE73%2CB75%3AAE75%2CB77%3AAE77%2CB79%3AAE79%2CB81%3AAE81%2CB83%3AAE83%2CB85%3AAE85%2CB87%3AAE87%2CB89%3AAE89%2CB91%3AAE91%2CB93%3AAE93%2CB95%3AAE95%2CB97%3AAE97%2CB99%3AAE99%2CB101%3AAE101%2CB103%3AAE103%2CB105%3AAE105%2CB107%3AAE107%2CB109%3AAE109%2CB111%3AAE111%22).Select%3CBR%20%2F%3E%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteAll%2C%20Operation%3A%3DxlMultiply%2C%20_%3CBR%20%2F%3ESkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.NumberFormat%20%3D%20%220%22%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22Y1%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22B65%3AAE65%2CB67%3AAE67%2CB69%3AAE69%2CB71%3AAE71%2CB73%3AAE73%2CB75%3AAE75%2CB77%3AAE77%2CB79%3AAE79%2CB81%3AAE81%2CB83%3AAE83%2CB85%3AAE85%2CB87%3AAE87%2CB89%3AAE89%2CB91%3AAE91%2CB93%3AAE93%2CB95%3AAE95%2CB97%3AAE97%2CB99%3AAE99%2CB101%3AAE101%2CB103%3AAE103%2CB105%3AAE105%2CB107%3AAE107%2CB109%3AAE109%2CB111%3AAE111%22).Select%3CBR%20%2F%3E%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteAll%2C%20Operation%3A%3DxlMultiply%2C%20_%3CBR%20%2F%3ESkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.NumberFormat%20%3D%20%220%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20multiplying%20the%20value%20in%20Y1%20(single%20value)%20with%20the%20range%20listed%20above(Just%20the%20black%20values%20in%3C%2FP%3E%3CP%3E18-20%20LRFD)%20sheet.%20When%20i%20run%20the%20macro%2C%20it%20changes%20the%20format%20in%20the%20range.%20How%20can%20i%20fix%20it%20so%20it%20won't%20change%20the%20format%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-656458%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-656596%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Multiply%20A%20Range%20Of%20Cells%20By%20Same%20Number%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656596%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sam%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20to%20remove%20the%20lines%20that%20say%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ESelection.NumberFormat%20%3D%20%220%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20two%20lines%20of%20them%20in%20the%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-656715%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Multiply%20A%20Range%20Of%20Cells%20By%20Same%20Number%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Haytham!%3C%2FP%3E%3CP%3EThis%20line%20is%20to%20truncate%20the%20numbers.%20My%20issue%20is%20i%20don't%20want%20to%20change%20the%20color%26nbsp%3B%20of%20the%20cell.%3C%2FP%3E%3CP%3EIn%20the%20top%20picture%20below%20is%20after%20multiplication%26nbsp%3B%20and%20removing%20the%20line%20you%20just%20told%20me%20about.%20The%20second%20picture%20is%20what%20it%20originally%20looked%20like.%20i%20Just%20want%20to%20multiply%20the%20numbers%20only%20without%20modifying%20the%20color%20of%20the%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20919px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116527i4A2B2374C6A548A7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-05-29%2015_24_57-Community%20question%20on%20unit%20conversion%20-%20Excel.png%22%20title%3D%222019-05-29%2015_24_57-Community%20question%20on%20unit%20conversion%20-%20Excel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20627px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116528i522D906EA38F7C67%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-05-29%2015_26_26-Community%20question%20on%20unit%20conversion%20-%20Excel.png%22%20title%3D%222019-05-29%2015_26_26-Community%20question%20on%20unit%20conversion%20-%20Excel.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-656840%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Multiply%20A%20Range%20Of%20Cells%20By%20Same%20Number%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656840%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20replace%20this%20argument%3A%3C%2FP%3E%3CPRE%3EPaste%3A%3DxlPasteAll%3C%2FPRE%3E%3CP%3EWith%20this%3A%3C%2FP%3E%3CPRE%3EPaste%3A%3DxlValue%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20two%20of%20this%20in%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20helps%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-656860%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Multiply%20A%20Range%20Of%20Cells%20By%20Same%20Number%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20this%20is%20the%20solution.Thank%20you%20Haytham!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
SamFares
Occasional Contributor

Hello,

 

i Recorded the Macro below:

 

Sub Conversion()
'
' Conversion Macro
'

'
Range("Y1").Select
Selection.Copy
Range("B11:AE11,B13:AE13,B15:AE15,B17:AE17,B19:AE19,B21:AE21,B23:AE23,B25:AE25,B25:AE25,B27:AE27,B29:AE29,B31:AE31,B33:AE33,B35:AE35,B37:AE37,B39:AE39,B41:AE41,B43:AE43,B45:AE45,B47:AE47,B49:AE49,B51:AE51,B53:AE53,B55:AE55,B57:AE57,B59:AE59,B61:AE61,B63:AE63").Select
' Range("B11:AE11,B13:AE13,B15:AE15,B17:AE17,B19:AE19,B21:AE21,B23:AE23,B25:AE25,B25:AE25,B27:AE27,B29:AE29,B31:AE31,B33:AE33,B35:AE35,B37:AE37,B39:AE39,B41:AE41,B43:AE43,B45:AE45,B47:AE47,B49:AE49,B51:AE51,B53:AE53,B55:AE55,B57:AE57,B59:AE59,B61:AE61,B63:AE63,B65:AE65,B67:AE67,B69:AE69,B71:AE71,B73:AE73,B75:AE75,B77:AE77,B79:AE79,B81:AE81,B83:AE83,B85:AE85,B87:AE87,B89:AE89,B91:AE91,B93:AE93,B95:AE95,B97:AE97,B99:AE99,B101:AE101,B103:AE103,B105:AE105,B107:AE107,B109:AE109,B111:AE111").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "0"

Range("Y1").Select
Selection.Copy
Range("B65:AE65,B67:AE67,B69:AE69,B71:AE71,B73:AE73,B75:AE75,B77:AE77,B79:AE79,B81:AE81,B83:AE83,B85:AE85,B87:AE87,B89:AE89,B91:AE91,B93:AE93,B95:AE95,B97:AE97,B99:AE99,B101:AE101,B103:AE103,B105:AE105,B107:AE107,B109:AE109,B111:AE111").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "0"


End Sub

 

I am multiplying the value in Y1 (single value) with the range listed above(Just the black values in

18-20 LRFD) sheet. When i run the macro, it changes the format in the range. How can i fix it so it won't change the format?

 

Thanks,

Sam

 

4 Replies
Highlighted

Hi Sam,

 

Try to remove the lines that say:

Selection.NumberFormat = "0"

 

There are two lines of them in the macro.

 

@SamFares

 

Hope that helps

Highlighted

@Haytham Amairah 

 

Thank you Haytham!

This line is to truncate the numbers. My issue is i don't want to change the color  of the cell.

In the top picture below is after multiplication  and removing the line you just told me about. The second picture is what it originally looked like. i Just want to multiply the numbers only without modifying the color of the cell. 

 

 

2019-05-29 15_24_57-Community question on unit conversion - Excel.png

2019-05-29 15_26_26-Community question on unit conversion - Excel.png 

Highlighted
Solution

@SamFares

 

If so, replace this argument:

Paste:=xlPasteAll

With this:

Paste:=xlValue

 

There is two of this in the code.

 

I hope that helps 

Highlighted

@Haytham Amairah 

Yes this is the solution.Thank you Haytham!

Related Conversations
Help with simple formulas please
nursekimberley in Excel on
2 Replies
Excel or Access? basic advice from which to start from.
grifton in Excel on
2 Replies
Goal seek
Abui_2195 in Excel on
3 Replies
Preparing a stacked chart
EuroSree in Excel on
2 Replies
Please Help debug my matrix formula
Katharina_Stemmer1989 in Excel on
1 Replies
Linking external source (eg Share Price) to a cell
Aitch1964 in Excel on
1 Replies