Solved

Excel Macro Challenge

Posted on 2012-04-04
8
278 Views
Last Modified: 2012-04-12
Hi experts,

I'm a little out of my comfort zone here, being an Access developer who now has an Excel VBA challenge.

I have a workbook with many worksheets. On one of the configuration sheets I have a series (perhaps could be a number of named ranges) with yes/no combos. Three columns to the left of each is a unique code no.

As each yes/no is set, it needs to gather the code number, then go to a selected number of other worksheets and find that code in a predetermined colum in each. The worksheets and columns can be stored in a table in another worksheet. Once found (and there will always be a match) the row that the code is in is hidden if the yes/no is no, and unhidden if yes.

To further complicate things, whenever the workbook is opened, it needs to check each and evry one of the yes/no values and ensure that the the appropraite rows are hidden or not.

I'm quite happy to battle through the VBA if I can get a few pointers and equally happy to break this into a number of questions if there's too much here.


Kelvin

Kelvin
0
Comment
Question by:Kelvin Sparks
  • 5
  • 3
8 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 37805367
Kelvin - can you post a sample workbook?
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37805377
I'll make up a sample and upload
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37805432
The sample xlsx file has a number of sheets. If you look at the sheet select product - two rows have yes and one no. Looking at worksheet Admin, it tells us to only use workbooks Wholesale and Retail.

Going back to Select product, there is a yes in the row for product 100, I want it to check the worksheetslisted in Admin and look in the column indicated for that worksheet for the code 100. As this is yes, that row containing the code 100 in each of the Retail and wholesale workbooks should be unhidden. For product code 101, there is a no, so the rows for the 101 code in thosew two sheets are to be hidden. Worksheet Product Details is not listed in Admin and so is to be ignored for this exercise.


Kelvin
Sample.xlsx
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37805624
For worksheet Wholesale I take it should be col B to check rather than C? Does the (un)hiding of rows apply only to those codes in the first sheet - i.e. if other rows are hidden they should be left as they are?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 37805691
Try this:
Sub x()
  
Dim rFind As Range, sAddr As String, ws As Worksheet, r As Range, r1 As Range
Dim rProd As Range, rSheet As Range

With Sheets("Select Product")
    Set rProd = .Range("B5", .Range("B" & Rows.Count).End(xlUp))
End With
With Sheets("Admin")
    Set rSheet = .Range("B5", .Range("B" & Rows.Count).End(xlUp))
End With

For Each r In rProd
    For Each r1 In rSheet
        With Sheets(r1.Text).Range(r1.Offset(, 2) & ":" & r1.Offset(, 2))
            Set rFind = .Find(What:=r, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                sAddr = rFind.Address
                Do
                    If r.Offset(, 2) = "Yes" Then
                        rFind.EntireRow.Hidden = False
                    ElseIf r.Offset(, 2) = "No" Then
                        rFind.EntireRow.Hidden = True
                    End If
                    Set rFind = .FindNext(rFind)
                 Loop While rFind.Address <> sAddr
            End If
        End With
        sAddr = ""
        Set rFind = Nothing
    Next r1
Next r

End Sub

Open in new window

0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37808745
Thanks, I'm away today, but will try this over the weekend - looking very very promising!


Kelvin
0
 
LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 37822229
Thanks for the very efficient code. I'm having some fun and games with it running against locked or protected sheets, but am working through this.
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 37836801
Hi,

The link below is a followup question for this answer - more points on offer!

Hope you can help

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27672336.html
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now