For Next with variable

cccbzg

Board Regular
Joined
Oct 5, 2014
Messages
61
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:
Monday
Tuesday
Wednesday
Friday
Saturday
1
6
3
2
3
2
4
5
4
4
3
8
7
6
5
4
9
9
8
6
5
12
11
10
7

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
Try this...

Code:
[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

Well-known Member
Joined
Feb 26, 2007
Messages
5,565
Office Version
2019
Platform
Windows
Try this:

Code:
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

Board Regular
Joined
Oct 5, 2014
Messages
61
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,099,048
Messages
5,466,249
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top