put a value in a range of cells

[Jeffjr02]

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!

Please Login or Register  to view this content.

[StevenM]

Given that b is a Variant and:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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?

[Jeffjr02]

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!

[StevenM]

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.

Please Login or Register  to view this content.

[Jeffjr02]

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?

[Jeffjr02]

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.

Please Login or Register  to view this content.

[StevenM]

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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

[Jeffjr02]

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:

Please Login or Register  to view this content.

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!

[StevenM]

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:

Please Login or Register  to view this content.

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

Now instead of:

Please Login or Register  to view this content.

You would have:

Please Login or Register  to view this content.

Then when you need it without a Name:

Please Login or Register  to view this content.

[StevenM]

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.

Please Login or Register  to view this content.

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.

[Jeffjr02]

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.

Please Login or Register  to view this content.

Tha isn't the issue is it?

[Jeffjr02]

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

Please Login or Register  to view this content.

just with different names and worksheet names

[StevenM]

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?)

[Jeffjr02]

Staffing Sheet Example.xls

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

[StevenM]

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.

[Jeffjr02]

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