Case Statement and For next loop

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
577
Office Version
  1. 365
Platform
  1. Windows
Hello,
My workbook has a tab for each month. When I’m in a specific month, I run the macro below to add a name to the Totals tab. It places the name on the first available row for that months chart. I created name rages for column A for each months chart on the totals tab. Example, Myjan=Worksheets(“Totals”).Range(“A2:A8”)

My goal now is to add the name to the rest of months on the totals tab. For example, if I add a name in June, I want to the macro to continue to add the person to July, Aug, Sept etc… through Dec.

I am not sure what path to take. Do I create a loop to go through the rest of the months incorporating the Case Statements and if so how?

I am going to add a bit of code to check the sheet and see if the name already exists. I haven’t gotten that far yet.

Any help is appreciated.




Book1
ABCD
1Jan
2Name
3Bill W
4Rick M
5Diane D
6
7
8
9
10Feb
11Name
12Bill W
13Rick M
14Diane D
15
16
17
18
Totals


Code:
 Option Compare Text
Sub AddNamesl()
Dim rng As Range

'startmon =current Month
 Startmon = “February”

‘Empnam =  Employee name to add
empnam = "Phil C"

Select Case Startmon
    Case "January"
         Set Rng = Range("myjan")
    Case "February"
         Set Rng = Range("Myfeb")
    Case "March"
         Set Rng = Range("Mymar")
‘etc…….    
End Select
With Worksheets("Totals")
    Rng.Cells.SpecialCells(xlCellTypeBlanks).Cells(1) = empnam
End With

'Add to rest of the worksheet



End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,789
Office Version
  1. 2010
Platform
  1. Windows
Try this

Code:
Sub AddNames()
  Dim nms As Variant, startmon As Long, empnam As String, i As Long
[COLOR=#0000ff]  empnam = "Phil C"[/COLOR]
  nms = Array("myjan", "myfeb", "mymar", "myapr", "mymay", "myjun", _
              "myjul", "myaug", "mysep", "myoct", "mynov", "mydec")
[COLOR=#008000]  startmon = 6  'example june[/COLOR]
  For i = startmon - 1 To UBound(nms)
    Sheets("Totals").Range(nms(i)).Cells.SpecialCells(xlCellTypeBlanks).Cells(1) = empnam
  Next
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,789
Office Version
  1. 2010
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,010
Messages
5,834,880
Members
430,325
Latest member
Thony

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