Vba Code requied - Loop in a range

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,475
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

I will be having two user form buttons one for UP and other one for DOWN

A list will be containing names of months starting from January, February till December in Range ("AA5:AA16")

If current value of cell L3 is March and UP is pressed then it should show April i.e. moving one cell down in that range & vice versa if DOWN is pressed

If current value of cell L3 is December i.e. the last cell in the range and UP is pressed then it should show January i.e. moving one cell forward which is coming back at the top of the list & vice versa if DOWN is pressed

If cell L3 is blank and UP is pressed then it should show January i.e. first cell in that range
If cell L3 is blank and DOWN is pressed then it should show December i.e. last cell in that range

Regards,

Humayun
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How about, Assign the Up macro to the Up button, assign the Down macro to the Down button.

VBA Code:
Sub Up()
  Call MoveMonth("AA16", "AA5", 1)
End Sub

Sub Down()
  Call MoveMonth("AA5", "AA16", -1)
End Sub

Sub MoveMonth(a As String, b As String, n As Long)
  Dim f As Range
  Set f = Range("AA5:AA16").Find([L3], , xlValues, xlWhole)
  If Not f Is Nothing Then
    If f.Address(0, 0) = a Then [L3] = Range(b) Else [L3] = f.Offset(n)
  Else
    [L3] = Range(b)
  End If
End Sub
 
Upvote 0
Hi DanteAmor,

Thanks.... The code is working EXACTLY what is required..

Only one small issue that column where the range is present needs to be unhidden....

Is it possible to make it work if the columns are hidden

Regards,

Humayun

EDIT:
noticed one more thing that the worksheet gets filtered like hides and unhides rows based on selection of the month & the some of the rows of that list also gets hidden and that is also having trouble....
 
Last edited:
Upvote 0
Only one small issue that column where the range is present needs to be unhidden.
Use the following:
VBA Code:
Sub Up()
  Call MoveMonth("AA16", "AA5", 1)
End Sub

Sub Down()
  Call MoveMonth("AA5", "AA16", -1)
End Sub

Sub MoveMonth(a As String, b As String, n As Long)
  Dim f As Range, c As Range
  For Each c In Range("AA5:AA16")
    If UCase(c.Value) = UCase([L3]) Then Set f = c
  Next
  [L3] = Range(b)
  If Not f Is Nothing Then If f.Address(0, 0) <> a Then [L3] = f.Offset(n)
End Sub
______________________________________________________________________________________________

noticed one more thing that the worksheet gets filtered like hides and unhides rows based on selection of the month & the some of the rows of that list also gets hidden and that is also having trouble
I don't understand what you mean, my code doesn't hide rows.
 
Upvote 0
Solution
Hi DanteAmor,

I am not saying that your code hide rows.... :)

What I am trying to say there is a workhsheet change event in the sheet which gets triggered as soon as the month is selected. And at times that worksheet change event does hide some rows. And when any row between range "A5:A16" is hidden then your code does not give proper result.

And also for the code to work the range column (where the month list exists) i.e. Range ("A5:A16") also needs to be unhidden.
 
Upvote 0
And also for the code to work the range column (where the month list exists) i.e. Range ("A5:A16") also needs to be unhidden.
You tried the code in post #4, it works for when you have the column hidden.
 
Upvote 0
I tried it again and it is working now :)

I am still scratching my head what did I do wrong at the first go....

Anyways, nothing wrong with the code...

Working PERFECT

Thanks & sorry for the trouble
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top