Solved

Problem with CountBlank code

Posted on 2004-10-09
5
278 Views
Last Modified: 2008-02-01
Hi,

I have the following code, and the first time it runs, everything runs perfect.  However, the second time I run it, I get an error at the WorksheetFunction.CountBlank part of the code.  Can anyone suggest something.

BTW; I've change CountBlank to CountIF but still had no luck.  I've also rest the value of i = 0, still no luck.

Thanks for the help.

'-----------------------------------------------------------------------
    strLBLSearch = "Appendix 1"

    Set wb = xl.Workbooks.Open(App.Path & "\Configuration\Technical.xls")
    Set ws = wb.Worksheets(strLBLSearch)

    'Loop through rows and list number of used cells per row
    For r = 1 To ws.Cells.SpecialCells(xlCellTypeLastCell).Row
        Next r
   
    lngHold = 0
   
    strRange = "A1:" & "A" & (r - 1)

    'counts the number of blank cells within a specified range
    For i = 1 To r
        intNumberMissing = WorksheetFunction.CountBlank(Range(strRange))   '<---------- PROBLEM IS HERE
        'intNumberMissing = WorksheetFunction.CountIf(Range(strRange), "")   '<---------- TRIED THIS TO, doesn't work
    Next

'-----------------------------------------------------------------------
0
Comment
Question by:eciabattari
  • 2
  • 2
5 Comments
 
LVL 15

Assisted Solution

by:Colosseo
Colosseo earned 150 total points
ID: 12269374
Hi there

two questions

1. What is the error that you are getting?

2. Is it the same section of code that is calling WorksheetFunction.CountBlank(Range(strRange)) the second time?

Cheers

Scott
0
 
LVL 9

Accepted Solution

by:
Shahid Thaika earned 350 total points
ID: 12269428
Hi eciabattri,
It would have been better if you would have posted more of the code. I tried doing something with whatever info you have given. Can't find the problem however.
Try declaring all the variables in advance. Also after the final loop, before 'End Sub', enter the code for closing the workbook and the excel application. Below is the code I experimented with. It is modified for my computer. Hope this info helps you. But please give some more info if needed.

Dim x1 As New Excel.Application
Dim ws As Excel.Worksheet
Dim wb As New Excel.Workbook

Private Sub Command1_Click()
cb
End Sub

Private Sub cb()
Dim intNumberMissing As Integer
strLBLSearch = "Employee Info"
   
    Set wb = x1.Workbooks.Open("C:\Program Files\Microsoft Office\Office\Library\COMMON.XLS")
    Set ws = wb.Worksheets(strLBLSearch)

    'Loop through rows and list number of used cells per row
    For r = 1 To ws.Cells.SpecialCells(xlCellTypeLastCell).Row
        DoEvents: DoEvents: DoEvents
    Next r
   
    lngHold = 0
   
    strRange = "A1:" & "A" & (r - 1)

    'counts the number of blank cells within a specified range
    For i = 1 To r
        intNumberMissing = WorksheetFunction.CountBlank(Range(strRange))   '<---------- PROBLEM IS HERE
        'intNumberMissing = WorksheetFunction.CountIf(Range(strRange), "")   '<---------- TRIED THIS TO, doesn't work
    Next i
   
wb.Close
x1.Quit
End Sub
0
 

Author Comment

by:eciabattari
ID: 12269464
Scott:
1.  I'm not getting any errors, the program just stops and nothing else happens.
2.  Yes, the same code is calling it.

eeshahidt:
I declared Dim intNumberMissing As Integer as a global; Public intNumberMissing As Integer
I did close out and quit the application.

here is the complete code

Private Sub SearchTech()
      strLBLSearch = "Appendix 1"
      Call Extent
End Sub

'-----------------------------------------------------
Function ExtendTechList()

    Dim strCells As Long
    Dim strCells2 As Long
    Dim lngCols2 As Long
    Dim lngHold As Long

    'count number of empty cells
    Dim strColumn As String
    Dim strRange As String

    Set wb = xl.Workbooks.Open(App.Path & "\Configuration\Technical.xls")
    Set ws = wb.Worksheets(strLBLSearch)
    lngHold = 0
   
    'Loop through rows and list number of used cells per row
    For r = 1 To ws.Cells.SpecialCells(xlCellTypeLastCell).Row
       DoEvents:
        Next r
   
    strRange = "A1:" & "A" & (r - 1)

    'counts the number of blank cells within a specified range
    For i = 1 To r
        intNumberMissing = WorksheetFunction.CountBlank(Range(strRange))  '<---------- PROBLEM IS HERE,
                                                                                                              ' JUST STOPS AND NOTHING ELSE HAPPENS
    Next

    strCells = r - intNumberMissing
    strCells2 = r + intNumberMissing
   
    'loop that gets data
    For r = 1 To strCells2
        x = ws.Cells(r, 1)
        y = ws.Cells(r, 2)
        w = ws.Cells(r, 3)

        r2 = r - lngHold

        If x = "Section" Then
            'no nothing
        ElseIf x = "" Then
            'do nothing
            lngHold = lngHold + 1
        Else
            'determins how many rows & pages to put in
            If strCells > 1 And strCells <= 25 Then
                Call Show2TechItems25
            ElseIf strCells > 27 And strCells <= 52 Then
                Call Show2TechItems50
            ElseIf strCells > 52 And strCells <= 77 Then
                Call Show2TechItems75
            ElseIf strCells > 77 And strCells <= 102 Then
                Call Show2TechItems100
            End If
        End If
    Next r

    'cleanup
    wb.Application.Workbooks(1).Save
    wb.Close
    xl.Quit
   
    Set wb = Nothing
    Set xl = Nothing

End Function
'-------------------------------------------------------------------------
0
 

Author Comment

by:eciabattari
ID: 12269534
Found the problem, is the modules, I had Set xl = Nothing and I should have written Set ws = Nothing.

Once I made the change, everything worked.

Thanks for the help.
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12269621
The cleaning up did the trick huh! Well that's great. Glad to assist you :).
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

709 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now