Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unable to get the vlookup property

Status
Not open for further replies.
wafs

wafs

Technical User
Jan 17, 2006
112
US
I have this vlookup in my code and I get the following error: unable to get the vlookup property of the worksheet function class. Can anyone help me on this.

Sub update_headers()

'====================================================================
' UPDATE HEADER SHEETS
'
'Description: Automatically updates header sheets with header you request
'
'Macro Created on 3/16/2006
'
'Written by Wendy Smith
'=========================================================================

Dim cellstart As Integer 'starting point of informaiton (row)
Dim commentstart As Integer 'starting point for comments (row)
Dim p As Integer
Dim c As Integer 'for comments
Dim h As Integer 'for comments
Dim x As Integer
Dim r As String 'counting variable for comments
Dim n As String 'counting variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim TestRange4 As String
Dim TestRange5 As String
Dim TestRange6 As String 'Test String for notes
Dim TestRange7 As String 'Test String for notes
Dim TestRange8 As String 'Test String for notes
Dim TestRange9 As String 'test string for notes
Dim m As String
Dim y As String 'for comments
Dim sh As String 'source sheet
Dim destsh As String 'dest sheet
Dim notes As String 'source sheet for comments
Dim reportlocation As String
Dim sFirstAddress As String
Dim header As String 'header you want to run Holds header
Dim output(1 To 8) As String
Dim myrange As Range
Dim rsearch As Range


cellstart = 3 'Starting point of source sheet
commentsstart = 4 'starting point for comments increase this number only if you need more comment space
p = 0
c = 0
header = InputBox("Which header do you want to run?", "header") 'input the requested header

TestRange = header
sh = "Daily" 'Work sheet where the header information is kept
destsh = header ' desination sheet for header
notes = "Notes" 'work sheet for notes

'Clear out old Data and unhide rows

sheets(destsh).Range("A4:A100").Select
Selection.ClearContents
Selection.EntireRow.Hidden = False

n = cellstart
r = commentsstart

TestRange2 = "B" + n 'column where to match header request
TestRange3 = "A" + n 'part number
TestRange4 = "AF" + n 'over 60 days
TestRange5 = "AE" + n 'furture 6
For x = 1 To 1000 'increase this number only if daily sheet has more than 1000 parts
If UCase(Trim(TestRange)) = UCase(Trim(sheets(sh).Range(TestRange2))) And UCase(Trim(sheets(sh).Range(TestRange4) < 61)) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
reportlocation = "CA" + m
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n
ElseIf UCase(Trim(TestRange)) = UCase(Trim(sheets(sh).Range(TestRange2))) And UCase(Trim(sheets(sh).Range(TestRange4) > 60)) And UCase(Trim(sheets(sh).Range(TestRange5) > 0)) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
reportlocation = "CA" + m
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n
Else
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n

End If
Next

'Hide empty rows
m = m + 1
reportlocation = "A" + m
Do While sheets(destsh).Range(reportlocation).Value = ""
sheets(destsh).Range(reportlocation).EntireRow.Hidden = True
m = m + 1
reportlocation = "A" + m
Loop

'Move Comments
sheets(destsh).Range("A104:B204").Select
Selection.ClearContents

output(7) = "CA" + r 'dest sheet part numbers
output(8) = sheets(destsh).Range(output(7)).Text
TestRange9 = output(8)
output(1) = "A" + r 'part numbers
output(2) = sheets(notes).Range(output(1)).Value
TestRange8 = output(2)
output(5) = "C" + r 'comments
output(6) = sheets(notes).Range(output(5)).Text
TestRange7 = output(6)


Do

Set myrange = sheets(destsh).Range(output(7))
Set rsearch = sheets(notes).Range(output(1))
'Set reportlocation2 = sheets(notes).Range(output(5))
Set rsearch = myrange.Find(what:=sheets(destsh).Range(output(7)), after:=myrange.Cells(myrange.Cells.Count), lookat:=xlWhole)
If Not myrange Is Nothing Then

