Formula to Value Macro

Asked Sep 14 '22 at 17:14

Active Sep 14 '22 at 17:14

Viewed 29 times

I am working on a macro that changes a specific row of formulas to their resultant values.

This macro should only work on a row if the value in column A matches the value in cell A1 on another worksheet(GRAND TOTALS). That works perfectly, however the "formula to value" task of my macro does not. I believe I am calling the desired row incorrectly. My code:

    Sub FormulaToValue()
    response = Sheets("GRAND TOTALS").Range("A1")
    lastRow = Sheets("Mem History").Columns(1).Find(What:=response).Row
    'MsgBox (lastRow)
    Sheets("Mem History").Activate
    Set Cell_Range = Rows(lastRow)
    For i = 1 To Cell_Range.Cells.Count
    Cell_Range.Cells(i) = Cell_Range.Cells(i).Value
    Next i
    End Sub

asked Sep 14 '22 at 17:14

Andy Dufresne

1

What happens if you use: `Cell_Range.Cells(i).Value = Cell_Range.Cells(i).Value`? – cybernetic.nomad Sep 14 '22 at 17:23
Same as before. It seems like it's going through (or counting) every cell, but the formula remains for each cell unfortunately. – Andy Dufresne Sep 14 '22 at 17:28
try: `Cell_Range.Cells(i).Value = Cell_Range.Cells(i).Value2` – cybernetic.nomad Sep 14 '22 at 17:29
I think you need `set Cell_range = Sheets("MemHistory").Range("A1:A" & lastRow)` - your current range only references the last row of your active sheet. – Ike Sep 14 '22 at 17:30
@cybernetic.nomad Same thing :/ Ike - With that it no longer seems like it's "counting" cells, but the formula remains :/ – Andy Dufresne Sep 14 '22 at 17:34
1

`Sheets("Mem History").Rows(lastRow).Value = Sheets("Mem History").Rows(lastRow).Value`. – BigBen Sep 14 '22 at 18:03
@BigBen that did it. Much simpler than I was doing. Thank you so much. – Andy Dufresne Sep 14 '22 at 18:09
2

Moral of the story, *always* quality the worksheet. – BigBen Sep 14 '22 at 18:10

Formula to Value Macro

0 Answers0