Referring a dynamic range in for Application.WorksheetFunction.Vlookup
 

Hi all.

I am currently working on a code that will input the vlookup function in specified cells. The code works fine if given a static range in the arguments for vlookup. However, I am attempting to make it dynamic, as I will need to apply this macro across multiple workbooks and the sheet ranges are not constant. However, my attempts have yielded no success.

The code is as follows:

Sub LookingUp(A)
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("MatchingNo").Activate
With ActiveWorkbook.Worksheets("MatchingNo")
Dim LastColumn As Long
Dim LastRow As Long
'Dim SM35Range As Range
Dim LastRow2 As Long
Dim LastColumn2 As Long
LastRow2 = ActiveWorkbook.Worksheets("SM35").UsedRange.Rows.C ount
LastColumn2 = ActiveWorkbook.Worksheets("SM35").UsedRange.Column s..Count
LastColumn = .UsedRange.Columns.Count
LastRow = .UsedRange.Rows.Count
For i = 2 To LastRow
For j = 2 To LastColumn
.Cells(i, j).Formula = Application.WorksheetFunction.VLookup(Cells(i, 1), ActiveWorkbook.Worksheets("SM35").Range(Cells(5, 1), Cells(LastRow2, LastColumn2)), j + 1, False)
Next j
Next i
Columns.AutoFit
ActiveWorkbook.Worksheets("MatchingNo").Range(Cell s(1, 1), Cells(LastRow, LastColumn)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End With

ActiveWorkbook.Worksheets("MatchingNo").Activate
ActiveWorkbook.Worksheets("MatchingNo").Range("A1" ).Select
Application.ScreenUpdating = True
End Sub

Any input is much appreciated.

Thank you and regards.

Referring a dynamic range in for Application.WorksheetFunction.Vlookup
 

Hi Daniel,

Am Fri, 19 Jun 2015 00:22:23 -0700 (PDT) schrieb Daniel Tan:

.Cells(i, j).Formula = Application.WorksheetFunction.VLookup(Cells(i, 1), ActiveWorkbook.Worksheets("SM35").Range(Cells(5, 1), Cells(LastRow2, LastColumn2)), j + 1, False)

if you use Formula you have to put in a formula and not a value.
Have a try:

Sub LookingUp()
Dim wsh As Worksheet
Dim LastColumn As Long, LastRow As Long
Dim LastRow2 As Long, LastColumn2 As Long

Application.ScreenUpdating = False
Set wsh = ActiveWorkbook.Worksheets("MatchingNo")
With wsh
LastRow2 = Worksheets("SM35").UsedRange.Rows.Count
LastColumn2 = Worksheets("SM35").UsedRange.Columns.Count
LastColumn = .UsedRange.Columns.Count
LastRow = .UsedRange.Rows.Count
.Range(.Cells(2, 2), .Cells(LastRow, LastColumn2)).Formula = _
"=VLookup($A1,SM35!" & _
Range(Cells(5, 1), Cells(LastRow2, LastColumn2)).Address _
& ",column(), 0)"
Columns.AutoFit
With .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End With

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Referring a dynamic range in for Application.WorksheetFunction.Vlookup
 

On Friday, June 19, 2015 at 4:07:13 PM UTC+8, Claus Busch wrote:
Hi Daniel,

Am Fri, 19 Jun 2015 00:22:23 -0700 (PDT) schrieb Daniel Tan:

.Cells(i, j).Formula = Application.WorksheetFunction.VLookup(Cells(i, 1), ActiveWorkbook.Worksheets("SM35").Range(Cells(5, 1), Cells(LastRow2, LastColumn2)), j + 1, False)

if you use Formula you have to put in a formula and not a value.
Have a try:

Sub LookingUp()
Dim wsh As Worksheet
Dim LastColumn As Long, LastRow As Long
Dim LastRow2 As Long, LastColumn2 As Long

Application.ScreenUpdating = False
Set wsh = ActiveWorkbook.Worksheets("MatchingNo")
With wsh
LastRow2 = Worksheets("SM35").UsedRange.Rows.Count
LastColumn2 = Worksheets("SM35").UsedRange.Columns.Count
LastColumn = .UsedRange.Columns.Count
LastRow = .UsedRange.Rows.Count
.Range(.Cells(2, 2), .Cells(LastRow, LastColumn2)).Formula = _
"=VLookup($A1,SM35!" & _
Range(Cells(5, 1), Cells(LastRow2, LastColumn2)).Address _
& ",column(), 0)"
Columns.AutoFit
With .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End With

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus for the quick response.

I have tried it and it will work if the cells I'm referring to is static. However, I need to apply this formula to a range of cells (i.e. B2 to Z2) and their corresponding values for the v-lookup column will change accordingly which is why I set the loops up.

I hope you can understand the explanation.

Thank you and regards.

Referring a dynamic range in for Application.WorksheetFunction.Vlookup
 

Hi,

Am Fri, 19 Jun 2015 01:38:44 -0700 (PDT) schrieb Daniel Tan:

I have tried it and it will work if the cells I'm referring to is static. However, I need to apply this formula to a range of cells (i.e. B2 to Z2) and their corresponding values for the v-lookup column will change accordingly which is why I set the loops up.

I hope you can understand the explanation.

no, I don't understand it.
Please post here how the formulas in B2, C2 and B3 should look like


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Referring a dynamic range in for Application.WorksheetFunction.Vlookup
 

Try...


Sub LookingUp_A()
Dim rngTarget As Range, rngLookup As Range, sFormula$

With ActiveWorkbook.Worksheets("MatchingNo").UsedRange
Set rngTarget = .Offset(1, 1).Resize(.Rows.Count - 1, _
.Columns.Count - 1)
End With

With ActiveWorkbook.Worksheets("SM35").UsedRange
'Lookup range starts at $A5 and stops at the last row/col
Set rngLookup = .Offset(4, 1).Resize(.Rows.Count - 4, _
.Columns.Count - 1)
End With
'Formula needs to self-adjust to its row in "A",
'so make its ref col-absolute/row-relative.
sFormula = "=VLOOKUP($A2,SM35!" & rngLookup.Address _
& ",COLUMN()+1,False)"

Application.ScreenUpdating = False
On Error GoTo ErrExit
With rngTarget
.Formula = sFormula: .Columns.AutoFit
.HorizontalAlignment = xlCenter: .VerticalAlignment = xlCenter
End With

ErrExit:
Set rngTarget = Nothing: Set rngLookup = Nothing
Application.ScreenUpdating = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion