locked
Excel No Responding RRS feed

  • Question

  • Dear Sir:

    I made the following procedure:

    Sub GetConcentrationData()
        Dim sht1 As Worksheet, sht As Worksheet
        Dim i As Long, j As Long, rowCount As Long, rowCount1 As Long
           
        Set sht = ThisWorkbook.Sheets(1)
        rowCount = sht.UsedRange.Rows.Count
       
        Set sht1 = ThisWorkbook.Worksheets.Add
        With sht1
            .Name = "ConcentrationData"
        End With
        sht1.Range("A1") = "Time"
        sht1.Range("B1") = "Area No"
        sht1.Range("C1") = "Gas No"
        sht1.Range("D1") = "Concentration"
        rowCount1 = sht1.UsedRange.Rows.Count
       
        Application.ScreenUpdating = False
        For i = 1 To rowCount
            If sht.UsedRange.Columns(7).Rows(i) = "concentration" Then
               
                sht1.Range("A" & rowCount1 + 1) = sht.UsedRange.Cells(i, 1)
                sht1.Range("B" & rowCount1 + 1) = sht.UsedRange.Cells(i, 4)
                sht1.Range("C" & rowCount1 + 1) = sht.UsedRange.Cells(i, 6)
                sht1.Range("D" & rowCount1 + 1) = sht.UsedRange.Cells(i, 9)
               
                rowCount1 = rowCount1 + 1
            End If
        Next i

    End Sub

    When I debuged it, it ran without any problem. But when I ran this macro, the excel has no responding. Would you please help see where the problem of my procedure is and how to solve this problem?

    Your support is highly appreciated!

    Regards,

    Shell Hu

    Tuesday, April 11, 2017 4:49 AM

Answers

  • Dear Sir:

    I revised my sub a little as following:

    Sub GetConcentrationData()
        Dim sht1 As Worksheet, sht As Worksheet
        Dim i As Long, j As Long, rowCount As Long, rowCount1 As Long    
        Set sht = Sheets("Sheet1")
        rowCount = sht.UsedRange.Rows.Count    
        Set sht1 = Sheets.Add
        With sht1
            .Name = "ConcentrationData"
        End With
        sht1.Range("A1") = "Time"
        sht1.Range("B1") = "Area No"
        sht1.Range("C1") = "Gas No"
        sht1.Range("D1") = "Concentration"
        rowCount1 = sht1.UsedRange.Rows.Count   
        For i = 1 To rowCount
            If sht.Cells(i, 7) = "concentration" Then
                sht1.Range("A" & rowCount1 + 1) = sht.Cells(i, 1)
                sht1.Range("B" & rowCount1 + 1) = sht.Cells(i, 4)
                sht1.Range("C" & rowCount1 + 1) = sht.Cells(i, 6)
                sht1.Range("D" & rowCount1 + 1) = sht.Cells(i, 9)            
                rowCount1 = rowCount1 + 1
            End If
        Next i
    End Sub

    Now this sub works fine for me.

    Regards,

    Shell Hu

    • Marked as answer by Shell Hu Wednesday, April 12, 2017 9:59 AM
    Wednesday, April 12, 2017 9:58 AM

