Simpkify ISNA and VLOOKUP code in macro

[forest2m]

Hello All,
I have this macro code:

Cells(i, 27).FormulaR1C1 = "=ISNA(VLOOKUP(RC[-23]&""-""&RC[-19], '[Helper File.xls]Lookup'!C1:C1,1,0))"

I would like to simplify it to no longer use the R1C1 syntax. I'd like to do something similar to the following:

VariableName = Application.WorksheetFunction.VLookup(Range(Cells(i, 3)).Value & "-" & Range(Cells(i, 13)).Value, '[Helper File.xls]Lookup'!C1:C1, 1, True)

That code doesn't work, of course, and it also doesn't include the ISNA portion. I'd like to have the variable hold the TRUE or FALSE result of the ISNA.
The first part of the VLOOKUP is a concatenated value, with a hyphen between the values, such as 10-223. I need to lookup data in Column C on the Lookup sheet in the Helper File. Any ideas?

 

[venkat1926]

even though it is possible to introduce a formula containing variables in excel vbal I find it tricky to correctly use the quotes and variables.
so I rewrite the macro and use find method to get the relevan reference value and offset to get at the particular column value.
anyhow sometime back I did this macro which uses application.vlookup(instead of worksheetfunction.vlookup
see whether you can modify it to suity you . you can incorporate if na into it. if necessary please post a very small extract of your sheet and deails of what you want.

'this is another way of filling navs to desired mfs
'using variable in the lookup value in vlookup funciton. 
'instead of vba forumual you use appliation method. 
'instead of naming the nav table you uses set method

Public Sub test()
Dim navtable As Range
Dim myrange As Range
    Worksheets("sheet1").Activate
Set navtable = ActiveSheet.UsedRange
    Sheet2.Activate
Dim mfone As Range
Dim mflast As Range
     Set mfone = Range("a6")
    Set mflast = Range("a6").End(xlDown)
        Set myrange = Range(mfone, mflast)
    For Each c In myrange
    c.End(xlToRight).Offset(0, 1) = Application.VLookup(c, navtable, 3, False)
    Next
End Sub

 

[Legacy 68668]

try

Dim myVariable As Boolean
myVariable = IsError(Application.VLookup(Range(Cells(i, 3)).Value & "-" & Range(Cells(i, 13)).Value, _
Workbooks("Helper File.xls").Sheets("Lookup").Range("a:a"), 1, True))

 

[forest2m]

Re: Simplify ISNA and VLOOKUP code in macro

Thank you both for your replies. I was able to use Seiya's code, but had to modify it as follows:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

myVariable = IsError(Application.VLookup(Cells(i, 3).Value & "-" & Cells(i, 13).Value, _

Workbooks("Helper File.xls").Sheets("Lookup").Range("A:A"), 1, False))

<o></o>
The first argument of the VLOOKUP had to be modified (I'm not sure why, but the Range part didn't work), and the last argument had to be changed to False so I would only get exact matches.<o></o>
<o></o>
I have 2 more questions:<o></o>
How do I return the value of the VLOOKUP itself, not the error portion? I tried simply removing the IsError() and changing the variable to String, instead of Boolean, but I got an error message.<o></o>
<o></o>
Does anyone know of a good resource that lists the different syntax for Cells, Range, Row, Column, etc? I know these can be used with numbers and variables, like Cells(i, 3).Value, or with quotes, like Range("A:A"), or with the R1C1 syntax. I'd like to see the rules on how to use all of those variations. I'd also like a list of the objects that can be used with these methods, such as .Value, .EntireColumn.Delete, etc.<o></o>
<o></o>
Thanks again!<o></o>

 

[forest2m]

Re: Simplify ISNA and VLOOKUP code in macro

Sorry the code broke into 2 parts in my last post. It should be:

myVariable = IsError(Application.VLookup(Cells(i, 3).Value & "-" & Cells(i, 13).Value, Workbooks("Helper File.xls").Sheets("Lookup").Range("A:A"), 1, False))

 

[Legacy 68668]

Re: Simplify ISNA and VLOOKUP code in macro

1) Declare myVariable as Variant, since it may return any type of value.
2) You can get all the method/properties though automatically when you write object like

Sheets("sheet1").
Range("a1").
Cells(1).

You should see the list when you type a period after the object.

Or browse the object browser.
In VBE
[View] - [Object Browser]