For Next with variable

[cccbzg]

A member of MrExcel was kind enough to help me with the code below. One thing it is lacking is the ability to manage a variable # of columns and rows. I would need to account for that possibility. My thanks for your help!



Sub Foo()
Dim MyDay As String
Dim MyCol As Long, i As Long
MyDay = Application.InputBox("Enter Day of Week")
If WorksheetFunction.CountIf(Range("A1:E1"), MyDay) Then
MsgBox MyDay & " Found"
Else
MsgBox MyDay & " Not Found"
Exit Sub
End If
MyCol = WorksheetFunction.Match(MyDay, Range("A1:E1"), 0)
For i = 2 To 6
MsgBox Cells(i, MyCol).Value
Next i
End Sub


This is what it does:
The user to keys the day of the week in an inputbox when prompted. The macro validates the day from row 1 and then looks up the values in col 1. When there is a hit on the day, it displays a message "Monday found" and then cycles through each remaining row displaying to the user in a message box, each value - one at a time, until the end of the column. So the user keys "Monday" - receives "Monday found" then the user gets a message box saying 3, then a box displaying 2, then one displaying 6, one displaying 4, then 5. if there was NO HIT on the day entered, it displays "not found" end exits the routine.

Data is below:
[TABLE="width: 328"]
<tbody>[TR]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]8
[/TD]
[TD]7
[/TD]
[TD]6
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]8
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]12
[/TD]
[TD]11
[/TD]
[TD]10
[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] Foo()
    [color=darkblue]Dim[/color] MyDay  [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] MyCol  [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] cell   [color=darkblue]As[/color] Range
    
    MyDay = Application.InputBox("Enter Day of Week")
    [color=darkblue]If[/color] MyDay = "False" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]    [color=green]'User canceled[/color]
    
    MyCol = Application.Match(MyDay, Rows(1), 0)
    [color=darkblue]If[/color] IsNumeric(MyCol) [color=darkblue]Then[/color]
        MsgBox MyDay & " Found"
    [color=darkblue]Else[/color]
        MsgBox MyDay & " Not Found"
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range(Cells(2, MyCol), Cells(Rows.Count, MyCol).End(xlUp))
        MsgBox cell.Value
    [color=darkblue]Next[/color] cell
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[alansidman]

Try this:

Option Explicit


Sub Foo()
Dim MyDay As String
Dim MyCol As Long, i As Long
Dim lc As Long, lr As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Range("A" & Rows.Count).End(xlUp).Row
MyDay = Application.InputBox("Enter Day of Week")
If WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(1, lc)), MyDay) Then
MsgBox MyDay & " Found"
Else
MsgBox MyDay & " Not Found"
Exit Sub
End If
MyCol = WorksheetFunction.Match(MyDay, Range(Cells(1, 1), Cells(1, lc)), 0)
For i = 2 To lr
MsgBox Cells(i, MyCol).Value
Next i
End Sub

 

[cccbzg]

Excellent!! THANKS SO MUCH!!


Sub Foo()
Dim MyDay As String
Dim MyCol As Long, i As Long
Dim lc As Long, lr As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Range("A" & Rows.Count).End(xlUp).Row
MyDay = Application.InputBox("Enter Day of Week")
If WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(1, lc)), MyDay) Then
MsgBox MyDay & " Found"
Else
MsgBox MyDay & " Not Found"
Exit Sub
End If
MyCol = WorksheetFunction.Match(MyDay, Range(Cells(1, 1), Cells(1, lc)), 0)
For i = 2 To lr
MsgBox Cells(i, MyCol).Value
Next i
End Sub
[/CODE][/QUOTE]

 

[cccbzg]

Thanks SO MUCH!!