Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with CountBlank code

Posted on 2004-10-09
5
Medium Priority
?
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

705 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