Fill in a cell based on a date requirement! 2

[McCondichie]

Hi

I'm trying to fill a range of cells based on three parameters.

-Start Date
-End Date
-Equipment.

Background Info: The equipment is held in the first column. All dates run along the first row. This is a booking type system which will show when equipment is booked out from and to. For example: If "Dell 1" is booked from the "01/01/07" to the "07/01/07", then I would like to fill the cells in the same row as "Dell 1" for the date range selected. The start date and end date are entered via a user form.

I have some knowledge of VBA code and have the code to fill the cells. I'm just not sure how to select the date range. Help would be much appreciated.

Many Thanks

Tony

 

[SkipVought]

Hi,

I recommend using Named Ranges, for On Sheet and Coding.

I'd Name the date range in row 1 and the equipment range in column A.

Then the range that you want to "fill in" is simple the intersection between the EntireColumn property of the From & Thru range in the date range and the EntireReo property of the specific equipment in the equipment range.

something like this...

dim rFrom as range, rThru as range, rEqip as range
set rFrom = rows(1).find(SelectedFromDate)
set rThru = rows(1).find(SelectedThruDate)
set rEqip = columns(1).find(SelectedEquip)

if not rFrom is nothing and _
   not rThru is nothing and _
   not rEqip is nothing then
   with application.intersect(range(rFrom, rThru).entirecolumn, rEqip.entirerow)
     'mark you cell here
     .value = "X"
   end with
end if


set rEqip = nothing
set rThru = nothing
set rFrom = nothing

Skip,

_[red][/red]

 

[McCondichie]

Hey Skip, thanks for the quick reply, I have tried the code you suggested and also tried it with some ammendments. I can't get it to work. I named the ranges as you said;

-dateRange
-equipmentRange

the code runs without a hitch but none of the cells are filled in. Am i right in saying that the variable names you choose should be the ones from my userform. i.e "SelectedFromDate" would be the equivelent of the user input? And the same for the following two? I seem to be stumped. Your help is much appreciated.

Thanks

Tony

 

[SkipVought]

How are you finding your dates? Should be something like...

    Set rFrom = Cells.Find(#3/1/2006#)

or

    dim d as date
'txtFrom is a textbox
    d = datevalue(txtFrom.text)
    Set rFrom = Cells.Find(d)

Skip,

_[red][/red]

 

[McCondichie]

this is the code which I am using! I have chosen to hard code the dates in to begin with and then eventually work it up for user inputs. I think the problem may be the way which I am referencing the equpiment name. "Dell 1" is the value in the equpment column.

Dim rFrom As Range, rThru As Range, rEqip As Range
Set rFrom = Rows(1).Find(#1/1/2007#)
Set rThru = Rows(1).Find(#2/1/2007#)
Set rEqip = Columns(1).Find("Dell 1")

If Not rFrom Is Nothing And _
   Not rThru Is Nothing And _
   Not rEqip Is Nothing Then
   With Application.Intersect(Range(rFrom, rThru).EntireColumn, rEqip.EntireRow)
     'mark you cell here
     .Value = "X"
   End With
End If


Set rEqip = Nothing
Set rThru = Nothing
Set rFrom = Nothing

Many thanks once again.

 

[SkipVought]

Try stepping thru your code. Do a Watch Window on each of the range objects Once you get past the Set statements, the range object values ought to be
[tt]
rFrom: #1/1/2007#
rThru: #2/1/2007#
rEqip: "Dell 1"
[/tt]
otherwise the FIND did not work. Are these values in Row 1 and Column A respectively?

You might also temporarily replace...

.value = "X"

with

.select

and observe what range is selected.

Skip,

_[red][/red]

 

[McCondichie]

After stepping throught the code - it takes all code lines into action with the exception of this statement

With Application.Intersect(Range(rFrom, rThru).EntireColumn, rEqip.EntireRow)
     'mark you cell here
     .Value = "X"
   End With

It seems to step over this action, i presume because the arguments set before hand aren't met. I'm kinda lost, apologies.

Both ranges are in Row 1 and column A respectively. I also tried what you suggested and changed it to .select but this step was skipped too.

This is the way the data looks-


A B C D
01/01/2007 02/01/2007 .....
Dell 1
Dell 2
...
...

Many Thanks for your patience with this

 

[SkipVought]

...which is because...

If Not rFrom Is Nothing And _
   Not rThru Is Nothing And _
   Not rEqip Is Nothing Then

is NOT TRUE.

ONE or more of these is not getting SET by the Find statement.

Which one(s)? THAT was my former question to you.

WHICH ONE(s)?

Skip,

_[red][/red]

 

[McCondichie]

Apologies for not answering the question - was slightly confused at first on how to set up watches but I have used other debuggers and figured it out - anyway;

I created the watches and found that it finds rFrom, but does not find rThru. It does find rEqip. So this means that rThru has got to be the problem. However, I'm clueless as to why it doesn't find the rThru value defined as it is in my sheet. Could it possibly be because I have already perfomed a find on that range???

Thanks again

T

 

[PHV]

Use non ambigue date:
Set rThru = Rows(1).Find(#2007-01-02#)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[McCondichie]

That works perfectly - thank you for all your help. However, I have one more question, how do I change the format of the date so that it searches for the format dd/mm/yyyy?

Much Apreciated

T

 

[SkipVought]

Dates are NOT formats -- they are NUMBERS. The format does not matter.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_[red][/red]

 

[McCondichie]

Ok cool, i think i get it. My last question is - I'm trying to pass in user entered data for all the parameters you helped me set. I have created a userform which will get the data but i don't seem to be able to pass it into the code successfully. I thought it would be the following code given what I have named the values;

-rFrom = a datetime picker named "DTPickerStart"
-rThru = a datetime picker named "DTPickerEnd"
-rEqip = a Combobox named "CBEquipment"

I thought that to get the values that the user inputs into the form i would have to modify the code as below - but it does not work. Your help is again greatly appreciated Skip.

Set rFrom = Rows(1).Find(DTPickerStart)
Set rThru = Rows(1).Find(DTPickerEnd)
Set rEqip = Columns(1).Find(CBequipment)

Thanks

T

 

[SkipVought]

try this...

Set rFrom = Rows(1).Find(DateValue(DTPickerStart.Value))
Set rThru = Rows(1).Find(DateValue(DTPickerEnd.Value))
Set rEqip = Columns(1).Find(CBequipment.Value)

the datepicker and combobox are OBJECTS. You must use the Text or Value property of the object

Skip,

_[red][/red]

 

[McCondichie]

I tired was you suggested but to no avail. Could it be that I need to use the text or value property of the equipment item also but define it like you have defined "DateValue". Sorry to be a pain on this

Thanks

T

 

[SkipVought]

Have you ever used the Warch Window in the VB Editor>

Check the VALUES of, for instance...
[tt]
rFrom.Value
DateValue(DTPickerStart.Value)
DTPickerStart.Value
[/tt]


Skip,

_[red][/red]

 

[McCondichie]

Ah my silly spelling - My biggest apologies Skip. The code was not executing because of a spelling mistake which I had made. After doing the watches you helped with and also executing the final code, it now works. Thanks for all your help on this it is much appreciated. I have accredited you in my code for your input and if you would like a full working version of what I produced, let me know and I'll arrange to get it to you.

Many Thanks

T

 

[SkipVought]

No problem -- been there - done that.

Thanks for the credit. Post the final results if you like.

Skip,

_[red][/red]