Excel search and show 2

[apex1x]

I'll try to explain this as best as possible.

I have several worksheets, one of which is a sheet for just the totals of the other sheets.

On the worksheets, there are various products listed vertically, and beside those are the quantity and price.

Basically, what I want to do is have the names of whatever products which have more than 5 for quantity, and then have those product names show up in some sort of list underneath the data in the totals worksheet.

Can I do this with simple If statements or do I need advanced macros/VBA??
Any ideas/suggestions would be welcome.

Let me know if you are unclear on what I'm trying to do. If you can understand it, you probably don't.

 

[apex1x]

The macro is written in the "ThisWorkbook" object.
The code is as follows:

Option Explicit

' When the workbook arrangement changes, the following
' may need revision:

Const TOTAL_SHEET = "TOTALS"
Const HIQ_RANGE = "$A$12:$C$6000"
Const ITEMNAME_COLUMN = 1
Const QUANTITY_COLUMN = 3
Const HIGH_QUANTITY = 6

Sub RecapHiQ()
Dim sh As Worksheet
ThisWorkbook.Sheets(TOTAL_SHEET).Range(HIQ_RANGE).Clear
For Each sh In ThisWorkbook.Sheets
If sh.Name <> TOTAL_SHEET Then RecapQ sh
Next sh
End Sub

