Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

Thanks JMB and Ossie<

I am trying to insert JMB formula in Ossie macro (I want a numeric 2 and a
relative row reference for the vlookup ). But the formula is placing #2 in
column M from 8 to 27 (Sheet2), instead of doing the vlookup. Any reason why?
Where should I call Sheet2.

Regards


Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M8:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult =
"IF(ISNA(VLOOKUP($A9,Sheet1!$A:$L,2,FALSE)),2,VLOO KUP($A9,Sheet1!$A:$L,2,FALSE))"
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub

"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
..Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

Hi again Faboboren,

If you want to insert the formula rather than the way I did it then you must
use relative addressing in the foumula. It will be like this:-

Note: The part of the formula between the double quotes is one line.

Sub Macro2()
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M8:M27")
End With

For Each c In rngResults

c.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(RC[-10],Sheet1!C1:C12,2,FALSE)),2,VLOOKUP(RC[-10],Sheet1!C1:C12,2,FALSE))"

Next c

End Sub



To get the correct syntax for for the forumla to insert in the macro, the
simplest way is:-

Insert the formula in the required cell in the interactive mode and get it
working.
Turn on the macro recorder.
Select the cell with the formula.
Make a dummy change to the formula like delete the last character and then
re-enter the character and then press Enter. (No change to the formula)
Turn off the recorder.
The recorded macro will show it for ActiveCell.FormulaR1C1 = .......
Replace ActiveCell with the required range which in this case is the range
variable c

--
Regards,

OssieMac


"Faboboren" wrote:

Thanks JMB and Ossie<

I am trying to insert JMB formula in Ossie macro (I want a numeric 2 and a
relative row reference for the vlookup ). But the formula is placing #2 in
column M from 8 to 27 (Sheet2), instead of doing the vlookup. Any reason why?
Where should I call Sheet2.

Regards


Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M8:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult =
"IF(ISNA(VLOOKUP($A9,Sheet1!$A:$L,2,FALSE)),2,VLOO KUP($A9,Sheet1!$A:$L,2,FALSE))"
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub

"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

you're welcome. thanks for letting us know that you got it working.

"Faboboren" wrote:

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

JMB,

I am trying to run the code we were talking about from Sheet13 to 15. Please
any idea how.

Thanks

Dim rrLoli As range
Dim sh As Worksheet
Dim wb As Workbook


Sub Vlookupsheets()
For Each wb In Workbooks
For i = Sheets("Sheet13") To ("Sheet15")
Set rrLoli = range("M:M")
Call Vlookupsheets1
Next i
Next wb
End Sub

Sub Vlookupsheets1()
Dim r As range

For Each r In rrLoli
If IsEmpty(Cells(Rows.Count, 1)) Then
Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 14))
End If
Set rngLookupValue = Cells(7, 1)
Next

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With

End Sub


"JMB" wrote:

you're welcome. thanks for letting us know that you got it working.

"Faboboren" wrote:

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

My apologies for delayed response. Try:


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long

For i = 13 To 15
With Sheets("sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i

End Sub

"Faboboren" wrote:

JMB,

I am trying to run the code we were talking about from Sheet13 to 15. Please
any idea how.

Thanks

Dim rrLoli As range
Dim sh As Worksheet
Dim wb As Workbook


Sub Vlookupsheets()
For Each wb In Workbooks
For i = Sheets("Sheet13") To ("Sheet15")
Set rrLoli = range("M:M")
Call Vlookupsheets1
Next i
Next wb
End Sub

Sub Vlookupsheets1()
Dim r As range

For Each r In rrLoli
If IsEmpty(Cells(Rows.Count, 1)) Then
Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 14))
End If
Set rngLookupValue = Cells(7, 1)
Next

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With

End Sub


"JMB" wrote:

you're welcome. thanks for letting us know that you got it working.

"Faboboren" wrote:

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

JMB,

This is great!!! working perfect!! double thanks again
I am so happy

Best Regards

"JMB" wrote:

My apologies for delayed response. Try:


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long

For i = 13 To 15
With Sheets("sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i

End Sub

"Faboboren" wrote:

JMB,

I am trying to run the code we were talking about from Sheet13 to 15. Please
any idea how.

Thanks

Dim rrLoli As range
Dim sh As Worksheet
Dim wb As Workbook


Sub Vlookupsheets()
For Each wb In Workbooks
For i = Sheets("Sheet13") To ("Sheet15")
Set rrLoli = range("M:M")
Call Vlookupsheets1
Next i
Next wb
End Sub

Sub Vlookupsheets1()
Dim r As range

For Each r In rrLoli
If IsEmpty(Cells(Rows.Count, 1)) Then
Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 14))
End If
Set rngLookupValue = Cells(7, 1)
Next

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With

End Sub


