• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

#N/A - Wont read the date

I have this piece of code.  I have three combo boxes, the second and third  is dependant on the first one.  After you pick you choice in the second one, I need to go do a vlookup

The lookup range is as follows:

 Col B                  Col 15
JUL                  7
06/09/2006                  7
06/15/2006            7
06/27/2006            7
06/30/2006            7
AUG                  8
07/12/2006            8
07/19/2006            8
07/22/2006            8
07/31/2006            8


If I pick the “JUL” ,  in the combo box , it returns 7 in R20 or pick the “AUG” , returns 8
But if I pick one of the dates I get “#N/A”

Any help would be appreciated!

This is the part I am having trouble with.
Range("R20") = Application.VLookup(cboSecondary.Value, Worksheets("data").Range("b2:r19"), 15, False)
/////////////////////////////////////////////////////////////////////////

Private Sub cboSecondary_Change()
Dim mysheet As Worksheet
Set mysheet = ActiveSheet


Application.EnableEvents = False
mysheet.Unprotect
Sheet22.Range("x28:x57") = cboSecondary.Value 'Copy Delivery Date Column X

Range("R20") = Application.VLookup(cboSecondary.Value, Worksheets("data").Range("b2:r19"), 15, False)

Application.EnableEvents = True


0
ca1358
Asked:
ca1358
  • 5
  • 2
1 Solution
 
lojk.Net and Infrastructure ConsultantCommented:
assuming the column is wide enough to display the data, is this not a case of incorrect date formatting? How is R20 formatted? As a date?

I.e. the actual result returned is

7 divided by 31 divided by 2006

which could quite easily equate to roughly zero.

perhaps instead of...

Range("R20") = Application.VLookup(cboSecondary.Value, Worksheets("data").Range("b2:r19"), 15, False)

you could...

Range("R20") = Application.VLookup(month(cboSecondary.Value), Worksheets("data").Range("b2:r19"), 15, False)


0
 
ca1358Author Commented:
I tried the “month” but that didn’t work.  

I think my problem is the combo box is a text field and the “Data” sheet if formatted as “Dates”.  

I came up with this but it doesn’t work either.

Any help would be appreciated.  I been working non stop for 2 days and coming up with nothing.

//////////////////////////////////////////////////
Private Sub cboSecondary_Change()
Dim mysheet As Worksheet
Set mysheet = ActiveSheet

Dim Date1 As String
Date1 = cboSecondary.Value
cboSecondary.Value = FormatDateTime(Date1, vbgeneral)


Application.EnableEvents = False
mysheet.Unprotect
Sheet22.Range("x28:x57") = cboSecondary.Value 'Copy Delivery Date Column X

Range("R20") = Application.VLookup(cboSecondary.Value, Worksheets("data").Range("b2:r19"), 15, False)

Application.EnableEvents = True




End Sub
0
 
lojk.Net and Infrastructure ConsultantCommented:
i get it now...

Range("R20") = Application.VLookup(cboSecondary.Value, Worksheets("data").Range("b2:r19"), 15, False)


here you are using the cboSecondary.Value surely you should be using cboSecondary.SelectedItem to return the text of the selected item instead?

Exactly how are the values in the lookups stored? i presume they are preceeded by a ' to make them text? may be worth doing

Range("R20") = Application.VLookup("'" & cboSecondary.SelectedItem, Worksheets("data").Range("b2:r19"), 15, False)

In fact this doesnt make ay sense at all anyway... why do a lookup when you already know the value?

even this hack should be better than what you are doing (i.e wasting two days with a silly issue)

if len(cbosecondary.selecteditem)=3 then
Range("R20") =month( "01" &  cboSecondary.SelectedItem & year (now))
else
Range("R20") =month(    cboSecondary.SelectedItem )
endif

How about that?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
lojk.Net and Infrastructure ConsultantCommented:
sorry according to your lookup table this should actually be...

if len(cbosecondary.selecteditem)=3 then
Range("R20") =month( "01" &  cboSecondary.SelectedItem & year (now))
else
Range("R20") =month(    cboSecondary.SelectedItem )+1
endif
0
 
lojk.Net and Infrastructure ConsultantCommented:
grrr more speed less haste...

if len(cbosecondary.selecteditem)=3 then
Range("R20") =month( "01"  &" " &  cboSecondary.SelectedItem &" " & year (now))
else
Range("R20") =month(    cboSecondary.SelectedItem )+1
endif

well you get the idea anyway....

0
 
ca1358Author Commented:
Thank you!
0
 
lojk.Net and Infrastructure ConsultantCommented:
Got there in the end, eh?

You're Welcome, thx for points...

:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now