Replacing a formula with it's result with vba

[andy72685]

I am creating a spreadsheet that pulls information from a named range, averages it, rounds it and puts it in a cell. The data will change daily, so when it closes I need it to save the current value in the cell. I have tried to set the value to a variable, and then input that variable to replace the calculation, but it says ‘Object required’ When it does, it highlights the first variable, but the yellow debug line is at the top, where the sub starts. Can you see where I’m going wrong?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh5 As Worksheet
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim f As Integer
Set sh5 = ThisWorkbook.Sheets(“CHANGING AVERAGES”)
a = 0
b = 0
c = 0
d = 0
e = 0
f = 0
If Range(“DATE!B2”).Value = 0 Then
If MsgBox(“You must enter 1 in cell B2 on the DATE worksheet before closing.”, _
vbCritical + vbOKOnly, “WARNING!!!!”) = vbOK Then
Cancel = True
End If
Else: Cancel = False
End If
Set a = sh5.Cells(2, Columns.Count).Value
Set b = sh5.Cells(3, Columns.Count).Value
Set c = sh5.Cells(4, Columns.Count).Value
Set d = sh5.Cells(5, Columns.Count).Value
Set e = sh5.Cells(6, Columns.Count).Value
Set f = sh5.Cells(7, Columns.Count).Value
sh5.Cells(2, Columns.Count) = a
sh5.Cells(3, Columns.Count) = b
sh5.Cells(4, Columns.Count) = c
sh5.Cells(5, Columns.Count) = d
sh5.Cells(6, Columns.Count) = e
sh5.Cells(7, Columns.Count) = f
End Sub

I posted this question on another forum and got this as an answer

Please find the the updated code.

[TABLE="width: 765"]
<tbody>[TR]
[TD]



































[/TD]
[TD]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
Dim sh5 As Worksheet
 Dim a As Integer, b As Integer, c As Integer
 Dim d As Integer, e As Integer, f As Integer
  
Set sh5 = ThisWorkbook.Sheets("CHANGING AVERAGES")
a = 0: b = 0: c = 0
 d = 0: e = 0: f = 0
  
If Range("DATE!B2").Value = 0 Then
    If MsgBox("You must enter 1 in cell B2 on the DATE worksheet before closing.", _
          vbCritical + vbOKOnly, "WARNING!!!!") = vbOK Then
                  Cancel = True
      End If
 Else: Cancel = False
 End If
  
 a = sh5.Cells(2, Columns.Count).Value
 b = sh5.Cells(3, Columns.Count).Value
 c = sh5.Cells(4, Columns.Count).Value
 d = sh5.Cells(5, Columns.Count).Value
 e = sh5.Cells(6, Columns.Count).Value
 f = sh5.Cells(7, Columns.Count).Value
  
With sh5
     .Activate
     .Cells(2, Columns.Count) = a
     .Cells(3, Columns.Count) = b
     .Cells(4, Columns.Count) = c
     .Cells(5, Columns.Count) = d
     .Cells(6, Columns.Count) = e
     .Cells(7, Columns.Count) = f
 End With
End Sub

[/TD]
[/TR]
</tbody>[/TABLE]

This followed:
With dimensioning the variables as integers, it should be telling the system to accept ONLY integers, right? I ask because the formula RETURNS an integer type (the actual value of the cell), but is not, strictly speaking, an integer. I input the code as stated and saved the program. When I closed it, it asked if I wanted to save the changes, which indicates it DID store a value in the variable and changed each cell accordingly, but when I reopened it, the formula still remained in the cells, rather than the result.
I even resorted to selecting EVERY column after the last and deleted them, in case there was formatting across those cells that were causing problems. and still nothing.
I’m stumped…

At this point I assumed that the variable was storing a ‘value’ beyond the end of the active columns

I edited a bit and came up with the following:

As you can see below, I abandoned the variable method, and have changed to a simple cut / paste method. I used ‘Record Macro’ to get the correct syntax. However now when I close it the Columns.Count Selects Column XFD. I copied and pasted my used range into a new sheet, so there is no entire row formatting, and no reason that I can tell that it should count all the way out there.

[TABLE="width: 781"]
<tbody>[TR]
[TD]
















































[/TD]
[TD]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh5 As Worksheet
Set sh5 = ThisWorkbook.Sheets(“CHANGING AVERAGES”)
If Range(“DATE!B2”).Value = 1 Then
If MsgBox(“You must enter 1 in cell B2 on the DATE worksheet before closing.”, _
vbCritical + vbOKOnly, “WARNING!!!!”) = vbOK Then
Cancel = True
End If
Else: Cancel = False
End If
With sh5
.Activate
.Cells(2, Columns.Count).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Cells(3, Columns.Count).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Cells(4, Columns.Count).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Cells(5, Columns.Count).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Cells(6, Columns.Count).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Cells(7, Columns.Count).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
End Sub

[/TD]
[/TR]
</tbody>[/TABLE]
I realize this was a bit drawn out, but I wanted to be sure the issue was fully explained. It’s been almost two weeks since I got a response on the other forum and would like some insight into this.
Thanks in advance

 

[andy72685]

Interesting insight, but that was one of the things mentioned in the initial post. I saved before clicking the close button. The Sheets.Activate command was executed and the appropriate sheet came up. The 'active cell' outline travelled down the last column, and it asked if I wanted to save the changes. The problem is when I reopened the file the 'changes' that were made were just reentering the formula. It's not terrible to go in manually and enter the values rather than the formula, but if I forget, or someone else is working the program, I'd rather have it automated.

 

[andy72685]

Catalin,

Just to confirm, is this telling excel to evaluate the formula inside the cell, discarding the formula and then replacing it with the answer it has just worked out?

 

[Joe4]

The 'active cell' outline travelled down the last column, and it asked if I wanted to save the changes.

And did you select "Yes"? If not, it won't save any of the changes your code just made and you won't see them the next time you open the workbook.

You can just add a line to the bottom of the BeforeClose code that says:

ActiveWorkbook.Save

and then it should do it automatically without that prompt.

 

[CatOnASpreadsheet]

Hi Andy,

The instruction literally tells Excel to take whatever is in the cell and replace it with the actual value. If the cell contains a value before the line is executed, the cell will remain unchanged, but if it contains a formula it will replace it with the formula result at the time the code is run.

Cheers,
Catalin

 

[andy72685]

Awesome! I didn't know you could auto save with a code line!

 

[andy72685]

Thanks. This helped alot!

 

[Joe4]

Awesome! I didn't know you could auto save with a code line!

Sure.

If you are not sure, a good way to check it to turn on the Macro Recorder and record yourself performing the step manually. Then stop your Macro Recorder and view the code your recorded, and see what it came up with.

 

[andy72685]

Thanks again. This place is great for tips and tricks. I'm glad I found it!