"JMB" wrote:

you're welcome. thanks for letting us know that you got it working.

"Faboboren" wrote:

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

Hi JMB,

As I said I am trying to do the same in 11 workbooks, I added to your code
"For Each wb In Workbooks", and not working, any idea why?

Thanks

Sub Vlookupsheets()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long
Dim wb As Workbook

For Each wb In Workbooks
For i = 13 To 15
With Sheets("WSP_Sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
..Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
..Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With


With rngResults
..Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
..Value = .Value
End With
Next i
Next wb

End Sub

"JMB" wrote:

My apologies for delayed response. Try:


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long

For i = 13 To 15
With Sheets("sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i

End Sub

"Faboboren" wrote:

JMB,

I am trying to run the code we were talking about from Sheet13 to 15. Please
any idea how.

Thanks

Dim rrLoli As range
Dim sh As Worksheet
Dim wb As Workbook


Sub Vlookupsheets()
For Each wb In Workbooks
For i = Sheets("Sheet13") To ("Sheet15")
Set rrLoli = range("M:M")
Call Vlookupsheets1
Next i
Next wb
End Sub

Sub Vlookupsheets1()
Dim r As range

For Each r In rrLoli
If IsEmpty(Cells(Rows.Count, 1)) Then
Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 14))
End If
Set rngLookupValue = Cells(7, 1)
Next

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With

End Sub


"JMB" wrote:

you're welcome. thanks for letting us know that you got it working.

"Faboboren" wrote:

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))

When using a For Each loop, you must reference the variable placeholder
within the loop. If not fully qualified (eg.
Workbook.Sheets("SomeSheet").Range("A1")), object references are assumed to
refer to the active workbook, worksheet, range, etc.

For example
Range("a1") refers the the active worksheet of the active workbook
Sheets("sheet1").Range("A1") refers to the active workbook

So assuming that all 11 workbooks are opened (12 including planos.xls), I
believe you only need the minor changes:
For Each wb In Workbooks
If wb.Name < "Planos.xls" Then
For i = 13 To 15
With wb.Sheets("WSP_Sheet" & i)
....
End With
Next i
End If
Next wb


However, if you have hidden workbooks opened (such as personal.xls) they
will be included. If you intend to have the code run on all open workbooks,
be sure to close hidden books you don't want the code to run on. Or, check
the visible property:

For Each wb In Workbooks
If Windows(wb.Name).Visible And wb.Name < "Planos.xls" Then
For i = 13 To 15
With wb.Sheets("WSP_Sheet" & i)
.....
End With
Next i
End If
Next wb


