Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
parameter queries in macros
Hello, The following macro works great!
Sub hidecolumns() Columns("d:fz").Hidden = False Dim sPrompt As String sPrompt = "Enter 1 for Initial DHS Recommendation" & vbNewLine & "Ente 2 for Project Recommendation" & vbNewLine & "Enter 3 for Final DH Recommendation" & vbNewLine & "Enter 4 for Project/DHS Matches" vbNewLine & "Enter 5 for DHS VDAT Modules" x = InputBox(sPrompt) For Each c In Range("d1:fz1") If Right(c, 1) < x Then c.EntireColumn.Hidden = True Next End Sub However, I would like to change this macro or create a new one a follows: If you enter "5" in the parameter query, I would like it to displa only the columns that: * contain a 5 in the header (as it is now) AND * have a D in at least one cell of that column I don't have to put this logic in this particular macro. I can put i in a new/separate macro if that is less complicated. Thx. much! (file attached) lindas Attachment filename: calwin food stamps training analysis-dhs vdat-demo.xl Download attachment: http://www.excelforum.com/attachment.php?postid=62450 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
parameter queries in macros
Try this:
Sub hidecolumns() Columns("d:fz").Hidden = False Dim sPrompt As String sPrompt = "Enter 1 for Initial DHS Recommendation" & vbNewLine & "Ente 2 for Project Recommendation" & vbNewLine & "Enter 3 for Final DH Recommendation" & vbNewLine & "Enter 4 for Project/DHS Matches" vbNewLine & "Enter 5 for DHS VDAT Modules" x = InputBox(sPrompt) Set myRange = Range("A1").CurrentRegion LastRow = myRange.Cells(myRange.Cells.Count).Row For Each c In Range("d1:fz1") If Right(c, 1) < x Then c.EntireColumn.Hidden = True Else: Hide = True j = c.Column For i = 2 To LastRow If Cells(i, j) = "D" Then Hide = False Next i If Hide Then c.EntireColumn.Hidden = True End If Next End Su -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
parameter queries in macros
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
parameter queries in macros
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table queries not updating when parameter changes | Excel Discussion (Misc queries) | |||
Parameter queries in Excel 2007 | Excel Discussion (Misc queries) | |||
Queries in Macros | Excel Worksheet Functions | |||
MS query--can it handle parameter queries from Access? | Excel Discussion (Misc queries) | |||
Where can I learn how to create parameter queries in Excel? | Excel Discussion (Misc queries) |