Solved

#N/A - Wont read the date

Posted on 2006-06-12
7
160 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now