Solved

Excel Find Next & Count Rows

Posted on 2004-10-11
12
318 Views
Last Modified: 2010-05-02
Hi - need som help,  I'm trying to calculate the number of cells that are blank between two fields.  

The code below does the following:
  1.  strLBLSearch = General
  2.  lngFirst = 1

Trying to figure our how to get A5 cell value.

A1 = General
A2 =
A3 =
A4 =
A5 = Commercial
A6 = Electrical
A7 =
A8 = Business

I know that I can use the following to calculate the number of blank cells within a range:
   strRange = "A" & lngFirst & ":" & "A" & lngSecond
   intNumberMissing = WorksheetFunction.CountBlank(Range(strRange))

'------------------------------------------------------------------
Dim lngFirst, lngSecond As Long

    For r = 1 To strCells
        x = ws.Cells(r, 1)
        y = ws.Cells(r, 2)
        w = ws.Cells(r, 3)
   
        If strLBLSearch = x Then
            lngFirst = r
        End If
       
    Next r
'--------------------------------------------------------------
0
Comment
Question by:eciabattari
  • 6
  • 3
  • 3
12 Comments
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12280568
Hi eciabattari. Looks like you are working on a big Excel project. Do you want to look for blank cells row wise or column wise.
0
 

Author Comment

by:eciabattari
ID: 12280626
Yes - I'm working on a big Excel project.  So far, most of the times I can figure things out; however, sometimes I just draw a complete blank.

I'm trying to count by row.

Thanks for all the help.
0
 

Author Comment

by:eciabattari
ID: 12280637
I really want to know what the next row value is.

Example:
A1 = General         <---------------------- returns row value 1
A2 =
A3 =
A4 =
A5 = Commercial  <------------------------- I want to get this value, should be 5
A6 = Electrical
A7 =
A8 = Business

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12280925
Give me some time. working on it :)
0
 

Author Comment

by:eciabattari
ID: 12281041
Figured out a way to do it but it probably isn't the correct way.  Any suggestions on improving would be great.  Thanks


'----------------------------------------------------------------
    'loop that gets data
    For r = 1 To strCells
        p = strLBLRowValue + 1
        x = ws.Cells(r, 1)
        y = ws.Cells(r, 2)
        w = ws.Cells(p, 3)
   
        If strLBLSearch = x Then
            lngFirst = r
            GoTo nextLoop
        End If
    Next r

nextLoop:
   
        For r = lngFirst + 1 To strCells
            p = strLBLRowValue + 1
            x = ws.Cells(r, 1)
            y = ws.Cells(r, 2)
            w = ws.Cells(p, 3)
           
                Do While x <> ""
                    lngSecond = r
                    GoTo nextLoop2
                Loop
               
        Next r

nextLoop2:
    Debug.Print lngFirst & " " & lngSecond

    strRange = "A" & lngFirst & ":" & "A" & lngSecond
   
    Debug.Print strRange
                   
    For i = 1 To strCells
        intNumberMissing = WorksheetFunction.CountBlank(Range(strRange))
    Next
                   
    Debug.Print intNumberMissing
'----------------------------------------------------------------
       
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12281449
I was offline for sometime. Haven't gone through your solution, but here is my sample code. I think it works fine. You need two text boxes... text1 & text2 and a command button command1.



Dim x As New Excel.Application
Dim ew As Workbook
Dim ewk As Worksheet
Dim r As Integer
Dim c As Integer


Private Sub Command1_Click()
Dim intNumberMissing As Integer
Dim n As Integer

n = 1

For c = Text2 To ewk.Cells.SpecialCells(xlCellTypeLastCell).Column
    'to check if first row is empty
    intNumberMissing = WorksheetFunction.CountBlank(Range(ColumnLetter(Text2) & Text1 & ":" & ColumnLetter(Text2) & Text1 + 1))
    If intNumberMissing > 1 Then
        Debug.Assert False
        MsgBox "There are " & intNumberMissing & " cells missing starting from " & ColumnLetter(Text2) & Text1
        Text1 = intNumberMissing + 1
    End If
   
    For r = Text1 To ewk.Cells.SpecialCells(xlCellTypeLastCell).Row - 1
        intNumberMissing = WorksheetFunction.CountBlank(Range(ColumnLetter(c) & Text1 & ":" & ColumnLetter(c) & (r + n)))
        'If all  cells in between two cells are blank then the difference will equal the number of blank cells
        'but if the difference is greater than the blank cell count, it means one of the cells is not blank
        'since we are incrementing by one, the latest cell is not blank.
       
        If ((r + n) - Text1) > intNumberMissing Then
            MsgBox "There are " & intNumberMissing & " cells missing from " & ColumnLetter(Text2) & Text1 & " to " & ColumnLetter(Text2) & (Text1 + intNumberMissing + 1)
            Text1 = (r + n)
            r = Text1 - 1
            n = 0
        End If
        n = n + 1
    Next r
    Text2 = c + 1
    Text1 = 1
Next c
End Sub

Private Sub Form_Load()
Set ew = x.Workbooks.Open("C:\Program Files\Microsoft Office\Office\Library\COMMON.XLS")
Set ewk = ew.Worksheets("Employee Info")
End Sub


Private Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function


Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
ew.Close

Set ew = Nothing
Set ewk = Nothing

x.Quit
Set x = Nothing
End Sub
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12281559
It took some time to figure out the mathematics and other problems, but finally cracked it :). What I have done is taken one cell, column wise. I loop downwards (by row) and check if the next cell is blank. If it is not, I display the accumulated blank count.

A1 - something
A2 - Blank
A3 - something

Here's how I did it.

1. Take A1 as the initial cell.
2. see the blank cells in the range A1:A2
3. It will be 1.
4. See the value for 2-1 = 1 (How many rows in range)
5. Compare with the value of blank = 1
6. Since it is the same... continue.
7. Do the same for the next (A3)
8. But only 1 blank between 3 & 1, but difference of 2 between 3 & 1.
    Hence, one of the cells between A1 - A3 is not blank. And since, we are checking one by one, it is obvious that the latest cell checked is not blank. Hence we can conclude the solution. :)


Hope this answers your solution. If you have any doubt, I'll answer them tomorrow. It's 03:35 AM out here :P. Gotta sleep :)
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
ID: 12282641
As the next non blank cell in Excel can be returned by Ctrl-Page Down you can use the VBA equivalent, ie

Sub CountBlanks()
Dim FirstCell As Range, NextNonBlank As Range
Set FirstCell = Range("A1")
Set NextNonBlank = FirstCell.End(xlDown)
MsgBox "There are " & NextNonBlank.Row - FirstCell.Row - 1 & " blanks"
End Sub

Cheers

Dave
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12282646
And to return row 5 as the next non blank in Column A

Sub CountBlanks()
Dim FirstCell As Range, NextNonBlank As Range
Set FirstCell = Range("A1")
Set NextNonBlank = FirstCell.End(xlDown)
MsgBox "There are " & NextNonBlank.Row - FirstCell.Row - 1 & " blanks" & vbNewLine _
& "Next non-blank cell is in row " & NextNonBlank.Row
End Sub

Cheers

Dave
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12283835
Oooh! Your code is so small Dave. Nice :)
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12283847
Thanks :)

Why don't we see more of you in the Excel TA?
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 12283947
I am new in here. Haven't set foot yet on all EE teritories :).
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
change vba from autofit to 13.5 width? 4 29
VB6 Compile Compatibility Issue 4 115
VB6 - Convert HH:MM into Decimal 8 62
Protecting vb6 & .Net code Obfuscation 18 152
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

860 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