Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: I want to edit all cells in a selected range

    I need to display values in thousands in a worksheet, i do this often so wanted to be able to select multiple ranges and then run the macro. I have done some, but my problem is selecting the next cell in the selected range.

    Heres my code. Any other suggestions are welcome

    Sub Thousands()
    Dim starts As String
    Dim formula As String
    Dim first As Boolean
    Dim cellOne As String
    Do While cellOne <> ActiveCell.Address
    cellOne = ActiveCell.Address
    starts = Left(ActiveCell.FormulaR1C1, 1)

    If starts = "=" Or starts = "+" Or starts = "-" Then
    formula = ActiveCell.formula & "/1000" '.FormulaR1C1
    Else
    formula = "=" & ActiveCell.formula & "/1000"
    End If

    If MsgBox("Update " & vbCrLf & ActiveCell.formula & vbCrLf & "to:" & vbCrLf

    & formula, vbYesNo, "Do you want to update formula?") = vbYes Then
    If starts = "=" Or starts = "+" Or starts = "-" Then
    ActiveCell.FormulaR1C1 = ActiveCell.formula & "/1000"
    Else
    ActiveCell.FormulaR1C1 = "=" & ActiveCell.formula & "/1000"
    End If
    End If
    ActiveCell.Offset(1, 0).Select 'THIS DONT WORK
    Loop
    End Sub

  2. #2
    Join Date
    Dec 2004
    Posts
    37

    This may point you in the direction

    Sub Div1000()
    Dim Rng As Range
    Dim C As Range

    Set Rng = Selection
    For Each C In Rng
    C.FormulaR1C1 = "=" & C & "/1000"
    Next
    End Sub

    The above will take whatever range is selected and change whatever number is there, to a formula that divides the number by 1000.

    the loop takes all the cells selected (Set as Rng) and goes through them one at a time (C will default to a single cell)

    Hope this helps

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    To make it more versatile, Dim a string and include an Input Box asking user what the string should be.

    Or to do it without code, then put 1000 into a blank cell, copy it, and then select all cells, and Paste Special > check Values and Divide. Click OK
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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