vlookup in VBA (dynamic range)

[Kayote]

Guys,

My problem is that I want my macro to insert columns inbetween some
cells and then do an iserror(vlookup (in those newly inserted columns)
to extract data from another sheet in workbook called 'Workings.xls'.

So far my code looks like this:


Dim CurMonth As String
Dim Warn As String
Dim Ans As Integer
Warn = "Please ensure that the Active Cell is where "
Warn = Warn & "you would like the columns to be inserted. Continue?"
Ans = MsgBox(Warn, vbYesNo)
If Ans = vbYes Then
Sheets(Array("UK", "FR", "DE", "IT", "ES")).Select
Sheets("UK").Activate
ActiveCell.Offset(0, -6).Columns("A:F").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 6).Range("A1").Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(4, -5).Range("A1").Select
CurMonth = InputBox("Enter the Month 'Format: WE Mo#/Wk#'")
ActiveCell = CurMonth
ActiveCell.Offset(6, 0).Range("A1").Select
End If
End Sub


What I want to do is insert iserror(vlookup formula connecting to the
'Workings.xls' 'Wk20' sheet.

Cheers

 

[Tom Ogilvy]

Hard to know the particulars with recorder style code, but try something like
this:


Dim s as String
s =
"=if(iserror(Vlookup(XXX,[Working.xls]WK20!A:B,2,False),0,Vlookup(XXX,[Working.xls]WK20!A:B,2,False))"
s = Application.Replace(s,"XXX",Activecell.Offset(0,2).Address(0,0))
ActiveCell.Resize(20,1).Formula = s

 

[Kayote]

thanks for the reply Tom.

Could you please elaborate what you are doing with this...

s = Application.Replace(s,"XXX",Activecell.Offset(0,2) .Address(0,0))
ActiveCell.Resize(20,1).Formula = s


The problem I have is that because of the newly inserted columns (wher
the vlookup is suppose to go), how can I define them as anything becaus
next time they are going to be different cells.

and one question Im confused about is:

how can I define as range as this one: A1:B5

Will it be:
Activecell.range("A1:B5") as Range1
Dim Range1 as double

Is this correct

 

[Tom Ogilvy]

I assume you want to use some cell in that for the first argument to the
Vlookup formula, so for demonstration, I use two columns to the right of the
activecell. I also made that address relative so when it was entered in each
of the rows, it would correctly refer to the cells in that column for that
row.

I made a typo/error in using Application.Replace. It should be just Replace
(if you are using xl2000 or later). Use Application.Substitute if you will
need to use in xl97 or earlier as well.

s = Replace(s,"XXX",Activecell.Offset(0,2) .Address(0,0))