Private Sub RecapQ(DetailSheet As Worksheet)
Dim sCategory As String
Dim sItemName As String
Dim nQuantity As Integer
Dim c As Range
Dim nBlankRows As Integer
sCategory = DetailSheet.Name
For Each c In DetailSheet.Range(&quot;A4:A65000&quot

If IsNumeric(c.Cells(1, QUANTITY_COLUMN)) Then
nQuantity = c.Cells(1, QUANTITY_COLUMN)
Else
nQuantity = 0
End If

If nQuantity >= HIGH_QUANTITY Then
sItemName = c.Cells(1, ITEMNAME_COLUMN)
PostHighQ sCategory, sItemName, nQuantity
Else
If nQuantity = 0 Then
nBlankRows = nBlankRows + 1
Else
nBlankRows = 0
End If
End If
If nBlankRows > 6 Then Exit Sub
Next c
End Sub

Private Sub PostHighQ(Category As String, Item As String, Quantity As Integer)

Dim TotalSheet As Worksheet
Dim r As Range
Set TotalSheet = ThisWorkbook.Sheets(TOTAL_SHEET)
Set r = TotalSheet.Range(HIQ_RANGE).Cells(1, 1)
If r.Formula <> &quot;&quot; Then
If r.Offset(1, 0).Formula <> &quot;&quot; Then Set r = r.End(xlDown)
Set r = r.Offset(1, 0)
End If
r.Cells(1, 1) = Category
r.Cells(1, 2) = Item
r.Cells(1, 3) = Quantity
Set r = Nothing
Set TotalSheet = Nothing
End Sub


The error comes up in the middle of the screen:

-----------------------
Microsoft Visual Basic


Type Mismatch

[ Ok ] [ Help ]
-----------------------


If you can understand it, you probably don't.

 

[Zathras]

I can't reproduce your results.

But, you haven't answered my question. What exactly are the keystrokes/mouse clicks you use to cause this message to appear? Does it happen as soon as you open the workbook? Do you enter any data? Do you run the macro from the menu? a shortcut? the VBA window? What version of Excel are you using? (I'm using Excel 97 here but Excel 2K should be ok, too. It's not doing anything fancy.)

Normally, this sort of macro code should go into a Module (VBA Menu: Insert/Module) but it works for me from the &quot;ThisWorkbook&quot; page as well. You might try moving the code from &quot;ThisWorkbook&quot; to a new module. That may solve your problem.

One thing I was surprised by is that you didn't need to change the specification for where you wanted the recap to be placed:

  Const HIQ_RANGE = "$A$12:$C$6000"

Whatever you have on the TOTALS sheet in rows 12 thru 6000 in the first three columns will be cleared and over-written. It is a remarkable coincidence if that is the way your TOTALS page is set up.

BTW, I am going on vacation for eight days starting tomorrow, so if we can't get to the bottom of this, you may need to start a new thread in the VBA forum: forum707

 

[apex1x]

Alright, I appreciate your help up to this point.
We are on the right track for sure.

I am using Excel (Office XP), and my TOTALS sheet is set up so that your original range for the summary is exactly where I want it

I go to Tools->Macro->Macros, and then I run my macro. The error then comes up immediatly.

I dont know if the code is looking at the wrong section of a worksheet or is assigning a variable to something of the wrong type, etc. If you can understand it, you probably don't.

 

[Zathras]

It looks like an XP thing. To get the &quot;good&quot; error dialog box you need to change an option:

From the VBA editor window menu, click Tools/Options... and select the General tab. Then under &quot;Error Trapping&quot; click the &quot;Break on All Errors&quot; radio button and click OK. Now you should get the four-button error message dialog.

I couldn't get my test file to show the error until I took out the if IsNumeric( ) test for setting nQuantity, so I still don't know exactly what is going wrong for you. But at least now you should be able to tell me which statement is issuing the complaint.

BTW, you may want to set that option back before giving the final product to your users. It will make it harder for them to break into your code and make things worse.

 

[apex1x]

OK, the program halts on

Next sh
If you can understand it, you probably don't.

 

[Zathras]

Sorry to be so long in replying, but I just got back from vacation.

I am guessing that you didn't tell me everything. I can reproduce the error by putting a chart sheet in the workbook.

Try replacing the line:

   For Each sh In ThisWorkbook.Sheets

with

   For Each sh In ThisWorkbook.Worksheets

Please let me know if it works.

 

[apex1x]

Yes, that fixed the problem with the error.
The problem now is that nothing shows up after I run the macro.
I even got rid of the chart so its just the 4 worksheets and the total worksheet.
I'm at a loss here.
Anything obvious that would cause this?
Maybe I should send you the file in order to diagnose this one. If you can understand it, you probably don't.

 

[Zathras]

Double-check the constants to be sure they are pointing to the correct columns and ranges:

My test data used the following, your setup is different (e.g., quantity is column 3)

Const TOTAL_SHEET = "Totals"
Const HIQ_RANGE = "$A$12:$C$6000"
Const ITEMNAME_COLUMN = 1
Const QUANTITY_COLUMN = 2
Const HIGH_QUANTITY = 6

If you continue to have difficulty, post your code again so I can test with what you are using.

 

[apex1x]

Const TOTAL_SHEET = &quot;TOTALS&quot;
Const HIQ_RANGE = &quot;$A$12:$C$6000&quot;
Const ITEMNAME_COLUMN = 1
Const QUANTITY_COLUMN = 3
Const HIGH_QUANTITY = 6

They all seem ok.
There are several quantities over 6, so entries should show up...

If you can understand it, you probably don't.

 

[Zathras]

You second post from Feb 17 indicated that SOME output was being generated. What is different now?

Are you still running the code from &quot;ThisWorkbook&quot; or have you moved it to a module? (It should work the same either way, but it is customary to use a module for this kind of code.)

 

[apex1x]

I&quot;ve moved it into a module, but to no effect.
The stuff I had showing up before was more or less a fluke because I specified the wrong total_sheet. I didn't know it was case sensitive.

As why nothing is showing up now, I have no idea. Here is the code i'm using currently:

Option Explicit

' When the workbook arrangement changes, the following
' may need revision:

Const TOTAL_SHEET = &quot;TOTALS&quot;
Const HIQ_RANGE = &quot;$A$12:$C$6000&quot;
Const ITEMNAME_COLUMN = 1
Const QUANTITY_COLUMN = 3
Const HIGH_QUANTITY = 6

Sub RecapHiQ()
Dim sh As Worksheet
ThisWorkbook.Sheets(TOTAL_SHEET).Range(HIQ_RANGE).Clear
For Each sh In ThisWorkbook.Sheets
If sh.Name <> TOTAL_SHEET Then RecapQ sh
Next sh
End Sub

Private Sub RecapQ(DetailSheet As Worksheet)
Dim sCategory As String
Dim sItemName As String
Dim nQuantity As Integer
Dim c As Range
Dim nBlankRows As Integer
sCategory = DetailSheet.Name
For Each c In DetailSheet.Range(&quot;A4:A65000&quot
nQuantity = c.Cells(1, QUANTITY_COLUMN)
If nQuantity >= HIGH_QUANTITY Then
sItemName = c.Cells(1, ITEMNAME_COLUMN)
PostHighQ sCategory, sItemName, nQuantity
Else
If nQuantity = 0 Then
nBlankRows = nBlankRows + 1
Else
nBlankRows = 0
End If
End If
If nBlankRows > 6 Then Exit Sub
Next c
End Sub

Private Sub PostHighQ(Category As String, Item As String, Quantity As Integer)
Dim TotalSheet As Worksheet
Dim r As Range
Set TotalSheet = ThisWorkbook.Sheets(TOTAL_SHEET)
Set r = TotalSheet.Range(HIQ_RANGE).Cells(1, 1)
If r.Formula <> &quot;&quot; Then
If r.Offset(1, 0).Formula <> &quot;&quot; Then Set r = r.End(xlDown)
Set r = r.Offset(1, 0)
End If
r.Cells(1, 1) = Category
r.Cells(1, 2) = Item
r.Cells(1, 3) = Quantity
Set r = Nothing
Set TotalSheet = Nothing
End Sub
If you can understand it, you probably don't.

 

[Zathras]

I pasted the code from your post into a new module and it works just fine for me. That would indicate the problem is in the detail sheets setup. The macro expects to find an item description in column &quot;A&quot; of each sheet, starting with cell &quot;A4&quot;

What aren't you telling me? Are the quantities numbers or text in the detail sheets (column &quot;C&quot. Does the data for the details get keyed in by a user or is it copied in from some other source (pasted from the clipboard / external data / ???)

Using the wrong case for the TOTALS sheet simply caused the macro to treat it as if it were an additional detail sheet which resulted in an infinite loop. It worked then, why not now? Again, I must ask what changed from the 17th?

Here is what I am using for test data:

Tab: Sewing

A3: 'Item
B3: 'Stuff
C3: 'Quantity
D3: 'Price
E3: 'Data
F3: 'MoreData
A4: 'aaa
C4: 3
A5: 'bbb
C5: 7
A6: 'ccc
C6: 3
A7: 'ddd
C7: 5
A8: 'eee
C8: 6

I have other sheets as well.

Here is the result (&quot;TOTALS&quot sheet:

Tab: TOTALS

A12: 'Sewing
B12: 'bbb
C12: 7
A13: 'Sewing
B13: 'eee
C13: 6
A14: 'Manufactured
B14: 'hh
C14: 6
A15: 'Manufactured
B15: 45
C15: 80
A16: 'Manufactured
B16: 'kk
C16: 9
etc.

 

[apex1x]

The infinite loop only took data off the TOTALS sheet, never off any of the other worksheets.

The quantity is in General format. I tried number format but it made no difference.

AH, i kinda got it to work. If I have every quantity entry >1 then when the macro hits a number bigger than the defined comparison (6), everything works fine. If there are blanks or 0's, nothing shows up on the totals sheet.

ie.

quantity:

1
4
2
2
2
2
2
21
1
1
0
0
0
7

In that series, the 21 will be displayed because the macro is able to reach it, but because of the zeros inbetween it and the 7, the 7 doesn't show up on the totals.
BUT if i change those zeros to 1's or greater, the 7 will show.

Is there a problem with the searching method?
If you can understand it, you probably don't.

 

[Zathras]

That's it! I was using quantity zero to stop the looping process in each detail sheet. Make the following changes and you should be in business: (Replace the entire RecapQ( ) subroutine with the following.)

Private Sub RecapQ(DetailSheet As Worksheet)
Dim sCategory As String
Dim sItemName As String
Dim nQuantity As Integer
Dim c As Range
Dim nBlankRows As Integer
  sCategory = DetailSheet.Name
    For Each c In DetailSheet.Range("A4:A65000")
    nQuantity = c.Cells(1, QUANTITY_COLUMN)
    sItemName = c.Cells(1, ITEMNAME_COLUMN)
    If nQuantity >= HIGH_QUANTITY Then
      PostHighQ sCategory, sItemName, nQuantity
    Else
      If sItemName = "" Then
        nBlankRows = nBlankRows + 1
      Else
        nBlankRows = 0
      End If
    End If
    If nBlankRows > 6 Then Exit Sub
  Next c
End Sub

 

[apex1x]

Awesome, works perfect.
Took some tweaking but its finally there.
Thanks.
If you can understand it, you probably don't.