Solved

Excel Macro Challenge

Posted on 2012-04-04
8
279 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office 2016 Excel Issue 4 26
Excel Score Formula 5 49
Hard coding time and date into Excel 2 31
splitting text of cell to columns 14 24
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

12 Experts available now in Live!

Get 1:1 Help Now