Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > If and vlookup in macro
 
 
LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #18   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
  
Posts: 2,062
Default If and vlookup in macro
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:
Reply With QuoteReply With Quote
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Threaded Mode Threaded Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro - Vlookup orquidea Excel Discussion (Misc queries) 6 May 14th 08 04:42 PM
vlookup macro MikeD1224 New Users to Excel 1 June 16th 07 04:37 AM
VBA Macro for VLOOKUP Myrna Rodriguez[_3_] Excel Programming 0 December 13th 05 09:22 PM
Please help.. VLookup Macro STEVE BELL Excel Programming 3 August 3rd 05 05:31 PM
VLOOKUP in a Macro Peter Hesselager Excel Programming 5 May 7th 05 01:28 AM


All times are GMT +1. The time now is 02:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement