VBA Programming!

Hi

See it this gets you going

Private Sub Annual_Click()
  Dim FindIT As Range, DayRng As Range, Scnt As Long
    With Selection.Interior
        .ColorIndex = 32
        .Pattern = xlSolid
    End With
    Set FindIT = Sheets("Main").Range("A:A").Find(what:=Cells(Selection.Row, 1).Value, lookat:=xlWhole)
  Set DayRng = Cells(3, Selection.Column).Resize(1, Selection.Columns.Count)
  Scnt = WorksheetFunction.CountIf(DayRng, "S")
  FindIT.Offset(0, 2).Value = FindIT.Offset(0, 2).Value + Selection.Columns.Count - Scnt
End Sub

rylo

Originally Posted by rylo

Hi

See it this gets you going

Private Sub Annual_Click()
  Dim FindIT As Range, DayRng As Range, Scnt As Long
    With Selection.Interior
        .ColorIndex = 32
        .Pattern = xlSolid
    End With
    Set FindIT = Sheets("Main").Range("A:A").Find(what:=Cells(Selection.Row, 1).Value, lookat:=xlWhole)
  Set DayRng = Cells(3, Selection.Column).Resize(1, Selection.Columns.Count)
  Scnt = WorksheetFunction.CountIf(DayRng, "S")
  FindIT.Offset(0, 2).Value = FindIT.Offset(0, 2).Value + Selection.Columns.Count - Scnt
End Sub

rylo

WOW!!!! It worked just fine!
Thank you so much Rylo!

Hi

Here goes as requested. I've put some comments for each line

'dimension the variables
Dim FindIT As Range, DayRng As Range, Scnt As Long

'create an object that is the position of the find function. If you have a selection, then it you can find the row. The cells(selection.row, 1) will give you the value in A1 of your selection and this is what you are searching for.
Set FindIT = Sheets("Main").Range("A:A").Find(what:=Cells(Selection.Row, 1).Value, lookat:=xlWhole)

'Set another object range that covers the days from row 3 that matches the selection. Cells(3,selection.column) will give the first cell. Resize(1,selection.columns.count) gives a 1 row x n column array that is the same size as your selection. Effectively selects the days from row 3 that matches your selection range.
Set DayRng = Cells(3, Selection.Column).Resize(1, Selection.Columns.Count)

'uses the countif function to find the number of "S" items in the "days" range
Scnt = WorksheetFunction.CountIf(DayRng, "S")

'goes 2 columns to the right of the findit range object, and adds the number of cells in the selection less any "S" items to the number that was in that cell.
FindIT.Offset(0, 2).Value = FindIT.Offset(0, 2).Value + Selection.Columns.Count - Scnt

If that doesn't make sense, don't hesitate to come back again.

rylo