Convert a foormula to a value in a macro.

[FFlyboyy]

I've been researching thisbut none of the answers I found seemed to make sense in what I am doing.

I have the following code:


' find last row
findlastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lastrow = findlastrow
lastrow = lastrow - 1



' set totals row
totalrow = lastrow + 1

' Sum purchases, international, roaming and other charges and credits
Columns("R:R").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R1").Select
ActiveCell.FormulaR1C1 = "Other Chgs & Creds"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R" & lastrow), Type:=xlFillDefault
Range("R2:R" & lastrow).Select


Columns(17).EntireColumn.Delete

The "Sum Purchases inserts a column after collumn Q and then runs a formula to total columns N-Q.

I need to convert the column with formula (column R) to values so when I delete N-Q the totals will be there.

i wrote this about 3 years ago and havent done much scripting since so I get lost easliy. Any help mucj appreciated.

 

[MARK858]

Maybe...

Sub FFlyboyy()
    ' find last row
    findlastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
   
   lastrow = findlastrow - 1

    ' set totals row
    totalrow = lastrow + 1

    ' Sum purchases, international, roaming and other charges and credits
    Columns("R:R").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("R1").Value = "Other Chgs & Creds"

    With Range("R2:R" & lastrow)
        .FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Value = .Value
    End With

    Columns(17).EntireColumn.Delete
End Sub

Btw isn't totalrow the same as findlastrow?

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub FFlyboyy()
   Dim UsdRws As Long
   UsdRws = Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row
   Columns("R:R").Insert
   Range("R1").Value = "Other Chgs & Creds"
   With Range("R2:R" & UsdRws)
      .FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
      .Value = .Value
   End With
   Columns(17).Delete
End Sub

 

[FFlyboyy]

Thanks, I'll give that a try.

I loaded the findlastrow into a variable to make it easier to point to. Lastrow to me is the last row of the detail cells. Totalrow is the row with well, the totals.

 

[MARK858]

If creating another variable makes it easier for you then it is all good as it doesn't do any harm

 

[FFlyboyy]

Thanks all, they both worked. Problem solved!

 

[Fluff]

Glad we could help & thanks for the feedback