What's wrong with my code? (cells reference and calculations)

[billatosu]

Hi, the following code runs OK from within a simple macro. But when run triggered from a command button click, it hangs at the line in Bold font below. Can anyone tell me what is wrong? The purpose of this code is to replace certain rows (row 16 until last row) of each column with a new date value. It also has a progress bar. Thank you!

For n = 1 To Round(LastColumn / 2)
Columns(2 * n - 1).Select
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:= _
xlByRows, SearchDirection:=xlPrevious).Row
For i = 16 To LastRow - 1
Cells(i, 2 * (n - 1) + 1) = Cells(10, 2 * (n - 1) + 2) _
+ (i - 16) * Cells(13, 2 * (n - 1) + 2) / 24 / 60 / 60
Next
If LastRow > MaxRow Then
MaxRow = LastRow
End If
PctDone = n / LastColumn
With UserForm1
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
DoEvents
Next

 

[wigi]

Where in the VBA-code of the Excel file do you have that code?
Can you post the full code for your button?
Please wrap the codein

 tags (the # icon) such that it is formatted. In your previous post, you did not do that.

 

[billatosu]

Where in the VBA-code of the Excel file do you have that code?
Can you post the full code for your button?
Please wrap the codein

 tags (the # icon) such that it is formatted. In your previous post, you did not do that.[/QUOTE]

This is embedded in a 2010 Excel macro. I am not sure how to wrap the code but I added the # icons below. Thank you!

[CODE]
Sub Main()

    Application.ScreenUpdating = True
    
    Dim LastRow As Long, LastColumn As Long, DataRow As Long
    Dim MaxRow As Long, CutRowPosition As Long, PctDone As Long
    Dim xTitle As Range, xData As Range, yColumn As Long
    Dim yTitle As Range, yData As Range, GraphRange As Range
    
    Rows(8).Select
    LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:= _
    xlByColumns, SearchDirection:=xlPrevious).Column
    
 
    For n = 1 To Round(LastColumn / 2)
            Columns(2 * n - 1).Select
            LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:= _
            xlByRows, SearchDirection:=xlPrevious).Row
        For i = 16 To LastRow - 1
            Cells(i, 2 * (n - 1) + 1) = Cells(10, 2 * (n - 1) + 2) _
            + (i - 16) * Cells(13, 2 * (n - 1) + 2) / 24 / 60 / 60
        Next
        If LastRow > MaxRow Then
        MaxRow = LastRow
        End If
        PctDone = n / LastColumn
        With UserForm1
            .FrameProgress.Caption = Format(PctDone, "0%")
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
        DoEvents
    Next
' Cut and paste all columns into one
    CutRowPosition = 16
        
    For m = 1 To Round(LastColumn / 2)
        
        Columns(2 * m).Select
        LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:= _
        xlByRows, SearchDirection:=xlPrevious).Row
        DataRow = LastRow - CutRowPosition
        Cells(CutRowPosition, 3).Resize(CutRowPosition + MaxRow, LastColumn).Cut _
        Destination:=Cells(CutRowPosition + DataRow, 1)
        CutRowPosition = CutRowPosition + DataRow
        
        PctDone = m / LastColumn + 0.5
        With UserForm1
            .FrameProgress.Caption = Format(PctDone, "0%")
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
        DoEvents
    Next
    
    Unload UserForm1
    
Application.ScreenUpdating = True

End Sub

 

[wigi]

So, this Sub Main works fine, but if you use a Command button and put Main in between the 2 lines of code for that button, it does not work?
Is that correct?

 

[billatosu]

Exactly. Also, when the code hangs, the userform unloads by itself and there is no error message. By stepping through the code, I was able to determine that the cells calculation step (bold in original post) is what's causing the problem

So, this Sub Main works fine, but if you use a Command button and put Main in between the 2 lines of code for that button, it does not work?
Is that correct?

 

[wigi]

I'm sorry, but it's hard to believe executing a from a userform causes Excel to hang, whereas the same macro executed outside of a userform, works correctly.
Then the only option you have is optimizing the macro (which is what you should always do, by the way).
Minimize the interaction with the sheet(s) and work as much as you can in memory (arrays for example).

 

[Jonmo1]

You probably need to qualify a Sheet Reference for the Cells

something like

Dim ws As Worksheet
Set ws = Sheets("Sheet1") 'Adjust for correct sheet name here.
ws.Cells(i, 2 * (n - 1) + 1) = ws.Cells(10, 2 * (n - 1) + 2) _
+ (i - 16) * ws.Cells(13, 2 * (n - 1) + 2) / 24 / 60 / 60

 

[billatosu]

Thank you Wigi and Jonmo1.

I tried setting the sheet as well as making the sheet reference explicit (see below), neither solved the problem.

ActiveSheet.Cells(i, 2 * (n - 1) + 1) = ActiveSheet.Cells(10, 2 * (n - 1) + 2) _
            + (i - 16) * ActiveSheet.Cells(13, 2 * (n - 1) + 2) / 24 / 60 / 60

This macro is stored in an Excel macro-enabled file and I run it from another Excel data file where I need to use the macro to process my data. I can't think of this inter-Excel file call of macro as the source of the problem. Any other ideas?

 

[billatosu]

I think I found out why. The code runs perfectly if I copy data into the same macro-enabled file and run everything within the same file. If I try to run the macro from another data file, it will hang. I wonder if I can find a way to run the macro from another file and avoid the problem.

 

[wigi]

Hello

The data file that contains the macro, is it a heavy file with lots of calculations?
By default Excel will recalculate all sheets in all files within the same Excel application.
That might be a reason why you see the problems mentioned.