How to use the worksheetfunction Vlookup

[R Tanner]

Hi----this is part of a much larger program, that I am testing
separately, if you are wondering what the heck I would use it
for.

I am trying to use the worksheet function vlookup in my code but it
keeps telling me

'unable to use the worksheet function vlookup class' when it gets to
that point in the code...

any idea how I should code this differently to make it work??

Thanks...


Sub makedayofweek()
Dim RANGEENTRIES As Range, NUMENTRIES As Integer, Mydate As Range,
Test As Range



Range("B22").Select

Set RANGEENTRIES = Range("B22", ActiveCell.End(xlToRight))
RANGEENTRIES.Select
NUMENTRIES = RANGEENTRIES.Columns.Count
Range("B12").Select

For i = 1 To NUMENTRIES
Set WDAY = ActiveCell
WDAY.Select
ActiveCell =
Application.WorksheetFunction.Weekday(ActiveCell.Offset(10, 0), 2)
ActiveCell.Offset(0, 1).Select
Next i

Set Test = ActiveCell.Offset(1, 0)


Range("B11").Select
For i = 1 To NUMENTRIES
Set Mydate = ActiveCell
Mydate.Value = Application.WorksheetFunction.VLookup(Test,
Range("E53:E59"), 2)
Mydate.Offset(0, 1).Select
Next i



End Sub

 

[R Tanner]

I figured out the problem deals with the 'Test' variable I am
using...I can't figure out how else to reference this though...

 

[R Tanner]

I guess I didn't have to give you the whole sub...this should suffice
and make it easier on the eyes...

Test = ActiveCell.Offset(1, 0).Value

For i = 1 To NUMENTRIES
Set Mydate = ActiveCell
Mydate.Value = Application.WorksheetFunction.VLookup(Test,
Range("E53:E59"), 2)
Mydate.Offset(0, 1).Select
Next i

again, it is something wrong with the 'Test' variable in the code...

 

[R Tanner]

I guess I didn't have to give you the whole sub...this should suffice
and make it easier on the eyes...

Test = ActiveCell.Offset(1, 0).Value

For i = 1 To NUMENTRIES
Set Mydate = ActiveCell
Mydate.Value = Application.WorksheetFunction.VLookup(Test,
Range("E53:E59"), 2)
Mydate.Offset(0, 1).Select
Next i

again, it is something wrong with the 'Test' variable in the code...

 

[JP]

Your lookup range is only one dimension, how can return the 2nd column
from a single-column range?

--JP

 

[R Tanner]

oops...that was a typo on here. My code actually had E53:F59

what about this one? It works fine, but gives me a '#REF!' error...

cell = Range("B12").Value

ActiveCell = Application.VLookup(cell, Range("E53:E59"), 2, True)

 

[JP]

Another typo, don't you mean

ActiveCell = Application.VLookup(cell, Range("E53:F59"), 2, True)

?

My understanding is that the VBA version of vlookup returns an error
if the value isn't found. So test it with an example you KNOW should
work, then we can proceed from there.

FYI in your original example, "Test" is a Range object, you can't do
vlookup on a range object, only on values or cell references.

" Mydate.Value = Application.WorksheetFunction.VLookup(Test,
Range("E53:E59"), 2) "


HTH,
JP

 

[R Tanner]

Another typo, don't you mean

ActiveCell = Application.VLookup(cell, Range("E53:F59"), 2, True)

?

My understanding is that the VBA version of vlookup returns an error
if the value isn't found. So test it with an example you KNOW should
work, then we can proceed from there.

FYI in your original example, "Test" is a Range object, you can't do
vlookup on a range object, only on values or cell references.

" Mydate.Value = Application.WorksheetFunction.VLookup(Test,
Range("E53:E59"), 2) "

HTH,
JP

I thank you for your help JP...Unfortunately I have run into some
bigger problems because alot of my rows are hidden and this has caused
problems with aggregating values in my rows and transferring them to
another spreadsheet...so I will not be spending time on this for a
bit...