put a value in a range of cells

Hello all

I am currently working on a sheet where I have two cells with dates. These represent the dates that someone would be out of the office. Each staff member has a worksheet with the dates of the year in the range ("E2:IT2"). I need a macro that, if these two cells with dates are filled out will search the range in each sheet and return the value "v" in rows 4-39 in the corresponding column. This will black out the column to indicate they are out.

Please see my current code below. I am getting a "type mismatch" error on the If line. Hopefully I am close to the proper code. Thanks in advance!

Dim b As Variant
Dim x As Long

For Each Column In Sheets("PMDavid H").Range("E2:IT2")
    b = Range("E2:IT2").Cells.Value
        If Sheets("Home").Cells("L6").Value <= b And b <= Sheets("Home").Cells("N6").Value Then
            For c = 4 To 39 And x = b.Column
                Cells(c, x).Values = "v"
            Next
        Else: Resume
        End If
    Next

Given that b is a Variant and:

b = Range("E2:IT2").Cells.Value

b is now a two dimensional array, hence the type mismatch on the line:

If Sheets("Home").Cells("L6").Value <= b And b <= Sheets("Home").Cells("N6").Value Then

The array will have rows and columns, e.g. b(1, 1), even though it is only 1 row (E2:IT2).

b(1,1) = Range("E2) and b(1,2) = Range("F2"), etc.

Can you upload a sample workbook?

Steven Please find it attached. Notice I made a type of userform directly on the first sheet. I intend to link this to an if true false statement later but now I'm just trying to figure out how to make it fill out the values properly. Thanks!

The sub-routine BlackOut takes a start date, end date, and the worksheet.
It looks for any date in row 2 (between column E & IT) which is between the two.
Then it marks rows 4 to 39 with a "v".

The sub-routine Run_BlackOut illustrates how to call the BlackOut sub-routine.

Sub BlackOut(dbStart As Date, dbEnd As Date, ws As Worksheet)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart _
            And dt <= dbEnd Then
                For nRow = 4 To 39
                    .Cells(nRow, nCol) = "v"
                Next nRow
            End If
        Next nCol
    End With
End Sub

Sub Run_BlackOut()
    BlackOut #2/27/2012#, #3/2/2012#, Worksheets("PMDavid H")
End Sub

Steven, I am putting that into my file but it's not exactly working. I don't understand the Run subroutine. The dates will constantly be changing. Thats why I have the cells in L and M on "Home" sheet for the user to fill out. This doesn't seem to work right. How do I link dt to those cells?

I've changed the code slightly but now it doesn't do anything. Before it would black out the entire range from E4 to IT39.

Dim dbstart As Date
    Dim dbend As Date
    Dim nCol As Long, nRow As Long
    
    With dbstart = Sheets("Home").Range("L6").Value And dbend = Sheets("Home").Range("M6").Value
    
        With Sheets("PMDavid H")
            For nCol = Columns("E").Column To Columns("IT").Column
                dt = .Cells(2, nCol)
                If dt >= dbstart And dt <= dbend Then
                    For nRow = 4 To 39
                        .Cells(nRow, nCol) = "v"
                    Next nRow
                End If
            Next nCol
        End With
    End With
    
End Sub

The run subroutine is only for illustration.

You're planning to have a macro connected to the OK button, yes?

And David is PM David H, yes?

Then you will need a macro like:

Sub WithOK()
    With Worksheets("Home")
        If Not IsEmpty(.Range("L6")) And Not IsEmpty(.Range("N6")) Then
            BlackOut .Range("L6").Value, .Range("N6").Value, Worksheets("PMDavid H")
        End If
    End With
End Sub

And this macro will be connected to your OK button.

Now, I assume that you will have worksheets for Dave, Tony, Sante, etc.

If my assumptions are correct,you will need an if ... end if statement for each person.

It looks like each set of dates are even numbers.
So Dave would be L8 & N8, Sante would be L10 & N10, etc.
And the worksheet name will be different.

Another way of doing this, is to have a string of worksheet names.

Sub WithOK_v2()
    Const sWkShtNames = "DavidSheet,DaveSheet,TonySheet,SanteSheet,NateSheet,KurtSheet,KeenanSheet,JustinSheet,JeffSheet"
    Dim sArray() As String
    Dim i As Long, nRow As Long
    
    sArray = Split(sWkShtNames, ",")
    With Worksheets("Home")
        For i = 0 To UBound(sArray)
            nRow = i * 2 + 6
            If Not IsEmpty(.Cells(nRow, "L")) And Not istempty(.Cells(nRow, "N")) Then
                BlackOut .Cells(nRow, "L").Value, .Cells(nRow, "N"), sArray(i)
            End If
        Next i
    End With
End Sub

Now I haven't tested either of these codes (and obviously, the worksheet names are just made up for illustration purposes). And I'm not so good at writing code that I don't make mistakes.

If you would upload a workbook containing everyone's worksheet I would get the above codes to work for you.

But the subroutine "BlackOut" I've tested and it seem to work perfectly.

Steven, I went back and copied you're original code and it does work perfectly. My apologies. I must've altered something and not realized it. Thank you for all of your help though. I did have one more question. I am trying to expand on this function and make it also work on the "All" sheet of the example file I posted. What I need it to do is have it again search through the dates but also it needs to search column D to make sure it corresponds to the person out of the office. My function runs through the first half where it fills out the individual sheets just fine but when it gets to the All sheet nothing happens. No errors or anything. My entire code is as follows:

Public Sub Vacation_Time()

 
        
    'Delete out of date vacation days
    For Each cell In Sheets("All").Range("E4:IT300")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("PMDavid H").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("PMDave S").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("PMTony").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("PMSante").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("PMNate").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("PMKurt").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("ENGKeenan").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("ENGJustin").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
    For Each cell In Sheets("ENGJeff").Range("E4:IT39")
        If cell.Value = "v" Then cell.ClearContents
    Next
       
       
       
    With Worksheets("Home")
        If Range("W6").Value = "True" Then
            BlackOut .Range("L6").Value, .Range("N6").Value, Worksheets("PMDavid H")
        End If
            
        If Range("W8").Value = "True" Then
            BlackOut .Range("L8").Value, .Range("N8").Value, Worksheets("PMDave S")
        End If
            
        If Range("W10").Value = "True" Then
            BlackOut .Range("L10").Value, .Range("N10").Value, Worksheets("PMTony")
        End If
            
        If Range("W12").Value = "True" Then
            BlackOut .Range("L12").Value, .Range("N12").Value, Worksheets("PMSante")
        End If
            
        If Range("W14").Value = "True" Then
            BlackOut .Range("L14").Value, .Range("N14").Value, Worksheets("PMNate")
        End If
            
        If Range("W16").Value = "True" Then
            BlackOut .Range("L16").Value, .Range("N16").Value, Worksheets("PMKurt")
        End If
            
        If Range("W18").Value = "True" Then
            BlackOut .Range("L18").Value, .Range("N18").Value, Worksheets("ENGKeenan")
        End If
            
        If Range("W20").Value = "True" Then
            BlackOut .Range("L20").Value, .Range("N20").Value, Worksheets("ENGJustin")
        End If
            
        If Range("W22").Value = "True" Then
            BlackOut .Range("L22").Value, .Range("N22").Value, Worksheets("ENGJeff")
        End If
    End With
    
    
    With Worksheets("Home")
        If Range("W6").Value = "True" Then
            BlackOut2 .Range("L6").Value, .Range("N6").Value, Worksheets("All")
        End If
    
        If Range("W8").Value = "True" Then
            BlackOut3 .Range("L8").Value, .Range("N8").Value, Worksheets("All")
        End If
            
        If Range("W10").Value = "True" Then
            BlackOut4 .Range("L10").Value, .Range("N10").Value, Worksheets("All")
        End If
            
        If Range("W12").Value = "True" Then
            BlackOut5 .Range("L12").Value, .Range("N12").Value, Worksheets("All")
        End If
            
        If Range("W14").Value = "True" Then
            BlackOut6 .Range("L14").Value, .Range("N14").Value, Worksheets("All")
        End If
            
        If Range("W16").Value = "True" Then
            BlackOut7 .Range("L16").Value, .Range("N16").Value, Worksheets("All")
        End If
    End With
       
    
      
    Application.ScreenUpdating = True
    
    End Sub
    
    Sub BlackOut(dbStart As Date, dbEnd As Date, ws As Worksheet)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart And dt <= dbEnd Then
                For nRow = 4 To 39
                    .Cells(nRow, nCol) = "v"
                Next nRow
            End If
        Next nCol
    End With
End Sub

Sub BlackOut2(dbStart As Date, dbEnd As Date, ws As Worksheet)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart And dt <= dbEnd Then
                For nRow = 4 To 300
                    If Range("D" & nRow).Value = "David H...." Then
                    .Cells(nRow, nCol) = "v"
                    End If
                Next nRow
            End If
        Next nCol
    End With
End Sub

Sub BlackOut3(dbStart As Date, dbEnd As Date, ws As Worksheet)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart And dt <= dbEnd Then
                For nRow = 4 To 300
                    If Range("D" & nRow).Value = "Dave S....." Then
                    .Cells(nRow, nCol) = "v"
                    End If
                Next nRow
            End If
        Next nCol
    End With
End Sub

Sub BlackOut4(dbStart As Date, dbEnd As Date, ws As Worksheet)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart And dt <= dbEnd Then
                For nRow = 4 To 300
                    If Range("D" & nRow).Value = "Tony" Then
                    .Cells(nRow, nCol) = "v"
                    End If
                Next nRow
            End If
        Next nCol
    End With
End Sub

Sub BlackOut5(dbStart As Date, dbEnd As Date, ws As Worksheet)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart And dt <= dbEnd Then
                For nRow = 4 To 300
                    If Range("D" & nRow).Value = "Sante" Then
                    .Cells(nRow, nCol) = "v"
                    End If
                Next nRow
            End If
        Next nCol
    End With
End Sub

Sub BlackOut6(dbStart As Date, dbEnd As Date, ws As Worksheet)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart And dt <= dbEnd Then
                For nRow = 4 To 300
                    If Range("D" & nRow).Value = "Nate" Then
                    .Cells(nRow, nCol) = "v"
                    End If
                Next nRow
            End If
        Next nCol
    End With
End Sub

Sub BlackOut7(dbStart As Date, dbEnd As Date, ws As Worksheet)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart And dt <= dbEnd Then
                For nRow = 4 To 300
                    If Range("D" & nRow).Value = "Kurt" Then
                    .Cells(nRow, nCol) = "v"
                    End If
                Next nRow
            End If
        Next nCol
    End With
End Sub

As you can see it first empties all sheets of and "v" values, then it goes through your BlackOut function. Then I tried altering it to make it search through the "All" sheet for a given individual and the dates they are out. Hopefully you can provide some guidance. I feel as though it's close but I can't understand if it's just declarations or something. Thanks for all of your help so far!

You multiplied my BlackOut function. Instead it should be modified to accept a Name.
You don't want more than one function doing the same type of things.

For example:

Sub BlackOut(dbStart As Date, dbEnd As Date, ws As Worksheet, sName As String)
    Dim nCol As Long, nRow As Long
    Dim dt As Date
    
    With ws
        For nCol = Columns("E").Column To Columns("IT").Column
            dt = .Cells(2, nCol)
            If dt >= dbStart And dt <= dbEnd Then
                If Len(sName) Then
                ' If there is a Name
                    For nRow = 4 To 300
                        If Range("D" & nRow).Value = sName Then
                            .Cells(nRow, nCol) = "v"
                        End If
                    Next nRow
                Else
                ' If there is no name
                    For nRow = 4 To 39
                        If Range("D" & nRow).Value = sName Then
                            .Cells(nRow, nCol) = "v"
                        End If
                    Next nRow
                End If
            End If
        Next nCol
    End With
End Sub

I haven't tested it, but the idea is right.

Now instead of:

BlackOut7 .Range("L16").Value, .Range("N16").Value, Worksheets("All")

You would have:

BlackOut .Range("L16").Value, .Range("N16").Value, Worksheets("All"), "Kurt"

Then when you need it without a Name:

BlackOut .Range("L6").Value, .Range("N6").Value, Worksheets("PMDavid H"), ""

My function runs through the first half where it fills out the individual sheets just fine but when it gets to the All sheet nothing happens.

   With Worksheets("Home")
        If Range("W6").Value = "True" Then
            BlackOut2 .Range("L6").Value, .Range("N6").Value, Worksheets("All")
        End If

Could the problem be that you don't have a dot before: Range("W6").Value??

Without the dot, Range refers to the active sheet, with the dot range refers to the With statement.

I'm not sure what you mean here. Each line of code is essentially

If Range("W22").Value = "True" Then
            BlackOut .Range("L22").Value, .Range("N22").Value, Worksheets("ENGJeff")
        End If

just with different names and worksheet names

I copied the new macro but now things seem worse. It seems to fill out the individual files correctly except that in row 10 it will never fill anything out. It's always row 10. And it still will not fill out the "All" sheet. I made all the If functions with the name and no name as you show, one if function for each case.

With Worksheets("Home")
        If Range("W6").Value = "True" Then
            BlackOut .Range("L6").Value, .Range("N6").Value, Worksheets("All"), "David H...."
        End If
            
        If Range("W8").Value = "True" Then
            BlackOut .Range("L8").Value, .Range("N8").Value, Worksheets("All"), "Dave S..."
        End If
            
        If Range("W10").Value = "True" Then
            BlackOut .Range("L10").Value, .Range("N10").Value, Worksheets("All"), "Tony"
        End If
            
        If Range("W12").Value = "True" Then
            BlackOut .Range("L12").Value, .Range("N12").Value, Worksheets("All"), "Sante"
        End If
            
        If Range("W14").Value = "True" Then
            BlackOut .Range("L14").Value, .Range("N14").Value, Worksheets("All"), "Nate"
        End If
            
        If Range("W16").Value = "True" Then
            BlackOut .Range("L16").Value, .Range("N16").Value, Worksheets("All"), "Kurt"
        End If
  
        If Range("W6").Value = "True" Then
            BlackOut .Range("L6").Value, .Range("N6").Value, Worksheets("PMDavid H"), ""
        End If
    
        If Range("W8").Value = "True" Then
            BlackOut .Range("L8").Value, .Range("N8").Value, Worksheets("PMDave S"), ""
        End If
            
        If Range("W10").Value = "True" Then
            BlackOut .Range("L10").Value, .Range("N10").Value, Worksheets("PMTony"), ""
        End If
            
        If Range("W12").Value = "True" Then
            BlackOut .Range("L12").Value, .Range("N12").Value, Worksheets("PMSante"), ""
        End If
            
        If Range("W14").Value = "True" Then
            BlackOut .Range("L14").Value, .Range("N14").Value, Worksheets("PMNate"), ""
        End If
            
        If Range("W16").Value = "True" Then
            BlackOut .Range("L16").Value, .Range("N16").Value, Worksheets("PMKurt"), ""
        End If
        
        If Range("W18").Value = "True" Then
            BlackOut .Range("L18").Value, .Range("N18").Value, Worksheets("ENGKeenan"), ""
        End If
        
        If Range("W20").Value = "True" Then
            BlackOut .Range("L20").Value, .Range("N20").Value, Worksheets("ENGJustin"), ""
        End If
        
        If Range("W22").Value = "True" Then
            BlackOut .Range("L22").Value, .Range("N22").Value, Worksheets("ENGJeff"), ""
        End If
    End With

Tha isn't the issue is it?

You have: If Range etc.

Instead of: If .Range etc.

See the dot before Range?

Without the dot, Range refers to the active worksheet.

With the dot, range goes with the with statement.

Is there anyway you can post your workbook here? (or email me your workbook?)

Staffing Sheet Example.xls

I changed it back to how I originally had the code.

See: Jeffjr02_Staffing Sheet Example_v2.xlsm

See if this works for you.

I unhid some columns, so you might want to re-hide them. But I think I got things working. Let me know.

That is fantastic. It seems to work perfectly! Thank you soooooo much!!!!