You are here:

Excel/FIND in the whole workbook & make report.

Advertisement


Question
Sheet263
Sheet263  
QUESTION: Hi Aidan,

I'm back again:)

What I would like to achieve.

I have a workbook with many worksheets and a new worksheet every 8 to 10 days.

On the first sheet named Dashboard, I would like to place a button that when pressed, an input box would appear asking for a NAME to be entered.

Now, the code would have to look on each sheet for the FIRST occurrence of the NAME, and IF the cell 2 cells down and one cell to the right is not blank,  on a NEW sheet  write the NAME, the sheet name, the value of B4, and the value of " the cell 10 cells down and one cell to the right of the NAME"


Hypothetically:

If the "searched for" NAME (Keith Mitchell) is found on sheetname 263in cell A22

and IF the cell 2 cells down and one cell to the right (B24)is not blank

and the value in B4 of that sheet 263 is 03/10/2013.

the cell 10 cells down and one cell to the right of the NAME (B32) is (1720.00)

then go through remaining sheets to produce the summary below


Print on New sheet

Name         Trip No   Date Paid      Amount Paid
Keith Mitchell   263      03/10/2013   1720.00
Keith Mitchell   264   16/10/2013   1411.00
Keith Mitchell   266   15/11/2013   1238.00
  -----   -----------
Total    3   4369.00


Is this possible?

Cheers
Keith

ANSWER: very possible - this macro

Sub lister()
Dim FindWhat As String
FindWhat = InputBox("What would you like me to find")
Dim X As Long, y As Long, RowLoop As Long, ColLoop As Long, outvar As Long
outvar = 2
For Each sht In ActiveWorkbook.Sheets

   If sht.Name <> "Dashboard" Then
       X = sht.Cells.SpecialCells(xlCellTypeLastCell).Row
       y = sht.Cells.SpecialCells(xlCellTypeLastCell).Column
       'so we now have the bounds to search
       For RowLoop = 1 To X
         For ColLoop = 1 To y
         'this is checking each cell
         If sht.Cells(RowLoop, ColLoop).Value = FindWhat And Len(sht.Cells(RowLoop + 2, ColLoop + 1).Text) > 0 Then
         ' test passed so store the data and move on
         Sheets("Dashboard").Cells(outvar, 1).Value = FindWhat
         Sheets("Dashboard").Cells(outvar, 2).Value = sht.Name
         Sheets("Dashboard").Cells(outvar, 3).Value = sht.Range("B4").Value
         Sheets("Dashboard").Cells(outvar, 4).Value = Len(sht.Cells(RowLoop + 10, ColLoop + 1).Value)
         RowLoop = X
         ColLoop = y
         'to exit the loop
         outvar = outvar + 1
         'to get the next lot of data to store
         
         
         
         
         End If
         Next
       Next
   End If
Next
If outvar <> 2 Then
'both the below need to refer to a specific cell
   Sheets("Dashboard").Cells(outvar, 2).FormulaR1C1 = "=COUNTA(R[-" & outvar - 2 & "]C:R[-1]C)"
   Sheets("Dashboard").Cells(outvar, 4).FormulaR1C1 = "=SUM(R[-" & outvar - 2 & "]C:R[-1]C)"
End If
End Sub


would do it - it stores the data on the dashboard, and only does it for an exact match - so there may be refinements needed, but BASICALLY this is the process you outlined!

---------- FOLLOW-UP ----------

QUESTION: Hi Aidan,

Thank you for you help again.

I have copied the above code and attached it as the macro for my button.  When I run the macro it works perfectly for columns A, B & C but column D is incorrect, it contains a 2, 3 or 4 instead of the cell value required.

I have tried to "step into" the macro, but it is then in a perpetual loop and does nothing.  Unfortunately, I don't understand the code well enough to modify it.  I would like to understand the code and the process so would be grateful for more comments / explanation / assistance

I do appreciate your help very much and would like to learn from you,

Keith

ANSWER: It's not a perpetual loop, but it is a long one!  And I made an error when copying and pasting - I'll revise and explain below

Sub lister()
Dim FindWhat As String
FindWhat = InputBox("What would you like me to find")
Dim X As Long, y As Long, RowLoop As Long, ColLoop As Long, outvar As Long
outvar = 2

'above sets up the variables and gets the input
For Each sht In ActiveWorkbook.Sheets

  If sht.Name <> "Dashboard" Then
'this sequence says do this for every sheet in the workbook except the Dashboard

      X = sht.Cells.SpecialCells(xlCellTypeLastCell).Row
      y = sht.Cells.SpecialCells(xlCellTypeLastCell).Column
'gets the bottommost cell on the sheet and the rightmost - enabling a loop through the
'used range - which is why it felt like a perpetual loop

      'so we now have the bounds to search
      For RowLoop = 1 To X
        For ColLoop = 1 To y
        'this is checking each cell
        If sht.Cells(RowLoop, ColLoop).Value = FindWhat And Len(sht.Cells(RowLoop + 2, ColLoop + 1).Text) > 0 Then
        ' test passed so store the data and move on
        Sheets("Dashboard").Cells(outvar, 1).Value = FindWhat
        Sheets("Dashboard").Cells(outvar, 2).Value = sht.Name
        Sheets("Dashboard").Cells(outvar, 3).Value = sht.Range("B4").Value
        Sheets("Dashboard").Cells(outvar, 4).Value = sht.Cells(RowLoop + 10, ColLoop + 1).text
'the above lines set the values for A-D columns - the last one had an error as I was storing
'the LENGTH of the value, not the value itself.
        RowLoop = X
        ColLoop = y
        'to exit the loop
        outvar = outvar + 1
        'to get the next lot of data to store
        
        
        
        
        End If
        Next
      Next
  End If
Next
If outvar <> 2 Then
'we have been at least once through the loop so we need some subtotals
  Sheets("Dashboard").Cells(outvar, 2).FormulaR1C1 = "=COUNTA(R[-" & outvar - 2 & "]C:R[-1]C)"
  Sheets("Dashboard").Cells(outvar, 4).FormulaR1C1 = "=SUM(R[-" & outvar - 2 & "]C:R[-1]C)"
End If
End Sub


Sorry about my error, and hope this revised version with explanations helps.

To step the macro, if you click into the bit where it stores data you can then "run to cursor" which gets you to where you want to be a bit quicker!

---------- FOLLOW-UP ----------

QUESTION: Crumbs that was quick Aidan.

We now have the correct info in all columns, but, the "SUM" function isn't adding the column.  It is strange because if I type =SUM(D2:D7) into another cell (say E10) it does not work either, but, if I overtype the contents of any of the cells in column D, it does SUM the cells that I overtyped and displays the sum in both D8 and E10 but does not include any value inserted via the macro.

Answer
I try to be quick! My GUESS would be that column D is formatted as text - if so, excel will treat text as text so it cannot be summed.  This isn't the fault of the macro as such, in that it simply sets the value of the cell to whatever is in the original cell - ignore the use of text or value, it shouldn't make a difference.  There are options - you can either have the column D pre-formatted as (say) currency - at which point it should behave - or it is easy enough to set the format of the cells via a macro - just seems wrong to do it each time it hits a cell - but I can change the macro if required!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.