Had this problem a few days ago...thought it was rectified but was wrong. I have no idea why, but here's what's happening.
I have one primary worksheet that I'll make changes to - "comp1". I'll then make right click on "comp1" and produce 3 copies which shows up as comp1 (2), comp1 (3), and comp1 (4). I'll then rename them to comp2, comp3, and comp4. I'm not sure if it's relevant, but these sheets have been repeatedly deleted with comp2-comp4 being replaced after I make updates to comp1. The reason I mention it is I 'think' it's a referencing error, but I'm not sure how to test that theory. So, although the name of each worksheet is comp2, the properties tab shows (Name) and it's set to Sheet20 or whatever sheet it's at, and the Name property below that shows 'comp2'. I tried both leaving the "(Name)" line item and "Name" what they were originally and setting them to comp2, and nothing works. Here's are some images of the properties for both the comp1 sheet - which works, and the comp2 sheet which doesn't work:
Sheet19 (comp1) - working macro Sheet20/comp2 (tried both names) - macro doesn't work
VBA Project View - not sure if anything looks w
I basically have 1 macro that clears the contents of a specific range, then the macro that doesn't work is supposed to fill that section of the worksheet with data based on its calculations. I've tried everything I can think of:
*Changing the scope of the sub - private, public...neither works
*I've tried selecting the sheet based on the button pushed, by giving it the worksheet name, and manually selecting the worksheet before I run the macro.
*I've tried running the macro from a standard form button and an activeX form button
*I've tried deleting the button, and copying the code word-for-word from comp1 into a new macro...which still doesn't work.
*I've tried copying and pasting the working code from comp1 directly into the VBA objects - which are the worksheets.
*I've tried ignorning any errors the code might through off and move on to the next line, and nothing.
*I've tried running the macro from the macro menu rather than a button and nothing.
So sick of dealing with this issue, so anyone willing to lend a hand....you're my hero! Let me know if you need any other code and I'll gladly provide it. Thanks!
Here's the code to the primary module:
Here's the full code from the Microsoft Excel Objects folder - comp2
I have one primary worksheet that I'll make changes to - "comp1". I'll then make right click on "comp1" and produce 3 copies which shows up as comp1 (2), comp1 (3), and comp1 (4). I'll then rename them to comp2, comp3, and comp4. I'm not sure if it's relevant, but these sheets have been repeatedly deleted with comp2-comp4 being replaced after I make updates to comp1. The reason I mention it is I 'think' it's a referencing error, but I'm not sure how to test that theory. So, although the name of each worksheet is comp2, the properties tab shows (Name) and it's set to Sheet20 or whatever sheet it's at, and the Name property below that shows 'comp2'. I tried both leaving the "(Name)" line item and "Name" what they were originally and setting them to comp2, and nothing works. Here's are some images of the properties for both the comp1 sheet - which works, and the comp2 sheet which doesn't work:
Sheet19 (comp1) - working macro Sheet20/comp2 (tried both names) - macro doesn't work
VBA Project View - not sure if anything looks w
I basically have 1 macro that clears the contents of a specific range, then the macro that doesn't work is supposed to fill that section of the worksheet with data based on its calculations. I've tried everything I can think of:
*Changing the scope of the sub - private, public...neither works
*I've tried selecting the sheet based on the button pushed, by giving it the worksheet name, and manually selecting the worksheet before I run the macro.
*I've tried running the macro from a standard form button and an activeX form button
*I've tried deleting the button, and copying the code word-for-word from comp1 into a new macro...which still doesn't work.
*I've tried copying and pasting the working code from comp1 directly into the VBA objects - which are the worksheets.
*I've tried ignorning any errors the code might through off and move on to the next line, and nothing.
*I've tried running the macro from the macro menu rather than a button and nothing.
So sick of dealing with this issue, so anyone willing to lend a hand....you're my hero! Let me know if you need any other code and I'll gladly provide it. Thanks!
Here's the code to the primary module:
Code:
Sub populateGrid2()Range("E5").Select
On Error Resume Next
Dim rng As Range, cell As Range
Dim targetCell As Double
Dim actThr As Double
Dim slopeCount, openCount, closeCount, maxAct As Double
Dim openSlope As Double
Dim openForecast1, openForecast2, openForecast3, openForecast4 As Double
Dim openAvg1, openAvg2, openAvg3 As Double
Dim closeAvg1, closeAvg2, closeAvg3 As Double
actThr = Range("AC11").Value
Set rng = Range("E4:W10")
For Each cell In rng
If (cell > actThr) Then
If (cell.Offset(0, -1).Value = "") Then
cell.Offset(11, 0).Value = cell.Value
openSlope = ((cell.Offset(0, 2).Value - cell.Offset(0, 1).Value) + (cell.Offset(0, 1).Value - cell.Value)) / 2
If openSlope < 5 Then
openSlope = 12.5
End If
openForecast1 = cell.Value - openSlope
If openForecast1 > actThr Then
cell.Offset(11, -1).Value = openForecast1
End If
If openForecast1 > actThr Then
openSlope = ((cell.Offset(0, 1).Value - cell) + (cell.Value - openForecast1)) / 2
openForecast2 = openForecast1 - openSlope
If openForecast2 > actThr Then
cell.Offset(11, -2).Value = openForecast2
End If
If openForecast2 > actThr Then
openSlope = ((cell - openForecast1) + (openForecast1 - openForecast2)) / 2
openForecast3 = openForecast2 - openSlope
If openForecast3 > actThr Then
cell.Offset(11, -3).Value = openForecast3
End If
If openForecast3 > actThr Then
openSlope = ((openForecast1 - openForecast2) + (openForecast2 - openForecast3)) / 2
openForecast4 = openForecast3 - openSlope
If openForecast4 > actThr Then
cell.Offset(11, -4).Value = openForecast4
End If
End If
End If
End If
ElseIf (cell.Offset(0, 1).Value = "") Then
cell.Offset(11, 0).Value = cell.Value
closeSlope = ((cell.Offset(0, -2).Value - cell.Offset(0, -1).Value) + (cell.Offset(0, -1).Value - cell.Value)) / 2
If closeSlope < 5 Then
closeSlope = 12.5
End If
closeForecast1 = cell.Value - closeSlope
If closeForecast1 > actThr Then
cell.Offset(11, 1).Value = closeForecast1
End If
If closeForecast1 > actThr Then
closeSlope = ((cell.Offset(0, -1).Value - cell) + (cell.Value - closeForecast1)) / 2
closeForecast2 = closeForecast1 - closeSlope
If closeForecast2 > actThr Then
cell.Offset(11, 2).Value = closeForecast2
End If
If closeForecast2 > actThr Then
closeSlope = ((cell - closeForecast1) + (closeForecast1 - closeForecast2)) / 2
closeForecast3 = closeForecast2 - closeSlope
If closeForecast3 > actThr Then
cell.Offset(11, 3).Value = closeForecast3
End If
If closeForecast3 > actThr Then
closeSlope = ((closeForecast1 - closeForecast2) + (closeForecast2 - closeForecast3)) / 2
closeForecast4 = closeForecast3 - closeSlope
If closeForecast4 > actThr Then
cell.Offset(11, 4).Value = closeForecast4
End If
End If
End If
End If
End If
End If
Next cell
End Sub
Here's the full code from the Microsoft Excel Objects folder - comp2
Code:
Sub picToFront1_click()
ActiveSheet.Pictures("fullsize1").Select
Selection.ShapeRange.ZOrder (msoBringToFront)
End Sub
Sub picToFront2_click()
ActiveSheet.Pictures("fullsize2").Select
Selection.ShapeRange.ZOrder (msoBringToFront)
End Sub
Sub picToFront3_click()
ActiveSheet.Pictures("fullsize3").Select
Selection.ShapeRange.ZOrder (msoBringToFront)
End Sub
Sub picToFront4_click()
ActiveSheet.Pictures("fullsize4").Select
Selection.ShapeRange.ZOrder (msoBringToFront)
End Sub
Sub picToFront5_click()
ActiveSheet.Pictures("fullsize5").Select
Selection.ShapeRange.ZOrder (msoBringToFront)
End Sub
Sub goToWorksheet()
Dim ButtonName
ButtonName = Application.Caller
Debug.Print (ButtonName)
End Sub
Sub populateGrid()
Dim rng As Range, cell As Range
Dim targetCell As Double
Dim actThr As Double
Dim slopeCount, openCount, closeCount, maxAct As Double
Dim openSlope As Double
Dim openForecast1, openForecast2, openForecast3, openForecast4 As Double
Dim openAvg1, openAvg2, openAvg3 As Double
Dim closeAvg1, closeAvg2, closeAvg3 As Double
'Future Use: Dim uncapturedOpp As Double
actThr = Range("AC11").Value
Set rng = Range("E4:W10")
uncapturedOpp = 0
For Each cell In rng
If (cell > actThr) Then
If (cell.Offset(0, -1).Value = "") Then
cell.Offset(11, 0).Value = cell.Value
openSlope = ((cell.Offset(0, 2).Value - cell.Offset(0, 1).Value) + (cell.Offset(0, 1).Value - cell.Value)) / 2
If openSlope < 5 Then
openSlope = 12.5
End If
openForecast1 = cell.Value - openSlope
If openForecast1 > actThr Then
cell.Offset(11, -1).Value = openForecast1
End If
If openForecast1 > actThr Then
openSlope = ((cell.Offset(0, 1).Value - cell) + (cell.Value - openForecast1)) / 2
openForecast2 = openForecast1 - openSlope
If openForecast2 > actThr Then
cell.Offset(11, -2).Value = openForecast2
End If
If openForecast2 > actThr Then
openSlope = ((cell - openForecast1) + (openForecast1 - openForecast2)) / 2
openForecast3 = openForecast2 - openSlope
If openForecast3 > actThr Then
cell.Offset(11, -3).Value = openForecast3
End If
If openForecast3 > actThr Then
openSlope = ((openForecast1 - openForecast2) + (openForecast2 - openForecast3)) / 2
openForecast4 = openForecast3 - openSlope
If openForecast4 > actThr Then
cell.Offset(11, -4).Value = openForecast4
End If
End If
End If
End If
ElseIf (cell.Offset(0, 1).Value = "") Then
cell.Offset(11, 0).Value = cell.Value
closeSlope = ((cell.Offset(0, -2).Value - cell.Offset(0, -1).Value) + (cell.Offset(0, -1).Value - cell.Value)) / 2
If closeSlope < 5 Then
closeSlope = 12.5
End If
closeForecast1 = cell.Value - closeSlope
If closeForecast1 > actThr Then
cell.Offset(11, 1).Value = closeForecast1
End If
If closeForecast1 > actThr Then
closeSlope = ((cell.Offset(0, -1).Value - cell) + (cell.Value - closeForecast1)) / 2
closeForecast2 = closeForecast1 - closeSlope
If closeForecast2 > actThr Then
cell.Offset(11, 2).Value = closeForecast2
End If
If closeForecast2 > actThr Then
closeSlope = ((cell - closeForecast1) + (closeForecast1 - closeForecast2)) / 2
closeForecast3 = closeForecast2 - closeSlope
If closeForecast3 > actThr Then
cell.Offset(11, 3).Value = closeForecast3
End If
If closeForecast3 > actThr Then
closeSlope = ((closeForecast1 - closeForecast2) + (closeForecast2 - closeForecast3)) / 2
closeForecast4 = closeForecast3 - closeSlope
If closeForecast4 > actThr Then
cell.Offset(11, 4).Value = closeForecast4
End If
End If
End If
End If
End If
End If
Next cell
End Sub