Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem with CountBlank code

Posted on 2004-10-09
5
Medium Priority
?
294 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 600 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 1400 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

963 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