+ Reply to Thread
Results 1 to 14 of 14

Multiply Range by a Constant Multiple

  1. #1
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    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. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Multiply Range by a Constant Multiple

    Try this part
    Please Login or Register  to view this content.
    Instead of this part
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    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. #4
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

    Re: Multiply Range by a Constant Multiple

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

  5. #5
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    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. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,567

    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.
    BTW, please use code tags for your code. Highlight your code and click on the #

  7. #7
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    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"
    ActiveWorkbook.SaveAs Filename:="F:\Trading\TD MODELS\TDSmallMod(328)\Holdings.csv", _
    FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    '
    End Sub

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Multiply Range by a Constant Multiple

    I think it is better to attach your sample workbook?

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,567

    Re: Multiply Range by a Constant Multiple

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

    Read the forum rules (#3 in your case)

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

  10. #10
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    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. #11
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

    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. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,969

    Re: Multiply Range by a Constant Multiple

    Guys, please use code tags when posting code. See my notes below on how to
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,567

    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. #14
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to multiply a constant to only first 10 digits of 45 digits in a Logical Function
    By gz3s36 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-10-2014, 02:40 PM
  2. I need numbers in given cells to multiply by a constant number
    By normanluc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-27-2012, 12:16 AM
  3. Multiply cell by constant.
    By mightybhwk in forum Excel General
    Replies: 3
    Last Post: 12-16-2011, 01:25 PM
  4. multiply by a constant number
    By tarheel in forum Excel General
    Replies: 1
    Last Post: 12-15-2011, 12:36 PM
  5. Multiply range with constant and round results
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2011, 04:58 AM
  6. Multiply range by a constant
    By jvanhoos in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-26-2009, 03:46 PM
  7. User Defined Function: multiply array by a constant
    By laterdaysluke in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2008, 06:06 PM
  8. Paste value from another workbook and multiply by constant
    By burl_rfc_h in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-06-2006, 02:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1