For Next with variable

cccbzg

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

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
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
6,364
Office Version
  1. 365
Platform
  1. 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
63
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]
 

Forum statistics

Threads
1,136,773
Messages
5,677,642
Members
419,708
Latest member
PhilD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top