# Multiply Range by a Constant Multiple

1. ## Multiply Range by a Constant Multiple

Hello,

I have created a macro to clean up a spread sheet. I would like to multiply each cell in the range by 98%.
I am getting a "Mismatch" error regarding my line that reads ".Value=.Value*.98" and its highlighted in yellow.

I have pasted my code below. Any help would be much appreciated. I am new to VBA.

Range("A:B,D:H,J:J").Select
Range("J1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("D9").Select
With Range("B1:B200")
.Value = .Value * 0.98
End With

Thank You,
Brandon

2. ## Re: Multiply Range by a Constant Multiple

Try this part
``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``

3. ## Re: Multiply Range by a Constant Multiple

Hey YasserKhalil,

Do I need to define "Cell". I tried the what you posted, but received an error message and a different line is highlighted as an error now. "Sub TD_328_Mod() is highlighted

Sub TD_328_Mod()
'
' TD_328_Mod Macro
'
Range("A:B,D:H,J:J").Select
Range("J1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("D9").Select
For Each Cell In Range("B1:B200")
Cell.Value = Cell.Value * 0.98
Next Cell

4. ## Re: Multiply Range by a Constant Multiple

use this cod for the numbers only
``Please Login or Register  to view this content.``

5. ## Re: Multiply Range by a Constant Multiple

Hey Salim,

I am receiving an "Define End of Statement" error.

Sub TD_328_Mod()
'
' TD_328_Mod Macro
'
Range("A:B,D:H,J:J").Select
Range("J1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("D9").Select
Sub multiplly_by()
For Each cel In Range("B1:B200") & Cells(Rows.Count, 2).End(3).Row) _
.SpecialCells(xlCellTypeConstants, 1)

I italicized what is red in my macro. The error pops up before I can finish writing the rest of the code.

6. ## Re: Multiply Range by a Constant Multiple

Don't need looping
Replace
``Please Login or Register  to view this content.``
with
``Please Login or Register  to view this content.``

7. ## Re: Multiply Range by a Constant Multiple

Hey Jolivanes,

I apologize before hand, but don't fully understand how to use code tags.

Once I implemented the "[B1:B200] = [(B1:B200)*0.98]" I received "VALUE" in all of my cells. I then proceeded to format the cells with another line, but I am receiving another error.

Current error message "Compile error, Expected End Sub". Below is my code.

Sub TD_328_Mod()
'
' TD_328_Mod Macro
'
Range("A:B,D:H,J:J").Select
Range("J1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("D9").Select
[B1:B200] = [(B1:B200)*0.98]
Sub formatColumns()
Columns(2).NumberFormat = "General"
'
End Sub

8. ## Re: Multiply Range by a Constant Multiple

I think it is better to attach your sample workbook?

9. ## Re: Multiply Range by a Constant Multiple

Re: I apologize before hand, but don't fully understand how to use code tags.

And yes, an example workbook would help a lot, as YasserKhalil mentions.

10. ## Re: Multiply Range by a Constant Multiple

``Please Login or Register  to view this content.``
Unfortunately, I am unable to attach a copy at this time. I understand if it makes it too difficult to help without the attachment.

Thank you all for you help and I will make sure to endorse each response.

Any further help would be appreciated.

11. ## Re: Multiply Range by a Constant Multiple

write the line
For Each cel In Range("b1:b" & Cells(Rows.Count, 2).End(3).Row) _
.SpecialCells(xlCellTypeConstants, 1)
exactly as in the code(without- B200")

12. ## Re: Multiply Range by a Constant Multiple

Guys, please use code tags when posting code. See my notes below on how to

13. ## Re: Multiply Range by a Constant Multiple

Replace
``Please Login or Register  to view this content.``
with
``Please Login or Register  to view this content.``
Delete
``Please Login or Register  to view this content.``
Are the values in Column B, after the deleting, numbers?

14. ## Re: Multiply Range by a Constant Multiple

Hey Jolivanes,

Yes, the values are in Column B after deleting the numbers. My goal is to reduce Column B by 2% (Column B * 0.98) and then format it so it is in a "General" format.

Thank you

There are currently 1 users browsing this thread. (0 members and 1 guests)