Beginner struggling with if, then, else if etc...

[Lindsey]

Ok, I just can't see why this isn't working but I am VERY new to all this so it could be something horribly simple...

I have a Userform which my colleagues complete to book out equipment which then completes my Equipment Tracker spreadsheet.
The spreadsheet has, among others, the following four columns Location, Return Date, Next Location and Shipping Date.
I have 2 of item X and am trying the following code:

To check if the first of Item X is already with our company or, if it isn't, that it will return before the requested shipping date I have:

If chkItemX.Value = True And Range("D10").Value = "Company Name" Or Range("E10").Value(Return Date) < txtShippingDate.Value Then
Range("F10").Value = txtEvent.Text
Range("G10").Value = txtShippingDate.Text

If the first of Item X is not with our company and if it will not return before the requested shipping date then the second of Item X needs to be looked at to see if it is with our company or not and if not, whether it will return in time for the requested shipping date:

ElseIf chkItemX.Value = True And Range("D10").Value <> "Company Name" And Range("E10").Value (Return Date of 1st Item X) > txtShippingDate.Value And Range("D11").Value = "Company Name" Or Range("E11").Value (Return Date of 2nd Item X) < txtShipping.Value Then
Range("F11").Value = txtEvent.Text
Range("G11").Value = txtShippingDate.Text

If neither of the two Item X's are with our company or are being returned in time for the requested shipping date I want a message box to appear:

ElseIf chkItemX.Value = True And Range("D10").Value <> "Company Name" And Range("D11").Value <> "Company Name" And Range("E10").Value (Return Date of 1st Item X) > txtShippingDate.Value And Range("E11").Value (Return Date of 2nd Item X) > txtShippingDate.Value Then
MsgBox "Item X Unavailable"
ElseIf chkItemX.Value = False Then

End If

The problem I'm having is with the Shipping Dates, I've given up on using dates to test whether this works as I'm struggling with a US/UK date compatibility issue so I'm just using values to get an idea.
The trouble is it doesn't seem to matter whether my requested shipping date value is higher or lower than the return date, it still loads in the requested information to the spreadsheet.

Any ideas what I'm doing wrong???

If this all sounds like rubbish, just say....

Thank you, L.

[Bob Phillips]

It is probably comparing dates to stringd, so try casting the text to date

CDate(txtShippingDate.Value)

and your date comparisons might work.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lindsey" <[email protected]> wrote in
message news:[email protected]...
>
> Ok, I just can't see why this isn't working but I am VERY new to all
> this so it could be something horribly simple...
>
> I have a Userform which my colleagues complete to book out equipment
> which then completes my Equipment Tracker spreadsheet.
> The spreadsheet has, among others, the following four columns Location,
> Return Date, Next Location and Shipping Date.
> I have 2 of item X and am trying the following code:
>
> To check if the first of Item X is already with our company or, if it
> isn't, that it will return before the requested shipping date I have:
>
> If chkItemX.Value = True And Range("D10").Value = "Company Name" Or
> Range("E10").Value(Return Date) < txtShippingDate.Value Then
> Range("F10").Value = txtEvent.Text
> Range("G10").Value = txtShippingDate.Text
>
> If the first of Item X is not with our company and if it will not
> return before the requested shipping date then the second of Item X
> needs to be looked at to see if it is with our company or not and if
> not, whether it will return in time for the requested shipping date:
>
> ElseIf chkItemX.Value = True And Range("D10").Value <> "Company Name"
> And Range("E10").Value (Return Date of 1st Item X) >
> txtShippingDate.Value And Range("D11").Value = "Company Name" Or
> Range("E11").Value (Return Date of 2nd Item X) < txtShipping.Value
> Then
> Range("F11").Value = txtEvent.Text
> Range("G11").Value = txtShippingDate.Text
>
> If neither of the two Item X's are with our company or are being
> returned in time for the requested shipping date I want a message box
> to appear:
>
> ElseIf chkItemX.Value = True And Range("D10").Value <> "Company Name"
> And Range("D11").Value <> "Company Name" And Range("E10").Value (Return
> Date of 1st Item X) > txtShippingDate.Value And Range("E11").Value
> (Return Date of 2nd Item X) > txtShippingDate.Value Then
> MsgBox "Item X Unavailable"
> ElseIf chkItemX.Value = False Then
>
> End If
>
> The problem I'm having is with the Shipping Dates, I've given up on
> using dates to test whether this works as I'm struggling with a US/UK
> date compatibility issue so I'm just using values to get an idea.
> The trouble is it doesn't seem to matter whether my requested shipping
> date value is higher or lower than the return date, it still loads in
> the requested information to the spreadsheet.
>
> Any ideas what I'm doing wrong???
>
> If this all sounds like rubbish, just say....
>
> Thank you, L.
>
>
> --
> Lindsey
> ------------------------------------------------------------------------
> Lindsey's Profile:
http://www.excelforum.com/member.php...o&userid=29095
> View this thread: http://www.excelforum.com/showthread...hreadid=488802
>

[Lindsey]

You are a genius! That works a treat!

Now all I have to do is fix my date issue - if I type dates directly into the speadsheet in UK format, they apply in UK format. If I type dates into the Userform in UK format however, they load into the speadsheet in US........aargh.....so close!

L