All replies

  • Hi Shell Hu,

    How did you execute Sub "GetConcentrationData"? 

    I don't know what you want to do, but your code works fine.
    I made a button on Sheet1. The button.Click calls your Sub "GetConcentrationData".

    Private Sub btn_GetConcentrationData_Click()
        Dim i As Integer
        For i = 1 To Worksheets.Count
            If (Worksheets(i).Name = "ConcentrationData") Then
                MsgBox "worksheet named [ConcentrationData] exists already"
                Exit Sub
            End If
        Next
        ' ---
        Call GetConcentrationData
    End Sub
    
    Sub GetConcentrationData()
        Dim sht1 As Worksheet, sht As Worksheet
        Dim i As Long, j As Long, rowCount As Long, rowCount1 As Long
    ....
    End Sub
    ______________
    Ashidacchi
    • Edited by Ashidacchi Tuesday, April 11, 2017 5:54 AM
    Tuesday, April 11, 2017 5:52 AM
  • Dear Sir:

    I just want to copy some of the data into a new worksheet. I call this sub on the active sheet with existing data.

    Regards,

    Shell Hu

    Tuesday, April 11, 2017 6:04 AM
  • Dear Shell Hu,

    I want to know the meaning of "Excel No Responding".
    I could make your code executed, and your code worked. So, I asked how you did to execute it.
    Could you explain in detail about "I call this sub on the active sheet"? 

    Regards,
    Hideki Ashida


    • Edited by Ashidacchi Tuesday, April 11, 2017 6:23 AM
    Tuesday, April 11, 2017 6:10 AM
  • Dear Sir:

    Maybe I'm not describing the issue clearly. When I call this sub, the excel seems stopped running. It shows "no responding" on the top of excel title. (I just called the sub like you described)

    Regards,

    Shell Hu

    Tuesday, April 11, 2017 6:31 AM
  • Thank you for explanation.
    I understand it and another question hit on me.
    What version/build of Excel and Windows are you using?

    I've shared an Excel file including your code via Dropbox.
    Please download and try it.
    https://www.dropbox.com/s/q3m0k2yzp0nvbmd/Excel%20GetConcentrationData.xlsm?dl=0

    # before click button [GetConcentrationData], delete the first sheet [ConcentrationData].
    ____________
    Ashidacchi 

    • Edited by Ashidacchi Tuesday, April 11, 2017 7:10 AM
    Tuesday, April 11, 2017 6:39 AM
  • Dear Sir:

    I'm using excel2016 and windows10.

    Regards,

    Shell Hu

    Tuesday, April 11, 2017 7:07 AM
  • Hi,

    Oh! It's the same as my environment.
    Could you download a shared file?
    ___________
    Ashidacchi
    Tuesday, April 11, 2017 7:09 AM
  • Dear Sir:

    I cannot reach the dropbox you provided.

    Regards,

    Shell Hu

    Tuesday, April 11, 2017 7:31 AM
  • Hello Shell,

    Do you use the same data when debugging and running?

    The macro works fine for me too. I think the issue casues from your data. Please create a new workbook with little data to check if the issue still exists. If we could narrow down the issue does cause from your current data, I suggest you share your file here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 12, 2017 5:46 AM
  • Hello Shell,

    I sent you an email with one attached file yesterday, but I could hear nothing from you. What's wrong?
    _____________
    Ashidacchi
    • Edited by Ashidacchi Wednesday, April 12, 2017 7:08 AM
    Wednesday, April 12, 2017 7:00 AM
  • Dear Sir:

    I revised my sub a little as following:

    Sub GetConcentrationData()
        Dim sht1 As Worksheet, sht As Worksheet
        Dim i As Long, j As Long, rowCount As Long, rowCount1 As Long    
        Set sht = Sheets("Sheet1")
        rowCount = sht.UsedRange.Rows.Count    
        Set sht1 = Sheets.Add
        With sht1
            .Name = "ConcentrationData"
        End With
        sht1.Range("A1") = "Time"
        sht1.Range("B1") = "Area No"
        sht1.Range("C1") = "Gas No"
        sht1.Range("D1") = "Concentration"
        rowCount1 = sht1.UsedRange.Rows.Count   
        For i = 1 To rowCount
            If sht.Cells(i, 7) = "concentration" Then
                sht1.Range("A" & rowCount1 + 1) = sht.Cells(i, 1)
                sht1.Range("B" & rowCount1 + 1) = sht.Cells(i, 4)
                sht1.Range("C" & rowCount1 + 1) = sht.Cells(i, 6)
                sht1.Range("D" & rowCount1 + 1) = sht.Cells(i, 9)            
                rowCount1 = rowCount1 + 1
            End If
        Next i
    End Sub

    Now this sub works fine for me.

    Regards,

    Shell Hu

    • Marked as answer by Shell Hu Wednesday, April 12, 2017 9:59 AM
    Wednesday, April 12, 2017 9:58 AM
  • Hi Shell Hu,

    Congratulations!  I'm happy to read your post.

    But, the code you showed in your first post (thread) could work fine in my environment.  I have no time to compare the latest and the first, so I want to know the difference between two, including how to call sub "GetConcentrationData".
    _________________
    Hideki Ashida
    • Edited by Ashidacchi Wednesday, April 12, 2017 10:26 AM
    Wednesday, April 12, 2017 10:26 AM