Do Until Loop

[RVS]

Hello all,

Here's my problem...

I'm in the process of writing a program that will allow the user to select a
month and week number (1 through 5 for each week of the month) for that
particular month by means of a dialog box. When the user clicks the OK
button, the program finds the appropriate month and the appropriate week
number for that month and colors that cell blue.

I've set the value in the Month combo box equal to a variable and then wrote
a Do Until loop so that the program will stop on the cell whose value is the
same as the month selected in the combo box. That part works fine.

I have another combo box where the user can select the week number for that
particular month. I tried writing another Do Until loop that will tell the
program to stop on the cell whose value is equal to the selected week number,
but it doesn't work. The program keeps looping and doesn't seem to recognize
when the cell value is equal to the week number variable.

My code is below...


Private Sub OKButton_Click()

SelectedMonth = ComboBox1.Value

WeekNum = ComboBox2.Value

'Find month on spreadsheet that matches month selected by user

x = 13
Do Until Cells(2, x).Value = SelectedMonth
x = x + 5
Loop
Cells(2, x).Select

'Find week number underneath correct month that matches week number selected
by user
'Variable 'x' represents the first week of the month selected by user

Do Until Cells(3, x).Value = WeekNum
x = x + 1
Loop


End Sub


Any help would be much appreciated!

 

[Leith Ross]

Hello all,

Here's my problem...

I'm in the process of writing a program that will allow the user to
select a
month and week number (1 through 5 for each week of the month) for
that
particular month by means of a dialog box. When the user clicks the OK
button, the program finds the appropriate month and the appropriate
week
number for that month and colors that cell blue.

I've set the value in the Month combo box equal to a variable and then
wrote
a Do Until loop so that the program will stop on the cell whose value
is the
same as the month selected in the combo box. That part works fine.

I have another combo box where the user can select the week number for
that
particular month. I tried writing another Do Until loop that will tell
the
program to stop on the cell whose value is equal to the selected week
number,
but it doesn't work. The program keeps looping and doesn't seem to
recognize
when the cell value is equal to the week number variable.

My code is below...


Private Sub OKButton_Click()

SelectedMonth = ComboBox1.Value

WeekNum = ComboBox2.Value

'Find month on spreadsheet that matches month selected by user

x = 13
Do Until Cells(2, x).Value = SelectedMonth
x = x + 5
Loop
Cells(2, x).Select

'Find week number underneath correct month that matches week number
selected
by user
'Variable 'x' represents the first week of the month selected by user

Cells(2, x).Select


End Sub


Any help would be much appreciated!

Hello RVS,

The reason the loop doesn't recognize either the SelectedMonth or
WeekNum is the ComboBox values are always Text. If you type a number
into a cell then the cell value is a number. If you type a date, the
date is stored internally as a number but displayed as text. The fix
here is simple. Don't compare use the cell Value property, but rather
the cell Text property.

x = 13
Do Until Cells(2, x).Text = SelectedMonth
x = x + 5
Loop
Cells(2, x).Select


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)

 

[JLGWhiz]

You can also use a conversion function.

Do Until Cells(3, x).Value = CInt(WeekNum)
x = x + 1
Loop