If and vlookup in macro

F

Faboboren

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",VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
 
F

Faboboren

I want to add some details:

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

JMB

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,VLOOKUP($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.
 
O

OssieMac

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
 
F

Faboboren

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,VLOOKUP($A9,Sheet1!$A:$L,2,FALSE))"
On Error GoTo 0

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

Next c

End Sub
 
J

JMB

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,VLOOKUP(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

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

Faboboren

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,VLOOKUP(C2,Sheet1!:C,2,FALSE))"
..Value = .Value
End With
 
O

OssieMac

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
 
J

JMB

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
 
F

Faboboren

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
 
J

JMB

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 said:
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 said:
you're welcome. thanks for letting us know that you got it working.
Click to expand...
Click to expand...
 
F

Faboboren

JMB,

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

Best Regards

JMB said:
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 said:
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 said:
you're welcome. thanks for letting us know that you got it working.

:

Hi JMB,

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


:

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


:

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,VLOOKUP(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




:

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,VLOOKUP(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

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




:

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



:

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,VLOOKUP($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.

:

I want to add some details:

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

:

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",VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Click to expand...
Click to expand...
Click to expand...
 
F

Faboboren

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 said:
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 said:
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 said:
you're welcome. thanks for letting us know that you got it working.

:

Hi JMB,

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


:

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


:

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,VLOOKUP(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




:

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,VLOOKUP(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

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




:

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



:

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,VLOOKUP($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.

:

I want to add some details:

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

:

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",VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Click to expand...
Click to expand...
Click to expand...
 
J

JMB

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 said:
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 said:
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 said:
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


:

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

:

Hi JMB,

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


:

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


:

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,VLOOKUP(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With




:

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,VLOOKUP(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

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




:

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



:

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,VLOOKUP($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.

:

I want to add some details:

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

:

Hi,
Click to expand...
Click to expand...
Click to expand...
 
F

Faboboren

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 said:
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 said:
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 said:
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

:

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


:

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

:

Hi JMB,

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


:

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


:

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,VLOOKUP(C2,Sheet1!:C,2,FALSE))"
.Value = .Value
End With
Click to expand...
Click to expand...
Click to expand...
 
J

JMB

Yes.

Faboboren said:
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 said:
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 said:
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

:

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

:

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


:

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

:

Hi JMB,

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


:

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:
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

using vlookup in vba 10
Vlook up to combine data in many sheets 1
Defining a dynamic table_array for vlookup 9
Need help to fix vlookup function in macro 1
adding vlookup into Macro and table_array is different every week. 1
Can I add a formula or function to Vlookup? 1
sorting: cleared/null v/s "" 3
Macro autofills formula when a cell value is changed 4

Top