Applying formula to range of cells, then pasting values

Question

I need to apply a formula/function (the DOLLAR function) to a range of cells, then cut and paste just the values.

I have a heading row and use Find to get to the column that I need. Then with all of the cells below, I want to apply the DOLLAR formula. However, the result at the end of my macro needs to be that those cells now contain just the value after applying the formula, and not the formula itself (this is because I'm going to use the sheet to run a mail merge so it can't have formulas).

I've tried running Do While and other cut/paste methods. I just don't have the skill to get it done :(

Sub ProjectSummaryAdjustment()

   Workbooks("Project Summary").Activate

   Range("A1",Range("A1").End(xlToRight)).Find("TD Budget Effort").Select

This is as far as I've gotten with the code. Now for all the cells below, I want to apply the DOLLAR formula, then have just the values pasted there. I don't mind if it has to cut and paste to the first empty column and then cut and paste back to the original column.

You can either perform the calculation in VBA or do `Columns(1).value = Columns(1).value` after you put in the calculation. — Cyril, May 28 '19 at 18:03
And the very important comment to make related to code... [avoid select/activate wherever possible](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)! — Cyril, May 28 '19 at 18:04
Are you trying to return an actual text string or format the numbers as "Currency"? — Scott Craner, May 28 '19 at 18:33
@ScottCraner I am trying to return an actual text string but with the format looking like currency — eyeScream, May 28 '19 at 18:50

score 1 · Accepted Answer · answered May 28 '19 at 18:57

First make sure that you should avoid Select and Activate. Then you can simple use evaluate to do the conversion. You do need to set the field as text first:

With Workbooks("Project Summary").Worksheets("Sheet1") 'Change to your sheet name
    Dim rng As Range
    Set rng = .Range("A1", .Range("A1").End(xlToRight)).Find("TD Budget Effort")
    'Test to make sure the header was found
    If Not rng Is Nothing Then
        With .Range(.Cells(2, rng.Column), .Cells(.Rows.Count, rng.Column).End(xlUp))
            .NumberFormat = "@"
            .Value = .Parent.Evaluate("index(DOLLAR(" & .Address & "),)")
        End With
    End If
End With

Thanks so much, Scott. That worked perfectly. Just a question. How do I get rid of the two decimal places? — eyeScream, May 30 '19 at 21:40
`.Value = .Parent.Evaluate("index(DOLLAR(" & .Address & ",0),)")` — Scott Craner, May 30 '19 at 21:44

Applying formula to range of cells, then pasting values

1 Answers1