Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

#N/A - Wont read the date

Posted on 2006-06-12
7
Medium Priority
?
166 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

618 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