VBA Date Ranges 1

[mar050703]

I am trying to write a macro that looks at a combobox (with names) and 2 text boxes (with dates) and on the back of that puts an "H" against the relevant name and dates.

Therefore if Mr Smith has 1st Sept - 5th Off, then I see an "H" against his name and the relevant dates.

However if the range of dates includes a Sat or Sun then those days DO NO show anything. Therefore only a Mon to Fri would ever have "H" in the cell

To help I have a different sheet for each month within the workbook

 

[Bong]

First of all, what application are you building this macro within?

Second, have you tried anything so far? If so, what, and what problems have you encountered?

Depending on what application you're in, I'm not sure the ComboBox-TextBox-TextBox design is the best structure but that's really for you to say.

As for date computation, I'm not sure what your asking. Let's say that the leave span is 28-Aug-07 to 12-Sep-07.

d1=dateserial(2007,8,28)
d2=dateserial(2007,9,12)

So the length (in days) of leave is: d2-d1, or 15.

Now, suppose you have a holiday, 3-Sep-07:

h1=dateserial(2007,9,3)

So, does the holiday fall within the leave:

if h2>d1 and h2<d2 then ...?

_________________
Bob Rashkin

 

[xlbo]

....so what are you stuck on ?

btw - having a diffferent sheet for each month would not actually be helping - you would be better off with all data being stored on the same sheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

You have something in your head that you are not expressing in your question.

"if Mr Smith has 1st Sept - 5th Off"

What is it in the example stated that indicates whether or not Mr. Smith has a particular date range ON or OFF?

"To help I have a different sheet for each month within the workbook"

That's no help at all. In fact is a great detriment! Chopping data up into separate sheets is a very bad design strategy. It makes data maintenance, analaysis and reporting, orders of magnitude more difficult. Consider redesigning your workbook to have your source data in one sheet.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[mar050703]

Yeah sorry I am working in Excel.

For ease of red and find then different sheets per month is best.

The combobox is the name selection, Although thinking of grouping these into departments.

The two text boxes are the dates said person is on holiday. therefore if Mr Smith is booking leave from 01/09/07 to 05/09/07, I would want the code to Choose Sept (Month 09) and find Mr Smith's name (Column A) and then put an "H" in corresponding column(dates) providing the day is nto a weekend.

Does that help?

 

[Bong]

Why not just have the names and leave dates in cells? What do the textboxes do for you?

_________________
Bob Rashkin

 

[mar050703]

The VBA is a form that is filled out. The text boxes are the required dates someone wants, therefore I need to have the text boxes.

 

[Bong]

OK. Where are you stuck? What is it that you can't accomplish? [hint]Try the Macro Recorder[/hint]

_________________
Bob Rashkin

 

[mar050703]

I am not sure what the code should look like. The recorder will nto help, i need to select the relevatn sheet (dependant on month) and then find the corresponding name and place the "H" in the relvant cells thus showign Mr Smith has booked off 1st Sept to 5th Sept.

 

[SkipVought]

"...i need to select the relevatn sheet (dependant on month)"

As you can see, it is NOT easier to chop your data up into different sheets. Now you need code to find what would be a simple matter had you applied best and accepted practices, as has already been suggested by other experienced developers, but refused by you.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Bong]

So what do you want to do?

The name selected in the combobox will be:
sheet1.ComboBox1.text,
assuming that you're on "sheet1" and the combobox is called "ComboBox1".

You can't ask us "how would you do this?" since it's pretty clear none of us would do it this way in the first place. That's not to say it can't be done but you have to be more specific in what you're asking.

_________________
Bob Rashkin

 

[SkipVought]

This assignment could probably be accomplished with a very small amount of code, using native Excel spreadsheet functionality like AutoFilter, if all your data were in a single table. You are troubling YOURSELF and many others with a worthless pursuit.

It like insisting on using a screwdriver to drive home a nail. Can it be done? Maybe. But a professional chooses the correct tool.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Rofeu]

Hi,

not being a professional, I have to agree fully with the above.

That being said, it should be simple enough to find the relevant numbers with Worksheetfunction.Match.

Dim xRow as integer
Dim lCol as integer
Dim uCol as integer
Dim I as integer

xRow = worksheetfunction.match(ComboBox1.Value, Sheet1.Columns(1),0)
lCol = worksheetfunction.match(ComboBox2.Value, Sheet1.Rows(1),0)
uCol = worksheetfunction.match(ComboBox3.Value, Sheet1.Rows(1),0)

For I = lCol to uCol
sheet1.cells(xRow,I).Value = "H"
next

(code not tested)
Cheers,

Roel

 

[mar050703]

Thanks for the coding - only just got round to sorting it out, I get a message saying: Runtime error 1004 - Unable to get the Match function from the worksheet function class.

Any suggestions

Thanks

 

[xlbo]

Howzabout reading the help file for the MATCH function and seeing why it is bombing out - probably a piece of data it can't match

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Hi,

as I said, code is not tested.

Possibly, it doesn't like the direct col/row ref.

try Sheet1.Range("A:A") & Sheet1.Range("1:1") for Column(1) and Row(1) respectively.

Also data it can't match will result in an error. Either use On Error Resume Next and then test for the error and handle it accordingly, or dimension the variables as variants and test if one those returns an error.

Cheers,

Roel