help modify code to include Vlookup for excel

[jkingarena]

I have a code that will automatically put the date i start a new row. It works, but I would like this code to also do a lookup based on the date and return details about the date. A regular lookup doesn't work for me because each entry I make is a new row.

For example If I were to use the lookup below, I want the computer to automatically show "Valentines day, on an entry I filled out on 2/14. ( Please be aware my list of dates is much longer than the example list by several months)

ie
2/14 Valentines day
2/15 Work
2/16 Vacation
etc.....

Here is my code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 7 Then
ThisRow = Target.Row
If Range("H" & ThisRow).Value = "" Then
If Target.Value = "" Then
Range("I" & ThisRow).Value = 0
ElseIf Target.Value <> 1.23456 Then
Range("H" & ThisRow).Value = Date
End If
End If
End If

End Sub

 

[SkipVought]

Just use VLOOKUP

sLookupReturn = application.vlookup(TheDateValue,The LookupTableRangeObject,2,false)

it might look like...

sLookupReturn = application.vlookup(TheDateValue,Sheets("MyCalendarTableSheet").[A1].Currentregion,2,false)

assuming that you have your lookup table on sheet MyCalendarTableSheet, starting in A1.




Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[jkingarena]

thank you Skip, I included the line into my code. I although there is some trouble shooting required, I am much closer than before.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 7 Then
ThisRow = Target.Row
If Range("H" & ThisRow).Value = "" Then
If Target.Value = "" Then
Range("I" & ThisRow).Value = 0
ElseIf Target.Value <> 1.23456 Then
Range("H" & ThisRow).Value = Date
sLookupReturn = Application.VLookup(TheDateValue, Sheets("Sheet10").[A1].CurrentRegion, 2, False)
Range("I" & ThisRow).Value = sLookupReturn
End If
End If
End If
End Sub

But I get an error message "Run-time error '9', subscript out of range. I don't completely understand what each part of the code does, but I thought you might be able to advise me.

The lookup material is from sheet10, A1:B24. The A column is the date information, while the B column is what I want to appear. Should I put something inplace of TheDateValue?

 

[SkipVought]

"Should I put something inplace of TheDateValue?"

It seems that you have nothing assigned to TheDateValue in your code.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[jkingarena]

I modifyed the code to include "TheDateValue = Date", but it is still getting hung up at "sLookupReturn = Application.VLookup(TheDateValue, Sheets("Sheet10").[A1].CurrentRegion, 2, False)" with "Run-time error '9', subscript out of range".


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 7 Then
ThisRow = Target.Row
If Range("H" & ThisRow).Value = "" Then
If Target.Value = "" Then
Range("I" & ThisRow).Value = 0
ElseIf Target.Value <> 1.23456 Then
Range("H" & ThisRow).Value = Date
TheDateValue = Date
sLookupReturn = Application.VLookup(TheDateValue, Sheets("Sheet10").[A1].CurrentRegion, 2, False)
Range("I" & ThisRow).Value = sLookupReturn
End If
End If
End If

End Sub

Thankyou for your help.

 

[SkipVought]

"subscript out of range" usually means an object that does not exist, ie Sheet10 does not exist.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[jkingarena]

Thankyou I got the error message to go away. It keeps saying #N/A, so I think "...[A1].Currentregion,2,false)" might be wrong. I am unable to find any reference material on this command. I could adjust it to fit my workbook if I knew what it was doing. What is that line of code telling my computer to do? I feel that with this last bit of information, This cade will be ready to use, I thank you for yourhelp and patience.


Justin King.

 

[SkipVought]

"It keeps saying #N/A"

That's a Spreadsheet notation, NOT a VBA notation. Where is THAT coming from???

faq707-4594

What does it indicate when you do a watch on Sheets("Sheet10") - when it errors, hit Debug, Highlight Sheets("Sheet10") and hit View Watch Window?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[jkingarena]

I modified the code, to this. It does the #N/A. I want A1:B42, maybe I placed it at the wrong place.

The watch on this says,
a1, b42, Date, Lookup, and CurrentRegion are Expressions not defined in Context
Sheets says it is Out of context

sLookupReturn = Application.VLookup(TheDateValue, Sheets("Date Lookup").[A1:b42].CurrentRegion, 2, False)
Range("I" & ThisRow).Value = sLookupReturn

Thankyou for your help

 

[SkipVought]

"...are Expressions not defined in Context"

which is absolutely correct. WHY are you doing a watch on the PIECES???

You do the watch on...
[tt]
Sheets("Date Lookup")
or
Sheets("Date Lookup").[A1:b42]
or
Sheets("Date Lookup").[A1:b42].CurrentRegion
[/tt]
each of those expressions are in context.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[jkingarena]

Sheets("Date Lookup")
<Out of Context>
Empty
Sheet7.Worksheet_Change

Sheets("Date Lookup").[A1:b42]
<Out of Context>
Variant/Empty
Sheet7.Worksheet_Change

Sheets("Date Lookup").[A1:b42].CurrentRegion
<Out of Context>
Variant/Empty
Sheet7.Worksheet_Change

 

[SkipVought]

"Sheets("Date Lookup")
<Out of Context>"

There's you answer. VB cannot find that sheet. LOOK NO FURTHER.

Where's the Sheet?

Did you misstype? Are you in the correct workbook?


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[jkingarena]

this is the new line. I used the name that was available in the properties tab of visual basic.

sLookupReturn = Application.VLookup(TheDateValue, Sheets("Sheet10").[A1:B42].CurrentRegion, 2, False)


now that I have made these changes, the debugger says subscript out of range. Integer.
If I take the quotes off of it, it says Type Mismatch, Integer.

I get the feeling that it shouldn't say integer.

 

[SkipVought]

sLookupReturn = Application.VLookup(TheDateValue, Sheets("Sheet10").[A1:B42][b][s].CurrentRegion[/s][/b], 2, False)

If you are EXPLICITLY defining the range, no need for CurrentRegion.

Again subscript out of range means that an object does not exist. What does the Watch Window indicate about your objects and variables in this statement???


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[jkingarena]

I renamed the item. The code does not have any run time errors. The code still references #N/A in the lookup field. Using the watch, I know that I am referencing the correct sheet. A1:B42 are the correct fields in the sheet. I am guessing that 2 is telling it to retrieve the information from column two, and I'm not sure what False does.

I just did a second check.now everything says out of context. What could have caused everything to change?

 

[SkipVought]

2 means that you are returning data from the SECOND column in the given lookup range.

False, means look for an EXACT match.
What value are you looking up and is there and exact match for it (including any leading or trailing spaces)???

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[jkingarena]

The computer generates the date the change was made, and uses that to reference a date column. I imput like 1/18 and the computer automatically converts it to 1/18/2008. Is this auto formating the same as just typing the full date in?

 

[SkipVought]

faq68-5827

When you enter 1/18, Excel, helpfully, assumes that you wanted to enter a date, assuming that 1 is the month, 18 is the day and the CURRENT YEAR is the year. What you enter is CHARACTERS, not a date. Excel takes the 1, 18 and current year and CONVERTS those values to a Date Value, which is a NUMBER--change the cell number format to GENERAL and see the NUMBER.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}