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

parameter queries in macros
 

This should do it

Sub find5andD()
hideall 'call your macro to hide all columns
For Each col In Columns("d:fz")
If Right(Cells(1, col.Column), 1) = 5 And _
Application.CountIf(col, "D") 0 Then col.Hidden = False
Next
End Sub
--
Don Guillett
SalesAid Software

"lindasf " wrote in message
...
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 & "Enter
2 for Project Recommendation" & vbNewLine & "Enter 3 for Final DHS
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 as
follows:

If you enter "5" in the parameter query, I would like it to display
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 it
in a new/separate macro if that is less complicated.

Thx. much! (file attached)

lindasf

Attachment filename: calwin food stamps training analysis-dhs
vdat-demo.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=624509
---
Message posted from http://www.ExcelForum.com/

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

parameter queries in macros
 

Thx much guys

--
Message posted from http://www.ExcelForum.com

parameter queries in macros
 

glad to help

--
Don Guillett
SalesAid Software

"lindasf " wrote in message
...
Thx much guys!


---
Message posted from http://www.ExcelForum.com/