VLookup Help Please 1

[ribhead]

I may have some overkill here but I'm just trying to figure out why my VLookup Function gives me an error? Any help would be great.

rib

Sub Get_Numbers()
Dim ClockNumbers As Range
Dim x As Long, r As Long, shift As String
Dim lrow As Long, clocknumber As String
Dim numparts As Double, numscrap As Double
Sheets("Week52&quot.Activate
lrow = Rows.Cells(Rows.count, 59).End(xlUp).Row
Set ClockNumbers = Range(Cells(2, 59), Cells(lrow, 60))
lrow = Empty
r = 2
For x = 1 To Sheets.count
Sheets(x).Activate
lrow = Rows.Cells(Rows.count, 2).End(xlUp).Row
Do While r <> lrow + 1
Cells(r, 2).Activate
clocknumber = ActiveCell.Value
If IsNumeric(clocknumber) Then
shift = Application.WorksheetFunction.VLookup(clocknumber, ClockNumbers, 2, True)
r = r + 1
Else
End If
r = r + 1
Loop
Next x
End Sub


I know a little about nothing and even less about everything.

 

[SkipVought]

Hi,

1. you need to intialize r INSIDE the worksheet loop

2. what happend when you get to Week52 in your loop?
[tt]
Sub Get_Numbers()
Dim ClockNumbers As Range
Dim x As Long, r As Long, shift As String
Dim lrow As Long, clocknumber As String
Dim numparts As Double, numscrap As Double
Sheets("Week52&quot.Activate
lrow = Rows.Cells(Rows.Count, 59).End(xlUp).Row
Set ClockNumbers = Range(Cells(2, 59), Cells(lrow, 60))
lrow = Empty
For x = 1 To Sheets.Count
Sheets(x).Activate
'what happens when you get to Sheets("Week52&quot
lrow = Rows.Cells(Rows.Count, 2).End(xlUp).Row
r = 2
Do While r <> lrow + 1
clocknumber = Cells(r, 2).Value
If IsNumeric(clocknumber) Then
shift = Application.WorksheetFunction.VLookup(clocknumber, ClockNumbers, 2, True)
r = r + 1
Else
End If
r = r + 1
Loop
Next x
End Sub
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Does'nt must the 2nd argument of VLookup be a named range ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

PH,

He set a range

Set ClockNumbers = Range(Cells(2, 59), Cells(lrow, 60))

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

2 things.

1. define clocknumber as some numeric type

2. Either Application or WorksheetFunction is the Object for VLOOKUP but NOT BOTH
[tt]
Sub Get_Numbers()
Dim ClockNumbers As Range
Dim x As Long, r As Long, shift As String
Dim lrow As Long, clocknumber
Dim numparts As Double, numscrap As Double
' Sheets("Week52&quot.Activate
lrow = Rows.Cells(Rows.Count, 5).End(xlUp).Row
Set ClockNumbers = Range(Cells(2, 5), Cells(lrow, 6))
lrow = Empty
For x = 1 To Sheets.Count
Sheets(x).Activate
'what happens when you get to Sheets("Week52&quot
lrow = Rows.Cells(Rows.Count, 2).End(xlUp).Row
r = 2
Do While r <> lrow + 1
clocknumber = Cells(r, 2).Value
If IsNumeric(clocknumber) Then
shift = Application.VLookup(clocknumber, ClockNumbers, 2, True)
r = r + 1
Else
End If
r = r + 1
Loop
Next x
End Sub
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ribhead]

Hey guys, Thanks for the response. Nothing will happen when I get to sheet "Week52". This is the sheet in which I will be putting all my info like in column 60. I don't have all the code in yet but I just couldn't figure out why my Vlookup wasn't working so thanks Skip here's a star. By the way do is there a difference between the statement I have set lrow=empty and set lrow=Nothing?

just wondering

rib

I know a little about nothing and even less about everything.

 

[SkipVought]

lRow is defined as LONG as any ROW variable should be. There is no need to assign anything to lRow at that point. You assign a row number in the loop.

BTW, I changed your 59 & 60 to 5 & 6 for my testing. Better change it back.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ribhead]

Yep did it skip. The lrow ? I have I guess is a general question. When I want to set a variable equal to nothing do I just write set x = Nothing? Or can I say set x= empty?

Is there a difference?

rib.

I know a little about nothing and even less about everything.

 

[SkipVought]

Nothing is what you would set an object to.

IsEmpty() is a function that returns TRUE is the variable has not been assigned a value

Then ther's Null -- IsNull() returns TRUE is the Variant contains no valid data. A "" value is not NULL.

You ought to read up on help

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ribhead]

Thanks Skip I don't alway have the help file available so I apologize if this is something I could have just looked up when I do have Help available.


rib

I know a little about nothing and even less about everything.

 

[SkipVought]

OK -- Several clips frpm HELP

IsNull Function


Returns a Boolean value that indicates whether an expression contains no valid data (Null).

Syntax

IsNull(expression)

The required expression argument is a Variant containing a numeric expression or string expression.

Remarks

IsNull returns True if expression is Null; otherwise, IsNull returns False. If expression consists of more than one variable, Null in any constituent variable causes True to be returned for the entire expression.

The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string ("&quot, which is sometimes referred to as a null string.

Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False.

IsEmpty Function


Returns a Boolean value indicating whether a variable has been initialized.

Syntax

IsEmpty(expression)

The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.

Remarks

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

Nothing


The Nothing keyword is used to disassociate an object variable from an actual object. Use the Set statement to assign Nothing to an object variable. For example:

Set MyObject = Nothing

Several object variables can refer to the same actual object. When Nothing is assigned to an object variable, that variable no longer refers to an actual object. When several object variables refer to the same object, memory and system resources associated with the object to which the variables refer are released only after all of them have been set to Nothing, either explicitly using Set, or implicitly after the last object variable set to Nothing goes out of scope.




Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Nothing is used for object only, via the Set statement.
Assigning objectvar to Nothing releases all the system and memory resources associated with the previously referenced object when no other variable refers to it.
The Empty keyword is used to indicate an uninitialized variable value. This is not the same thing as Null. Both are used via the = assignment operator.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[IrishJim]

Skip,
Thanks for the advice above. I have been picking at this problem for a while, doing work-arounds, until I had to revisit my code and try to make the vlookup work. You said above:
"2. Either Application or WorksheetFunction is the Object for VLOOKUP but NOT BOTH"
Following this seems to have gotten me past my current problem, and into the next.

Unfortunately, it was Microsoft's Help that got me in that bind in the first place. I quote:
Using Microsoft Excel Worksheet Functions in Visual Basic
. . .
Calling a Worksheet Function from Visual Basic
In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object.
The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it’s set to range A1:C10 on Sheet1. Another variable, answer, is assigned the result of applying the Min function to myRange. Finally, the value of answer is displayed in a message box.
Sub UseFunction()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer
End Sub
----
Looking for help on "WorksheetFunction" says that Vlookup should work, just like Min.

I've learned tons from "Help", but thought I'd point out that it led me astray on this one, and I appreciate your help!
JM

 

[xlbo]

yup - most of the time help is actually quite good - but there are a few little hiccups where they're not quite on the money - this is one of them !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question