sFirstAddress = rsearch.Address

If rsearch = myrange And reportlocation2 <> "" Then

c = c + 1
y = c + 103 'Tells where to start importing comments
reportlocation = "A" + y 'Starting row of part numbers for comments
rsearch.Copy sheets(destsh).Range(reportlocation)
reportlocation = "B" + y 'comments
-----------> rsearch = Application.WorksheetFunction.vlookup(A104, Worksheets("Notes").Range("A4:C400"), 3)
'reportlocation2.Copy sheets(destsh).Range(reportlocation)
'Set myrange = rsearch.FindNext(myrange)
Set rsearch = myrange.FindNext(rsearch)
End If

End If
r = r + 1
output(7) = "CA" + r
'output(5) = "C" + r
Loop Until myrange = ""

End Sub

 
Sort by date Sort by votes


Code: 
dim vRet as variant
vRet  = Application.vlookup([A104], Worksheets("Notes").Range("A4:C400"), 3)
if not iserror(vRet) then
  'now use vRet 
end
A104 is a cell reference, hence [A104] or sheetref.range("A104")

rsearch is defined as an object

Skip,

[glasses] [red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [tongue]
 
Upvote 0 Downvote
Got vlookup working. Thanks for the help.

Now the next questions would be how to get the information to copy over to destsh. I tried the .copy, but it did not work.
 
Upvote 0 Downvote


how come it's commented out?
Code: 
'Set reportlocation2 = sheets(notes).Range(output(5))
...
'reportlocation2.Copy sheets(destsh).Range(reportlocation)

Skip,

[glasses] [red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [tongue]
 
Upvote 0 Downvote
Was testing the code with comments out. I just did not take the ' off. The code will copy over comments, but it starts at the first line and goes down to the next comment. It doesn't look for the comment that matches the part number (myrange/rsearch).
 
Upvote 0 Downvote


What do you see when you step thru your code?

Skip,

[glasses] [red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [tongue]
 
Upvote 0 Downvote
It goes through the code with out a problem, it makes sure that rsearch = myrange and copies over the first comment. It shows no problem, except that it is reading the wrong comment. The part number(rsearch) is in A14, but copies over comment (reportlocation2) in A1.


If rsearch = myrange And reportlocation2 <> "" Then

c = c + 1
y = c + 103 'Tells where to start importing comments
reportlocation = "A" + y 'Starting row of part numbers for comments
rsearch.Copy sheets(destsh).Range(reportlocation)
reportlocation = "B" + y 'comments
rsearch = Application.WorksheetFunction.vlookup(myrange, Worksheets("Notes").Range("A4:C400"), 3)
rsearch.Copy sheets(destsh).Range(reportlocation)
 
Upvote 0 Downvote
Just realizing that the vlookup is working, it is the copy part of the code that is not copying over the right vlookup.

rsearch.copy sheets(destsh).range(reportlocation) does not copy over the vlookup info

rsearch=Application.WorksheetFunction.vlookup(myrange,worksheet("Notes").Range("A4:C400"),3)

Any suggestions??????

I've tried reportlocation 2 and myrange and it does not copy over the right comments.
 
Upvote 0 Downvote


VLOOKUP returns a VALUE not a Cell reference. You need a range reference to do a copy.

To find a Cell reference, use the MATCH function to retunr a row offset from the start of the lookup range.

Skip,

[glasses] [red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [tongue]
 
Upvote 0 Downvote
OK, will give it a try and let you know.
 
Upvote 0 Downvote
Hey, I changed things around and now when I run the

vRet = Application.Match(myrange, Worksheets("Notes").Range("A4:C4000"), 0)

I put the mouse over vRet and I get an ERROR 2042. What is in or missing fromt his line that is causing it to get an error?

Do

Set myrange = sheets(destsh).Range(output(7))
Set rsearch = sheets(notes).Range(output(1))
Set reportlocation2 = sheets(notes).Range(output(5))
Set rsearch = myrange.Find(what:=sheets(destsh).Range(output(7)), after:=myrange.Cells(myrange.Cells.Count), lookat:=xlWhole)
If Not myrange Is Nothing Then

If rsearch = myrange And reportlocation2 <> "" Then

c = c + 1
y = c + 103 'Tells where to start importing comments
reportlocation = "A" + y 'Starting row of part numbers for comments
rsearch.Copy sheets(destsh).Range(reportlocation)


Dim vRet As Variant
vRet = rsearch
if Not IsError(vRet) Then
-------> vRet = Application.Match(myrange, Worksheets("Notes").Range("A4:C4000"), 0)
reportlocation = "B" + y 'comments
vRet.Copy sheets(destsh).Range(reportlocation)
Set rsearch = myrange.FindNext(rsearch)
End If
End If
End If
r = r + 1
output(7) = "CA" + r
output(5) = "C" + r
Loop Until myrange =
 
Upvote 0 Downvote


Code: 
vRet = Application.Match(myrange, Worksheets("Notes").Range("A4:C4000"), 0)
myrange needs to be the VALUE that you are looking up. If that VALUE is in a cell that has myrange as a REFERENCE, then
Code: 
vRet = Application.Match(Worksheets(???).Range(myrange), Worksheets("Notes").Range("A4:C4000"), 0)


Skip,

[glasses] [red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [tongue]
 
Upvote 0 Downvote
I tried it Skip and now I get Application-defined or object-defined error. I've defined everything so I don't understand why am I getting this error. Below is the whole code. vRet = N8078345 myrange=N8078345



Sub update_headers()

'====================================================================
' UPDATE HEADER SHEETS
'
'Description: Automatically updates header sheets with header you request
'
'Macro Created on 3/16/2006
'
'Written by Wendy Smith
'=========================================================================

Dim cellstart As Integer 'starting point of informaiton (row)
Dim commentstart As Integer 'starting point for comments (row)
Dim p As Integer
Dim c As Integer 'for comments
Dim h As Integer 'for comments
Dim x As Integer
Dim r As String 'counting variable for comments
Dim n As String 'counting variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim TestRange4 As String
Dim TestRange5 As String
Dim TestRange6 As String 'Test String for notes
Dim TestRange7 As String 'Test String for notes
Dim TestRange8 As String 'Test String for notes
Dim TestRange9 As String 'test string for notes
Dim m As String
Dim y As String 'for comments
Dim sh As String 'source sheet
Dim destsh As String 'dest sheet
Dim notes As String 'source sheet for comments
Dim reportlocation As String
Dim reportlocation2 As Range
Dim header As String 'header you want to run Holds header
Dim output(1 To 8) As String
Dim myrange As Range
Dim rsearch As Range


cellstart = 3 'Starting point of source sheet
commentsstart = 4 'starting point for comments increase this number only if you need more comment space
p = 0
c = 0
header = InputBox("Which header do you want to run?", "header") 'input the requested header

TestRange = header
sh = "Daily" 'Work sheet where the header information is kept
destsh = header ' desination sheet for header
notes = "Notes" 'work sheet for notes

'Clear out old Data and unhide rows

sheets(destsh).Range("A4:A100").Select
Selection.ClearContents
Selection.EntireRow.Hidden = False

n = cellstart
r = commentsstart

TestRange2 = "B" + n 'column where to match header request
TestRange3 = "A" + n 'part number
TestRange4 = "AF" + n 'over 60 days
TestRange5 = "AE" + n 'furture 6
For x = 1 To 1000 'increase this number only if daily sheet has more than 1000 parts
If UCase(Trim(TestRange)) = UCase(Trim(sheets(sh).Range(TestRange2))) And UCase(Trim(sheets(sh).Range(TestRange4) < 61)) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
reportlocation = "CA" + m
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n
ElseIf UCase(Trim(TestRange)) = UCase(Trim(sheets(sh).Range(TestRange2))) And UCase(Trim(sheets(sh).Range(TestRange4) > 60)) And UCase(Trim(sheets(sh).Range(TestRange5) > 0)) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
reportlocation = "CA" + m
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n
Else
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n

End If
Next

'Hide empty rows
m = m + 1
reportlocation = "A" + m
Do While sheets(destsh).Range(reportlocation).Value = ""
sheets(destsh).Range(reportlocation).EntireRow.Hidden = True
m = m + 1
reportlocation = "A" + m
Loop

'Move Comments
sheets(destsh).Range("A104:B204").Select
Selection.ClearContents

output(7) = "CA" + r 'dest sheet part numbers
output(8) = sheets(destsh).Range(output(7)).Text
TestRange9 = output(8)
output(1) = "A" + r 'part numbers
output(2) = sheets(notes).Range(output(1)).Value
TestRange8 = output(2)
output(5) = "C" + r 'comments
output(6) = sheets(notes).Range(output(5)).Text
TestRange7 = output(6)


Do

Set myrange = sheets(destsh).Range(output(7))
Set rsearch = sheets(notes).Range(output(1))
Set reportlocation2 = sheets(notes).Range(output(5))
Set rsearch = myrange.Find(what:=sheets(destsh).Range(output(7)), after:=myrange.Cells(myrange.Cells.Count), lookat:=xlWhole)

If Not myrange Is Nothing Then

If rsearch = myrange Then

c = c + 1
y = c + 103 'Tells where to start importing comments
reportlocation = "A" + y 'Starting row of part numbers for comments
rsearch.Copy sheets(destsh).Range(reportlocation)
Dim vRet As Variant
vRet = rsearch
-------> vRet = Application.Match(Worksheets(destsh).Range(myrange), Worksheets("Notes").Range("A4:C4000"), 0)
If Not IsError(vRet) Then
reportlocation = "B" + y 'comments
vRet.Copy sheets(destsh).Range(reportlocation)
Set rsearch = myrange.FindNext(rsearch)
End If
End If
End If
r = r + 1
output(7) = "CA" + r
output(5) = "C" + r
Loop Until myrange = ""

End Sub

 
Upvote 0 Downvote


I don't understand!!!
Code: 
                   Dim vRet As Variant
                   [red][b]vRet = rsearch[/b][/red]
------->          vRet = Application.Match(Worksheets(destsh).Range(myrange), Worksheets("Notes").Range("A4:C4000"), 0)
what is the purpose for the second statement? It is pointless since you are assigning vRet in the third statement. It seem that you do not understand what you are doing.

if myrange contains N8078345, this is a VALUE and NOT a range reference. so why do you have it coded as a range reference?

Skip,

[glasses] [red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [tongue]
 
Upvote 0 Downvote
myrange is reference to column CA and is to all values in column CA.

output(7) = "CA" + r 'dest sheet part numbers
Set myrange = sheets(destsh).Range(output(7))
r in just an increment for the rows. The myrange varies depending on the number of parts. And know I don't know what I'm doing with VB, I'm self taught and learning everyday.
 
Upvote 0 Downvote


I'm still confused, since in your prior post you stated...

"Below is the whole code. vRet = N8078345 myrange=N8078345"

Maybe you meant to say that myrange.value = N8078345

So myrange is a Range Object. Therefore,
Code: 
                   Dim vRet As Variant
------->          vRet = Application.Match(myrange.value, Worksheets("Notes").Range("A4:C4000"), 0)

Skip,

[glasses] [red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [tongue]
 
Upvote 0 Downvote
I got it to work. It moves the comments that match the part number.

thanks for all your help.

Wendy
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
1K
morechocolate
morechocolate
K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
712
combo
combo
huiettcm
  • Locked
  • Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
497
strongm
strongm
rf92d
  • Locked
  • Question
Hi referring to thread707-107529
Replies
0
Views
524
rf92d
rf92d
rf92d
  • Locked
  • Question
hi referring to thread707-103495
Replies
9
Views
693
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top