hi,

I have a macro coded as below:-

Sub LookupValues()

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'C:\Documents and Settings\Jac\Desktop\Book2.xls'
_myRange,2,0)"
Selection.AutoFill Destination:=Range("B1:B3")

End Sub

But this macro can only fill up the specific range with values which is
Range("B1:B3") but I wish that I could have the way that the macro can help
to fill up values for range with unknown length, which mean sometimes the
range could be longer or sometimes the length could be shorter.

I have tried out some methods but they seem like not working out as
expected. So someone please help to advise....

Thanking in advance.

Can you pick out a column that would tell you how far to fill?

Dim LastRow as long
'I'm using column C
lastrow = cells(rows.count,"C").end(xlup).row

.....

selection.autofill destination:=range("B1:B" & lastrow)



Jac wrote:

hi,

I have a macro coded as below:-

Sub LookupValues()

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'C:\Documents and Settings\Jac\Desktop\Book2.xls'
_myRange,2,0)"
Selection.AutoFill Destination:=Range("B1:B3")

End Sub

But this macro can only fill up the specific range with values which is
Range("B1:B3") but I wish that I could have the way that the macro can help
to fill up values for range with unknown length, which mean sometimes the
range could be longer or sometimes the length could be shorter.

I have tried out some methods but they seem like not working out as
expected. So someone please help to advise....

Thanking in advance.

--

Dave Peterson

hi,

first of all thanks for the help!

something to clarify is that the statement below:-
selection.autofill destination:=range("B1:B" & lastrow)
Is it mean that values will be filled up in range B1:B3 & in range of column
C?



"Dave Peterson" wrote:

Can you pick out a column that would tell you how far to fill?

Dim LastRow as long
'I'm using column C
lastrow = cells(rows.count,"C").end(xlup).row

.....

selection.autofill destination:=range("B1:B" & lastrow)



Jac wrote:

hi,

I have a macro coded as below:-

Sub LookupValues()

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'C:\Documents and Settings\Jac\Desktop\Book2.xls'
_myRange,2,0)"
Selection.AutoFill Destination:=Range("B1:B3")

End Sub

But this macro can only fill up the specific range with values which is
Range("B1:B3") but I wish that I could have the way that the macro can help
to fill up values for range with unknown length, which mean sometimes the
range could be longer or sometimes the length could be shorter.

I have tried out some methods but they seem like not working out as
expected. So someone please help to advise....

Thanking in advance.

--

Dave Peterson

hi, Dave

I think I get what the code means!
Thanks :)


"Jac" wrote:

hi,

I have a macro coded as below:-

Sub LookupValues()

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'C:\Documents and Settings\Jac\Desktop\Book2.xls'
_myRange,2,0)"
Selection.AutoFill Destination:=Range("B1:B3")

End Sub

But this macro can only fill up the specific range with values which is
Range("B1:B3") but I wish that I could have the way that the macro can help
to fill up values for range with unknown length, which mean sometimes the
range could be longer or sometimes the length could be shorter.

I have tried out some methods but they seem like not working out as
expected. So someone please help to advise....

Thanking in advance.

Just in case...

It uses column C to determine the last row to fill. But it actually fills the
cells in column B.


Jac wrote:

hi,

first of all thanks for the help!

something to clarify is that the statement below:-
selection.autofill destination:=range("B1:B" & lastrow)
Is it mean that values will be filled up in range B1:B3 & in range of column
C?

"Dave Peterson" wrote:

Can you pick out a column that would tell you how far to fill?

Dim LastRow as long
'I'm using column C
lastrow = cells(rows.count,"C").end(xlup).row

.....

selection.autofill destination:=range("B1:B" & lastrow)



Jac wrote:

hi,

I have a macro coded as below:-

Sub LookupValues()

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'C:\Documents and Settings\Jac\Desktop\Book2.xls'
_myRange,2,0)"
Selection.AutoFill Destination:=Range("B1:B3")

End Sub

But this macro can only fill up the specific range with values which is
Range("B1:B3") but I wish that I could have the way that the macro can help
to fill up values for range with unknown length, which mean sometimes the
range could be longer or sometimes the length could be shorter.

I have tried out some methods but they seem like not working out as
expected. So someone please help to advise....

Thanking in advance.

--

Dave Peterson


--

Dave Peterson

hi, another thing regarding this macro module is that if the files to look up
for the values are different, how would I have to assign each file to the
macro?

I have tried out using input box to replace the file name(s) but the
directory will still remain, so is there any other way to do it? Please
advise........

Thanking in advance.


"Jac" wrote:

hi,

I have a macro coded as below:-

Sub LookupValues()

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'C:\Documents and Settings\Jac\Desktop\Book2.xls'
_myRange,2,0)"
Selection.AutoFill Destination:=Range("B1:B3")

End Sub

But this macro can only fill up the specific range with values which is
Range("B1:B3") but I wish that I could have the way that the macro can help
to fill up values for range with unknown length, which mean sometimes the
range could be longer or sometimes the length could be shorter.

I have tried out some methods but they seem like not working out as
expected. So someone please help to advise....

Thanking in advance.