Reoccurring VBA macro code not working on cloned worksheets in same spreadsheet

J

jdlev

New Member
Joined
Jan 6, 2017
Messages
27
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


28109869329_4318d20b05.jpg
25018541697_0c09ecefd7.jpg


VBA Project View - not sure if anything looks w

39857662612_c47f9edcc1.jpg


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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
All your code simply refers to the active sheet, or the sheet containing the code - it isn't clear where the code is actually located. There are no sheet names in use that I can see.
 
Upvote 0
Hi Rory,
That's what I thought too, but I tried a few different bits of code to activate the sheet I wanted (though I suppose I could have had bug in the code). I tried:

Sheets("comp2").Select
Sheets("comp2").Activate
ActiveSheet("comp2").Select
Worksheets("comp2").Select

Even tried naming the buttons the same as the worksheet I was looking for and pulling in the name of the button to make sure it would go to the right sheet, but couldn't get anything to work.

Is there a way to check the references in the VBA code? What I mean is, are there inferred references behind the scenes that aren't written directly into the code? I'm guessing that's why I've never had any issues w/ 'comp1' working correctly every time, since that's probably the sheet that was active when I originally wrote the code.

One other question I had for you that I thought of is since the button tied to the macro is on the sheet it's supposed to run the code on, wouldn't that activate that sheet by default (unless another sheet were directly referenced in the code)?
 
Last edited:
Upvote 0
One other quick thing Rory, I'm not sure I'm editing the right code, so I've just been trying editing both. The same sub was located in both the Microsoft Excel Object list (the top folder in the VBA project section on the navigator), and it was also include in Module2 under the modules section. Maybe having the same sub in two different places is causing a conflict?

Anyways, which should I be editing when building VBA code? The Microsoft Excel Object specific to a sheet or the modules?
 
Upvote 0
There's got to be a bug in the code somewhere. I added Worksheets("comp2").Activate, and swapped in comp1, comp3, and comp4...and the macro would take you to the page and then quit. When I run the script from worksheet comp1 on comp1, it runs the complete table like normal. If I activate comp1 from comp2, it jumps to comp1 from comp2 no problem, but then doesn't fill in the table. It does the same thing when the pages are reversed.
 
Upvote 0
I finally figured it out!!!!!! And found out I'm an idiot! :(

You know what it was? There wasn't anything wrong with the code (so you were right...it was clean). The code that populated the original grid upon which the second macro was running wasn't filling in NULL values for certain cells - it was putting in ZEROES! :mad: So when I finally figured out how to properly debug the code by using Print.Debug(cell) - I noticed it was skipping an if/then statement it should be working down...which is how I found out that excel doesn't consider "0" the same as "".

Hard way to learn a lesson, but glad I figured it out (FINALLY!) Only took 2 days finding that needle in a haystack. Thanks again Rory! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Combine two codes in one worksheet change events
Replies
3
Views
247
Alex Blakenburg
A
Agnarr
  • Solved
Checkbox Timestamp with VBA
Replies
3
Views
179
bobsan42
bobsan42
The Ruff Report
  • Question
Add multiple attachments from email list
Replies
1
Views
260
Trebor76
T
The Ruff Report
  • Question
Help adjusting Macro to email multiple cc addresses
Replies
1
Views
357
The Ruff Report
The Ruff Report
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
335
Agnarr
Agnarr

Forum statistics

Threads
1,214,430
Messages
6,119,443
Members
448,898
Latest member
drewmorgan128

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top