CODEWITHSUNDEEP
excelselection
M M
M M

Reputation: 65

how do I have a macro operate on a range of cells that I selected beforehand?

So, I have a macro that loops through a lot of cells on the worksheet and does stuff to them.

Here's the code http://pastie.org/4290581

Private Sub ReplaceFormulaWithValues()
    Dim lastrow As Long, r1 As Long
    Dim temp As String, arTemp
    Dim temp2 As String
    Dim temp3 As String

    Dim letter

    temp3 = ""

        ' Get the last row in the worksheet
    lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

    For r1 = 1 To lastrow

        For r2 = 0 To 10
            letter = Chr(Asc("A") + r2)
            letter = letter + LTrim(Str(r1))


            ' If A & B aren't blank, make the formula of cell C equal to A + B.
            If Sheet1.Range(letter).Value <> "" And Mid(Sheet1.Range(letter).Formula, 1, 1) = "=" Then
                If Asc(Mid(Sheet1.Range(letter).Formula, 2, 1)) >= 65 And Asc(Mid(Sheet1.Range(letter).Formula, 2, 1)) <= 90 Then
                    ' Get the formula for the current C cell
                    temp = Replace(Sheet1.Range(letter).Formula, "=", "")

                    'Create an array by splitting the formula on the + sign
                    arTemp = Split(temp, "+")

                    temp2 = Sheet1.Range(arTemp(0)).Value

                    For i = 1 To UBound(arTemp)
                        temp3 = Sheet1.Range(arTemp(i)).Value
                        temp2 = temp2 & "+" & temp3

                    Next i

                    Sheet1.Range(letter).Value = "=" & temp2
                End If

            End If

        Next
    Next
End Sub

This is what it does:

for instance, let's say that the formula of cell C2 is C2=A1+B1, where A1 = 10 and B1 = 20.
I would like to change it so that the formula of cell C2 is C2=10+20.
However, I don't want the formula displayed in the cell or anything.

My question: how do I set it up so that I first can highlight/select a group of cells from the worksheet, THEN activate the macro so that the macro only works on every cell in that range?

Upvotes: 0

Views: 14403

Answers (3)

NickSlash
NickSlash

Reputation: 5077

I had a go at making something to do what I think you were trying to achieve.

It (The Macro EvaluateSelection) should look through every cell in your selection that contains a formula (starts =) and change it to a semi-evaluated formula.

Sub EvaluateTarget(ByVal Target As Range)
Dim Result As String
Dim Cell As Range
Dim Precedent As Range

Dim Formula As String
Dim CellReference As String
Dim CellFormula As String

    Formula = Target.Formula

    For Each Cell In Target.SpecialCells(xlCellTypeFormulas)
        For Each Precedent In Cell.DirectPrecedents
            CellReference = Replace(Precedent.Address, "$", "")
            CellFormula = Replace(Precedent.Formula, "=", "")
            Formula = Replace(Formula, CellReference, CellFormula)
        Next Precedent
    Next Cell

    Target.Value = Replace(Target.Address, "$", "") & Formula

End Sub

Sub EvaluateSelection()
Dim Cell As Range

For Each Cell In Selection.Cells
    If Left(Cell.Formula, 1) = "=" Then
        EvaluateTarget Cell
    End If
Next Cell
End Sub

Reference - Found this and worked from it.

I've not tested this much at all, seems to work for my simple examples.

Upvotes: 0

Stepan1010
Stepan1010

Reputation: 3136

I won't rewrite your code - but generally you can loop through a bunch of cells that you have selected like this:

Sub ClearZeroCells()

    Dim cell As Range
    For Each cell In Selection
        If cell = 0 Then cell.ClearContents
        ' or you can put your own code here.
    Next cell

End Sub

Upvotes: 1

Jon Kelly
Jon Kelly

Reputation: 316

The program currently loops through all of the cells in the sheet with the two for loops. These loops can be modified to only loop through the current selection using Application.Selection.

So your code will look like this:

For Each cell In Selection.Cells


    ' If A & B aren't blank, make the formula of cell C equal to A + B.
    If cell.Value <> "" And Mid(cell.Formula, 1, 1) = "=" Then
            If Asc(Mid(cell.Formula, 2, 1)) >= 65 And Asc(Mid(cell.Formula, 2, 1)) <= 90 Then
                ' Get the formula for the current C cell
                temp = Replace(cell.Formula, "=", "")

                'Create an array by splitting the formula on the + sign
                arTemp = Split(temp, "+")

                temp2 = ActiveSheet.Range(arTemp(0)).Value

                For i = 1 To UBound(arTemp)
                    temp3 = ActiveSheet.Range(arTemp(i)).Value
                    temp2 = temp2 & "+" & temp3

                Next i

                cell.Value = "=" & temp2
            End If

        End If

Next

I also used ActiveSheet instead of Sheet1 so that you could run it on any sheet.

Upvotes: 1

Related Questions