Seting lookup to a variable

M

mpjohnston

Greetings all, I have a lookup to another sheet as follows:

=LOOKUP(2,1/(qperiodresponseabandcall!A1:A994="Breeze/Ma
Reservations"),qperiodresponseabandcall!D1:D994)

I would like to set a varialbe to that lookup only if it is not "0"
The problem I have is I am not sure what type the variable should b
and perhaps the information it is pulling from look up is a text not
number. I was thinking the EVALUATE function but it doesnt seem to wor
in VBA.

xVarialble = EVALUATE(LOOKUP(2,1/(qperiodresponseabandcall!R[-" & Coun
- 1 & "]C[-" & i & "]:R[" & RCount & "]C[-" & i & "]=""" & txtSearch(h
& """),qperiodresponseabandcall!R[-" & Count - 1 & "]C[2]:R[" & RCoun
& "]C[2]))

It tells me Compile error: "( expected" and highlights "[-" & Count -
& "]"

Count is defined as
Count = "37"

Thanks for any input
Mik
 
D

Dave Peterson

Why not just mimic the worksheet formula that worked in a cell?

Option Explicit
Sub testme()

Dim xVariable As Variant
xVariable _
= Evaluate("LOOKUP(2,1/(qperiodresponseabandcall!A1:A994=" & _
"""Breeze/Max Reservations""),qperiodresponseabandcall!D1:D994)")

If IsError(xVariable) Then
'do what you want for an error
ElseIf xVariable = 0 Then
'do what you want for 0
Else
'do whatever
End If

End Sub

mpjohnston < said:
Greetings all, I have a lookup to another sheet as follows:

=LOOKUP(2,1/(qperiodresponseabandcall!A1:A994="Breeze/Max
Reservations"),qperiodresponseabandcall!D1:D994)

I would like to set a varialbe to that lookup only if it is not "0".
The problem I have is I am not sure what type the variable should be
and perhaps the information it is pulling from look up is a text not a
number. I was thinking the EVALUATE function but it doesnt seem to work
in VBA.

xVarialble = EVALUATE(LOOKUP(2,1/(qperiodresponseabandcall!R[-" & Count
- 1 & "]C[-" & i & "]:R[" & RCount & "]C[-" & i & "]=""" & txtSearch(h)
& """),qperiodresponseabandcall!R[-" & Count - 1 & "]C[2]:R[" & RCount
& "]C[2]))

It tells me Compile error: "( expected" and highlights "[-" & Count - 1
& "]"

Count is defined as
Count = "37"

Thanks for any input
Mike
Click to expand...
 
M

mpjohnston

If I try hard coding the ranges I receive this error:

Compile error "Expected: )" and it highlights the semicolon.

I go back to my theory that you cannot evaluate a Lookup

Either way what ever information that is being pulled from the othe
sheet is not recognized as being a 0 (number) or the 0 (letter) in th
if statemen
 
M

mpjohnston

Is there some statement to add before this:

xVariable _
= Evaluate("LOOKUP(2,1/(qperiodresponseabandcall!A1:A994=" & _
"""Breeze/Max Reservations""),qperiodresponseabandcall!D1:D994)")

to ensure that it will be a number to be added to another number
 
D

Dave Peterson

I'm not sure why you didn't get what you expected with my original guess.

Maybe you just needed to convert a string value to numeric:

xVariable = clng(xVariable)
or
xvariable = cDbl(xvariable)
 
Top