Require some codes for variable in formula

[Seeking help]

My code cant seems to work,could you help me modified it,thanks
thousand times!

Sub formula()

'Count how many rows are there in a sheet
Dim GRC2 As Integer
Sheets("Supplier list").Select
GRC2 = Range("A65536").End(xlUp).Row

'Putting in of formula
Range("K4").Select
ActiveCell.formula = "=VLOOKUP(A4,'input status'!$A$
:$Q$GRC2,2,FALSE)"
Selection.AutoFill Destination:=Range("Q4:Q" & GRC2)
Type:=xlFillDefault
End Sub

With gratitude,
Seeking hel

 

[duane]

try this

ActiveCell.formula = "=VLOOKUP(A4,'input status'!$A$1
:indirect(""$Q$""&GRC2),2,FALSE)"

note I assumed cell a1 for top left of range of vlookup

 

[Seeking help]

Thanks for those coding though it didn't really work.If i just input the
rows den it will work.But problem is i got to create an automate one
which can count the rows and execute the action.

 

[duane]

first, let me ay I am hardly the best at this sort of thing...maybe
another approach would be to create a named range within the macro and
then use that name in the vlookup instead of the cell references.

 

[duane]

something like the following should work

GRC2 = Range("A65536").End(xlUp).Row
range(cells(1,1),cells(17,GRC2)).current region.name = "thisrange"

then refer to this range name in your vlookup

ActiveCell.formula = "=VLOOKUP(A4,thisrange,2,FALSE)

 

[duane]

I messed it up a little


GRC2 = Range("A65536").End(xlUp).Row
range(cells(1,1),cells(GRC2,17)).name = "thisrange"

then refer to this range name in your vlookup

ActiveCell.formula = "=VLOOKUP(A4,thisrange,2,FALSE)"

 

[Seeking help]

Thanks for your help,but it will display another result instead of the
one i want.How do you change the thing to make it show information that
is in sheet1 but the formula input is being placed in sheet2.They are
able to extract because of a common column in each sheet which is
column A.
Sorry to troubled that much but i really have no idea of what coding
are,kind of a coding idiot..lolz..

 

[duane]

so the row count comes from sheet supplier list, and the formula also
goes in this same sheet, but the lookup takes place on sheet input
status?

Sub formula()

'Count how many rows are there in a sheet
Dim GRC2 As Integer
Sheets("Supplier list").Select
GRC2 = Range("A65536").End(xlUp).Row
sheets("input status").range(cells(1,1),cells(GRC2,17)).name =
"lookuprange"
sheets("supplier list").range(cells(1,1),cells(17,GRC2)).current
region.name = "fillrange"
Range("K4").Select
ActiveCell.formula = "=VLOOKUP(A4,lookuprange,2,FALSE)"
Selection.AutoFill Destination:=Range("fillrange"),
Type:=xlFillDefault
End Sub

 

[duane]

I got this to work - some more tweeks

Sub formula()
'Count how many rows are there in a sheet
Dim GRC2 As Integer
GRC2 = Sheets("Supplier list").Range("A65536").End(xlUp).Row
Cells(1, 6) = GRC2
Sheets("input status").Select
Range(Cells(1, 1), Cells(GRC2, 17)).Name = "lookuprange"
Sheets("Supplier list").Select
Range(Cells(4, 11), Cells(GRC2, 11)).Name = "fillrange"
Range("K4").Select
ActiveCell.formula = "=VLOOKUP(A4,lookuprange,2,FALSE)"
Select

 

[duane]

sorry - previous copy did not include entire macro

Sub formula()
'Count how many rows are there in a sheet
Dim GRC2 As Integer
GRC2 = Sheets("sheet1").Range("A65536").End(xlUp).Row
Cells(1, 6) = GRC2
Sheets("sheet2").Select
Range(Cells(1, 1), Cells(GRC2, 17)).Name = "lookuprange"
Sheets("sheet1").Select
Range(Cells(4, 11), Cells(GRC2, 11)).Name = "fillrange"
Range("K4").Select
ActiveCell.formula = "=VLOOKUP(A4,lookuprange,2,FALSE)"
Selection.AutoFill Destination:=Range("fillrange")
Type:=xlFillDefault
End Su