Require some codes for variable in formula
 

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

--
Seeking hel
-----------------------------------------------------------------------
Seeking help's Profile: http://www.excelforum.com/member.php...fo&userid=3565
View this thread: http://www.excelforum.com/showthread.php?threadid=55631

Require some codes for variable in formula
 

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=556310

Require some codes for variable in formula
 

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.


--
Seeking help
------------------------------------------------------------------------
Seeking help's Profile: http://www.excelforum.com/member.php...o&userid=35658
View this thread: http://www.excelforum.com/showthread...hreadid=556310

Require some codes for variable in formula
 

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


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=556310

Require some codes for variable in formula
 

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)

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=55631

Require some codes for variable in formula
 

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)"


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=556310

Require some codes for variable in formula
 

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..


--
Seeking help
------------------------------------------------------------------------
Seeking help's Profile: http://www.excelforum.com/member.php...o&userid=35658
View this thread: http://www.excelforum.com/showthread...hreadid=556310

Require some codes for variable in formula
 

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


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=556310

Require some codes for variable in formula
 

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

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=55631

Require some codes for variable in formula
 

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

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=55631