Solved

#N/A - Wont read the date

Posted on 2006-06-12
7
161 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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel object stays open 19 76
VBA to copy paste columns form one file to other 20 87
to transfer string from C lanaguage to VBA 4 66
Hide vba in gp 7 95
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

773 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