VLookup and Dates formats help need!



Im trying to develop a calender program in macros for my job. This calender will look for a specific date in a table, and would move the info from that table into a Calender that is already finished. The Main problem I have is that each time I look for a date, "error 424 object required" pops out. But when I look for values the program seems to be ok. Ithink is problem of format, but I dont know what the problem really is. Can anyone give me a hand? here is the code:

Sub calender()

Dim Cont As Date
'Dim A As Integer

On Error GoTo errHandler
'A = 1
Sheet1.Range("D6").Value = Application.WorksheetFunction.HLookup(CLng(CDate(Sheet1.Range("D40"))), Sheet2.Range("B2:AK6"), 1, False)
'Cont = Sheet1.Range("B31").Value

Exit Sub


MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

End Sub

Thanks for your help,




Selected Answer

I wouldn't use HLOOKUP on principle, the principle being that I use worksheetfunctions on worksheets and VBA functions in VBA. Whenever you want to use worksheetfunctions in VBA use the type adapted to VBA, meaning Application.Hlookup() rather than Applciation.Worksheetfunction.Hlookup(). But when it comes to HLOOKUP I wouldn't use it at all because it is harder to set up in VBA (otherwise you wouldn't have a question) and harder to trap errors. I would use code like this:-

Sub MyCalender()
    Dim Cont(1) As String
    Dim LookFor As Date
    Dim Fnd As Range
    LookFor = Sheet1.Cells(40, "D").Value       'CLng(Sheet1.Range("D40"))
    ' you might modify the LookFor value like this:
    ' LookFor = CDate(Format(.Cells(40, "D").Value, "dd/mm/yyyy"))
    With Sheet2.Range("B2:AK2")
        Set Fnd = .Find(What:=LookFor, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
    End With
    If Fnd Is Nothing Then
        MsgBox "The date " & Format(LookFor, "dd/mm/yyyy") & _
               " wasn't found.", vbInformation, "Invalid date"
        Cont(0) = Sheet1.Cells(3, "B").Value
        Cont(1) = Sheet2.Cells(1, Fnd.Column).Value
        Sheet1.Cells(6, "D").Value = Join(Cont, " ")      ' = .Range("D6").Value = Fnd.Value
    End If
End Sub

First you declare what you look for. That would be the Date "LookFor".
Then you declare where to look. I did this in the With statement.
The Find method will return a range object. That was declard at the top and is now used.

The key to this code is in the date format. The format in D40 must be exactly the same as in B2:AK2. I tried

LookFor = CDate(Format(.Cells(40, "D").Value, "dd/mm/yyyy"))
, meaning if D40 has a different format you can adapt it to the format in the search range.

Excel will start the search in the cell after the specified one, search tot he end of the range and loop back to the beginning (as determined by SearchDirection). Therefore the code specifies the last cell to start with, which means it will start with cell 1.

You are looking at values (not formulas) and the whole cell content (not some part of it). HLOOKUP doesn't offer you a control panel like that.

Now, if no match is found the Fnd range will be empty and an error message can be issued. Else Fnd specifies a range which has all the properties of a range, including Value, Row and Column. HLOOKUP would use the Column property and then find a cell at an offset to Fnd. You don't need that because you want the found cell itself.

I kept traces of your original code in the above to help you find your bearings.



Thanks a lot, really apreciated it, but the code should look for the date in a table that is on sheet2 and not in sheet1. When  I try to change the range, only the nothing message is shown.
[With Sheet2.Range("B3:AK3")]

and the thing is that once the date is found in table in sheet2, they should concatenate two cells where is the name of the project for that date. Example:B3= Buy and column Z1= sugar, so if the lookup date is in Z2, put in the calender the name concatenate between B3 and Z1. So the row name and column name give us the activity name.
thnks for the help and sorry for all the troubles.
Jonacz (rep: 4) Aug 22, '17 at 10:34 am
Sub calender()
Dim Cont As Date

On Error GoTo errHandler
Sheet1.Range("D6").Value = Application.HLookup(Sheet1.Range("D40"), Sheet2.Range("B2:AK6"), 1, False)
Exit Sub
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

End Sub
Jonacz (rep: 4) Aug 22, '17 at 1:29 pm
I followed your instructions and took out the ".worksheet" and the program starts to work, but now the text that should be return is not working. It only return #N/A to the cell, the text is in standard format.
Jonacz (rep: 4) Aug 22, '17 at 1:31 pm
Sorry about mixing up the sheet references. I have corrected that in the above code. I also added code to do a concatenation. Unfortunately, I don't quite understand what to concatenate but I wrote the code in such a way that you can probably do any required adjustments yourself. My remaining concern is with the date formats. Basically, the Find function will look for the string on display in the searched range, not the value in the cell. But the only reasonable way to produce exactly that string is to use Excel's own way of creating it from a date value both in the searched range and the find string.
Variatus (rep: 813) Aug 22, '17 at 9:08 pm
Thanks a lot really appreciated it, the code is easy to understand. The only problem I got with it, is that the "Lookfor Format" is nor matching the to dates even when they are just the same, I will try to find it a solution. But any suggestion is welcome. And thanks again for the help
Jonacz (rep: 4) Aug 23, '17 at 11:06 am
How are the dates created? Both in the 'LookFor' cell and the 'SearchIn' range all cells must have numbers in them, integers like 42692. To check, set their number format to 'General' and take a look. The problem isn't from the code. It's from the way you enter the dates. In Excel, dates which look the same are not the same.
Variatus (rep: 813) Aug 23, '17 at 9:59 pm
Thanks! yes the problems were the dates format, so had to changed all in the schedule. What happend if there are two items that share the same date into D6? Is there any function that let me add them both into a cell without earesing the other, or keep adding if more items share the same date? Because,when I run it and some items share the same date, it only let me introduce one of them and not all.

Sorry if I asking too much, is the final thing that is need it, and im litlle bit rust in macros and running out of time.

Thanks for your help Really!
Jonacz (rep: 4) Aug 24, '17 at 11:09 am
No. You can't have more than one date in a cell. You might convert the dates to strings and have several strings in a cell each of which looks like a date but to do calculations with them will be very cumbersome, perhaps impossible. And then there would be the problem of adjusting the cell size to a perhaps unknown number of dates in it. The better solution might be to combine several cells into a frame, if you can make that work for you. Anyway, we are getting beyond the limits of this thread, and if you need more help I suggest you ask a new question. Good luck!
Variatus (rep: 813) Aug 24, '17 at 2:37 pm
Thanks for your help
Jonacz (rep: 4) Aug 25, '17 at 9:32 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login