If all of the target workbooks are in one folder, you could have the macro
open them up, put the formula in, save, close, etc. The Planos.xls file will
need to be open (if not, the Vlookup formula will need edited to include the
file path - I'm pretty sure vlookup can pull info from a closed file).

Option Explicit

Sub test()
Const strPath As String = "I:\Excel\Test" '<<<CHANGE
Dim wbTemp As Workbook
Dim wsTemp As Worksheet
Dim rngResults As Range
Dim rngLookupValue As Range
Dim lngWS As Long
Dim lngWB As Long

With Application.FileSearch
.NewSearch
.LookIn = strPath
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
If .FoundFiles.Count 0 Then
For lngWB = 1 To .FoundFiles.Count
Set wbTemp = Workbooks.Open(.FoundFiles(lngWB))
For lngWS = 13 To 15
Set wsTemp = Nothing
On Error Resume Next
Set wsTemp = wbTemp.Sheets("WSP_Sheet" & lngWS)
On Error GoTo 0
If Not wsTemp Is Nothing Then
With wsTemp
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
End If
Next lngWS
wbTemp.Close savechanges:=True
Next lngWB
End If
End With
End Sub







"Faboboren" wrote:

Hi JMB,

As I said I am trying to do the same in 11 workbooks, I added to your code
"For Each wb In Workbooks", and not working, any idea why?

Thanks

Sub Vlookupsheets()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long
Dim wb As Workbook

For Each wb In Workbooks
For i = 13 To 15
With Sheets("WSP_Sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With


With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i
Next wb

End Sub

"JMB" wrote:

My apologies for delayed response. Try:


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long

For i = 13 To 15
With Sheets("sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i

End Sub

"Faboboren" wrote:

JMB,

I am trying to run the code we were talking about from Sheet13 to 15. Please
any idea how.

Thanks

Dim rrLoli As range
Dim sh As Worksheet
Dim wb As Workbook


Sub Vlookupsheets()
For Each wb In Workbooks
For i = Sheets("Sheet13") To ("Sheet15")
Set rrLoli = range("M:M")
Call Vlookupsheets1
Next i
Next wb
End Sub

Sub Vlookupsheets1()
Dim r As range

For Each r In rrLoli
If IsEmpty(Cells(Rows.Count, 1)) Then
Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 14))
End If
Set rngLookupValue = Cells(7, 1)
Next

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With

End Sub


"JMB" wrote:

you're welcome. thanks for letting us know that you got it working.

"Faboboren" wrote:

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

Hi JMB,

Thanks so much for your extensive answer. It is really great and working
perfect. I was out of town last week, I could not check the answer properly
in my blackberry.

When you referred in the third option to:

Const strPath As String = "I:\Excel\Test" '<<<CHANGE

Is this the path where 11 files are, without the names of files?

Thanks once again.


"JMB" wrote:

When using a For Each loop, you must reference the variable placeholder
within the loop. If not fully qualified (eg.
Workbook.Sheets("SomeSheet").Range("A1")), object references are assumed to
refer to the active workbook, worksheet, range, etc.

For example
Range("a1") refers the the active worksheet of the active workbook
Sheets("sheet1").Range("A1") refers to the active workbook

So assuming that all 11 workbooks are opened (12 including planos.xls), I
believe you only need the minor changes:
For Each wb In Workbooks
If wb.Name < "Planos.xls" Then
For i = 13 To 15
With wb.Sheets("WSP_Sheet" & i)
....
End With
Next i
End If
Next wb


However, if you have hidden workbooks opened (such as personal.xls) they
will be included. If you intend to have the code run on all open workbooks,
be sure to close hidden books you don't want the code to run on. Or, check
the visible property:

For Each wb In Workbooks
If Windows(wb.Name).Visible And wb.Name < "Planos.xls" Then
For i = 13 To 15
With wb.Sheets("WSP_Sheet" & i)
.....
End With
Next i
End If
Next wb


If all of the target workbooks are in one folder, you could have the macro
open them up, put the formula in, save, close, etc. The Planos.xls file will
need to be open (if not, the Vlookup formula will need edited to include the
file path - I'm pretty sure vlookup can pull info from a closed file).

Option Explicit

Sub test()
Const strPath As String = "I:\Excel\Test" '<<<CHANGE
Dim wbTemp As Workbook
Dim wsTemp As Worksheet
Dim rngResults As Range
Dim rngLookupValue As Range
Dim lngWS As Long
Dim lngWB As Long

With Application.FileSearch
.NewSearch
.LookIn = strPath
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
If .FoundFiles.Count 0 Then
For lngWB = 1 To .FoundFiles.Count
Set wbTemp = Workbooks.Open(.FoundFiles(lngWB))
For lngWS = 13 To 15
Set wsTemp = Nothing
On Error Resume Next
Set wsTemp = wbTemp.Sheets("WSP_Sheet" & lngWS)
On Error GoTo 0
If Not wsTemp Is Nothing Then
With wsTemp
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
End If
Next lngWS
wbTemp.Close savechanges:=True
Next lngWB
End If
End With
End Sub







"Faboboren" wrote:

Hi JMB,

As I said I am trying to do the same in 11 workbooks, I added to your code
"For Each wb In Workbooks", and not working, any idea why?

Thanks

Sub Vlookupsheets()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long
Dim wb As Workbook

For Each wb In Workbooks
For i = 13 To 15
With Sheets("WSP_Sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With


With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i
Next wb

End Sub

"JMB" wrote:

My apologies for delayed response. Try:


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long

For i = 13 To 15
With Sheets("sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i

End Sub

"Faboboren" wrote:

JMB,

I am trying to run the code we were talking about from Sheet13 to 15. Please
any idea how.

Thanks

Dim rrLoli As range
Dim sh As Worksheet
Dim wb As Workbook


Sub Vlookupsheets()
For Each wb In Workbooks
For i = Sheets("Sheet13") To ("Sheet15")
Set rrLoli = range("M:M")
Call Vlookupsheets1
Next i
Next wb
End Sub

Sub Vlookupsheets1()
Dim r As range

For Each r In rrLoli
If IsEmpty(Cells(Rows.Count, 1)) Then
Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 14))
End If
Set rngLookupValue = Cells(7, 1)
Next

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With

End Sub


"JMB" wrote:

you're welcome. thanks for letting us know that you got it working.

"Faboboren" wrote:

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote:

JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With

Yes.

"Faboboren" wrote:

Hi JMB,

Thanks so much for your extensive answer. It is really great and working
perfect. I was out of town last week, I could not check the answer properly
in my blackberry.

When you referred in the third option to:

Const strPath As String = "I:\Excel\Test" '<<<CHANGE

Is this the path where 11 files are, without the names of files?

Thanks once again.


"JMB" wrote:

When using a For Each loop, you must reference the variable placeholder
within the loop. If not fully qualified (eg.
Workbook.Sheets("SomeSheet").Range("A1")), object references are assumed to
refer to the active workbook, worksheet, range, etc.

For example
Range("a1") refers the the active worksheet of the active workbook
Sheets("sheet1").Range("A1") refers to the active workbook

So assuming that all 11 workbooks are opened (12 including planos.xls), I
believe you only need the minor changes:
For Each wb In Workbooks
If wb.Name < "Planos.xls" Then
For i = 13 To 15
With wb.Sheets("WSP_Sheet" & i)
....
End With
Next i
End If
Next wb


However, if you have hidden workbooks opened (such as personal.xls) they
will be included. If you intend to have the code run on all open workbooks,
be sure to close hidden books you don't want the code to run on. Or, check
the visible property:

For Each wb In Workbooks
If Windows(wb.Name).Visible And wb.Name < "Planos.xls" Then
For i = 13 To 15
With wb.Sheets("WSP_Sheet" & i)
.....
End With
Next i
End If
Next wb


If all of the target workbooks are in one folder, you could have the macro
open them up, put the formula in, save, close, etc. The Planos.xls file will
need to be open (if not, the Vlookup formula will need edited to include the
file path - I'm pretty sure vlookup can pull info from a closed file).

Option Explicit

Sub test()
Const strPath As String = "I:\Excel\Test" '<<<CHANGE
Dim wbTemp As Workbook
Dim wsTemp As Worksheet
Dim rngResults As Range
Dim rngLookupValue As Range
Dim lngWS As Long
Dim lngWB As Long

With Application.FileSearch
.NewSearch
.LookIn = strPath
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
If .FoundFiles.Count 0 Then
For lngWB = 1 To .FoundFiles.Count
Set wbTemp = Workbooks.Open(.FoundFiles(lngWB))
For lngWS = 13 To 15
Set wsTemp = Nothing
On Error Resume Next
Set wsTemp = wbTemp.Sheets("WSP_Sheet" & lngWS)
On Error GoTo 0
If Not wsTemp Is Nothing Then
With wsTemp
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
End If
Next lngWS
wbTemp.Close savechanges:=True
Next lngWB
End If
End With
End Sub







"Faboboren" wrote:

Hi JMB,

As I said I am trying to do the same in 11 workbooks, I added to your code
"For Each wb In Workbooks", and not working, any idea why?

Thanks

Sub Vlookupsheets()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long
Dim wb As Workbook

For Each wb In Workbooks
For i = 13 To 15
With Sheets("WSP_Sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With


With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i
Next wb

End Sub

"JMB" wrote:

My apologies for delayed response. Try:


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range
Dim i As Long

For i = 13 To 15
With Sheets("sheet" & i)
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = .Range(.Cells(7, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(7, 1)
End With

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With
Next i

End Sub

"Faboboren" wrote:

JMB,

I am trying to run the code we were talking about from Sheet13 to 15. Please
any idea how.

Thanks

Dim rrLoli As range
Dim sh As Worksheet
Dim wb As Workbook


Sub Vlookupsheets()
For Each wb In Workbooks
For i = Sheets("Sheet13") To ("Sheet15")
Set rrLoli = range("M:M")
Call Vlookupsheets1
Next i
Next wb
End Sub

Sub Vlookupsheets1()
Dim r As range

For Each r In rrLoli
If IsEmpty(Cells(Rows.Count, 1)) Then
Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 1).End(xlUp).Offset(0, 12))
Else: Set rngResults = range(Cells(7, 13), _
Cells(Rows.Count, 14))
End If
Set rngLookupValue = Cells(7, 1)
Next

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",'[Planos.xls]A&P plano'!A:N,14,FALSE))"
.Value = .Value
End With

End Sub


"JMB" wrote:

you're welcome. thanks for letting us know that you got it working.

"Faboboren" wrote:

Hi JMB,

This is really working so well, 100 thanks!!!


"JMB" wrote:

Not sure my response was posted, so I'll try it again...


Here is an approach using variables.


Sub test()
Dim rngResults As Range
Dim rngLookupValue As Range

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = Sheets("sheet2").Range("C2")

With rngResults
.Formula = "=IF(ISNA(VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE)),2,VLOOKUP(" & _
rngLookupValue.Address(False, False) & _
",Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

End Sub


If you want the range in column M to be from M2 to whatever the last row is
in column C replace

Set rngResults = Sheets("sheet2").Range("M2:M27")
Set rngLookupValue = rngResults.Cells(1).Offset(0, -10)

with this code

With Sheets("sheet2")
If IsEmpty(.Cells(.Rows.Count, 3)) Then
Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 3).End(xlUp).Offset(0, 10))
Else: Set rngResults = .Range(.Cells(2, 13), _
.Cells(.Rows.Count, 13))
End If
Set rngLookupValue = .Cells(2, 3)
End With


"Faboboren" wrote: