+ Reply to Thread
Results 1 to 6 of 6

VBA Code to multiply column by value

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-26-2012
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2007
    Posts
    129

    VBA Code to multiply column by value

    Hello,

    I am a little stuck on why this won't work. I copied values from one sheet to another. Now, on the new sheet, I'd like to multiply one particular column by 1000. However, the multiplication is not updating.

    Private Sub GetRawData_Click()
      
     With Sheets("Raw Data")
        .Range("A11", .Range("A" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("A12")
        .Range("B11", .Range("B" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("B12")
        .Range("E11", .Range("E" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("C12")
        .Range("F11", .Range("F" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("D12")
        .Range("H11", .Range("H" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("E12")
        
        End With
    
        Dim i As Integer
        Dim c As Integer
        
        i = Application.CountA(Range("B12"))
        
        For c = 12 To i
            Cells(c, B) = Cells(c, B) * 1000
        Next c
      
      
    End Sub

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: VBA Code to multiply column by value

    Questions on this:

    1. Do you know what the value of "i" is when it runs? Maybe your "For" loop is not running since i would have to be greater than 12
    2. "Cells(c, B)" would not really work, since you are using B without quotes VBA would treat is as a variable, and is presumably 0 at the moment, replace your B with a 2?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Contributor
    Join Date
    02-26-2012
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2007
    Posts
    129

    Re: VBA Code to multiply column by value

    Okay, I made those corrections. For i, I set it equal to a cell. Because the first column represents the test number, I used that to represent the last row. I know this is not the most efficient way. It works now actually.

    Dim i As Integer
        i = Cells(4, "I")
     
        'Counter for Ron
         Dim r As Integer
     
         For r = 12 To i
          Cells(r, 2) = Cells(r, 2) * 1000
          Next r

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: VBA Code to multiply column by value

    Hi Natalie,

    Does this mean the issue is resolved? You could change your code for i as follows, to pick up the last cell:

    i = Range("A" & Rows.Count).End(xlUp).Row

  5. #5
    Forum Contributor
    Join Date
    02-26-2012
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2007
    Posts
    129

    Re: VBA Code to multiply column by value

    Awesome, that line worked! I guess it is resolved now. Thanks!

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: VBA Code to multiply column by value

    Please don't forget to marked as SOLVED
    Glad we could help you out!

+ 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. Replies: 18
    Last Post: 02-23-2014, 07:02 PM
  2. how to multiply row and column
    By dinatamrakar in forum Excel General
    Replies: 1
    Last Post: 09-20-2013, 08:52 AM
  3. If and Then Formula/Code to multiply with a Designated Cell
    By aaron.mendes in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-09-2013, 03:08 PM
  4. Replies: 1
    Last Post: 07-12-2012, 02:29 PM
  5. Replies: 8
    Last Post: 08-06-2009, 09:02 AM

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