Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to Round variable (Excel 97 SR2)

    I am trying to write a macro that assigns certain cell values to variables then after rounding the amounts, put the integers in a new location.
    The macro goes through all the cells assigning each cell to a different variable (Var1, Var2, etc), then moves Active cell to the new position so it can write the data.
    I tried ActiveCell.Value = Round(Var1, 0) But this does not work. How should I be doing this?

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Round variable (Excel 97 SR2)

    If you want to assign a series of values, you can process them one by one, in which case you don't need a variable for the value, or you can use an array.
    In general, it's more efficient NOT to select cells in a macro, but to use a Range object. If you tell us a bit more about what you want to do, someone can advise you how to avoid selecting cells.
    In what sense does ActiveCell.Value = Round(Var1, 0) not work?

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Round variable (Excel 97 SR2)

    Hans,
    I attached a workbook with my sample.
    When I tried to use ActiveCell.Value = Round(Var1, 0) the macro fails and highlights the work Round and says Sub or Function not defined.
    Do I have to make a function to do the rounding part?

    Thanks for your time,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Round variable (Excel 97 SR2)

    Ah yes, I hadn't noticed that you're using Excel 97. The Round function was introduced in Office 2000 VBA, it doesn't exist in Office 97 VBA.

    You can use this code:

    Sub aBeginning()
    Dim i As Long
    Dim j As Long
    For i = 0 To 14 Step 2
    For j = 0 To 2
    ActiveCell.Offset(j, i / 2 + 4) = Int(ActiveCell.Offset(i, j) + 0.5)
    Next j
    Next i
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Round variable (Excel 97 SR2)

    Hans,
    Thanks for your reply. I read your response and figured well, maybe I can tweak it to work for what I want.
    I studied it and realized I could not, so I decided to step through the macro and see what it does.
    It does exactly what I asked for. I know this does not surprise you, but I could not see how it was going to do what I wanted.
    I have a LOOONG way to go in my understanding of how to write (and read) VBA.
    I thank you very much.
    Have a Great Weekend.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Round variable (Excel 97 SR2)

    Think of i as the row, and of j as the column in the "source" range. The double loop

    For i = 0 To 14 Step 2
    For j = 0 To 2
    ...l
    Next j
    Next i

    traverses all rows in the source range (the "Step 2" part is because every other row is blank), and all columns in each row.

    ActiveCell.Offset(i, j) is the cell i rows down and j columns to the right of the active cell. To round the value of this cell, we add .5, then chop of the fractional part by taking Int. For example, 2.34 becomes 2.84 and Int rounds this down to 2, whereas 3.79 becomes 4.29 and Int rounds this to 4.

    ActiveCell.Offset(j, i / 2 + 4) is the cell in the "target" range. The roles of i (row) and j (column) are reversed, and i is divided by 2 because we don't want blank columns in the result. The + 4 specifies that the Target range starts 4 columns to the right of the active cell. If you had wanted to shift the target range up or down, you'd also have had to add something to j.

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Round variable (Excel 97 SR2)

    Hans,
    You are not only a Master at solutions, but a great instructor as well.
    Thank you for the concise explaination. It really helped.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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