?
Solved

#N/A - Wont read the date

Posted on 2006-06-12
7
Medium Priority
?
165 Views
Last Modified: 2010-04-30
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
Comment
Question by:ca1358
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 9

Expert Comment

by:lojk
ID: 16890238
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
 

Author Comment

by:ca1358
ID: 16897528
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
 
LVL 9

Expert Comment

by:lojk
ID: 16897755
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:lojk
ID: 16897763
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
 
LVL 9

Accepted Solution

by:
lojk earned 2000 total points
ID: 16897822
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
 

Author Comment

by:ca1358
ID: 16897894
Thank you!
0
 
LVL 9

Expert Comment

by:lojk
ID: 16897929
Got there in the end, eh?

You're Welcome, thx for points...

:-)
0

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 10 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question