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?
I posted this question on another forum and got this as an answer
Please find the the updated code.
<tbody> </tbody>
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.
<tbody>
</tbody>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
Code:
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 SubI posted this question on another forum and got this as an answer
Please find the the updated code.
|
Code:
|
<tbody> </tbody>
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.
| |
Code:
|
<tbody>
</tbody>
Thanks in advance
