Find last row and select cells

[imported_unknown]

Problem:
My worksheet has an undefined number of rows (different for each time).
I want to find the last row with data in it, and then put a formula (vlookup something...) into coloumn A, and stretch/copy this to all cells in Auntil I reach the last A-cell containing data.

Anyone who feel the call to help me out here??

 

[flexinau]

In your vlookup formula, you are able to use a range name for Table_array once you have defined the name of the table in Insert|Name |Define.

A limitation to name ranges is the inability to use the same range name more than once in different workbooks on the same Excel file.

 

[imported_unknown]

I need to perform the same task myself, but how might you do this using VBA. I also need to allow the code insert a new column where the formula will be inserted.

 

[bolo]

first you need to name the range that contains the data you are looking up. for example if cols a and b contain the following:

adam 23
bob 45
chris 24
david 34

go to insert,name then define. In the formula bit put:
=offset('Sheet1'!$A$1,0,0,counta('Sheet1'!$A:$A),2) name the thing info or something.

what this does is create a dynamic range that increases or decreases as you enter more or less data.

Suppose your inputs are in column D
You can use the following macro to see how many inputs you have and copy the vlookup formula down in column e.

sub test()
dim i as integer
i= range("D1").end(xldown).row
range("e1").value = "=vlookup(D1,info,2,false)"
range("e1").copy
range("e1:e" & i).select
activesheet.paste

end sub

P.S. this only works if there are no gaps in the inputs.
Hope that all makes sense and does what you want.Let us know how you get on.
This message was edited by bolo on 2002-10-05 08:12

 

[imported_unknown]

THat one didn't work for me... I'll have to go to work and pick up my VBA macro there, and I'll come back to this forum with more details about my problem.
Thanks so far - seems that people are very helpfull and has great skills at this forum.

VikingS6

 

[imported_unknown]

I'm really not following you guys (and/or girls) here
Lets take it from scratch:
In my MAIN.xls I have column A with i.e. these numbers:

11111
22222
3333
444
555555
66666

Then I have another worksheet in another Excel-document called DATALIST.XLS.
In column A and B is the following info:

943827 Dan
5453 Eric
345 John
65456 Melissa
11111 David
43234 Ron
4343 Carrie
3 Max
34545 Jamie
725487 Bob

Then in MAIN.XLS I'd like a formula that searches the whole column A from my DATALIST.XLS, starting with cell A1 in MAIN.XLS. Then onto cell A2 and so on....
When it finds a identical number, I want the formula to put the info from column B (in DATALIST.XLS) into column C in MAIN.xls.
So in my example I would have the name David to appear in cell C1 in MAIN.XLS.

Actually I've done all this using a macro in VBA.
I could of course put the formula into the cells A1 to A10000 in MAIN.XLS, but the data in MAIN.XLS is different from each time I do this task with this macro of mine. Because I import data from a text file to my MAIN.XLS, and the number of rows in the text file are never the same. Sometimes it could be 163 rows, the next time it could be 6932 rows. See ??

So I only need the VBA code for telling my macro that: After putting in the formula in C1 in MAIN.XLS, I want the same formula to be put into cell C2, C3, C4 and so on.
The formula should of course refer to the data in cell A at the same row as the formula is.

Did that make any more sense or what??

My other problem is that I have the Norwegian version of Excel at work, and are not that familiar with the english trems and words in VBA and Excel.
At home I have english version of Excel, so I just have to put my language skills up for a test now

Appreciate any help on this.

 

[bolo]

OK in your datalist.xls goto insert then tools then define. In the formula box put

=offset('Sheet1'!$a:$a,0,0,counta('Sheet1'!$B:$B),2)
in the name bit write info and click add
(this is assuming the data is in the sheet called Sheet1)
Now in your main.xls write the code into a module:

Private sub obtain_info()
dim i as integer
i= range("a1").end(xldown).row
range("c1").value = "=vlookup(a1,datalist!info,2,false)"
range("c1").copy
range("c1:c" & i).select
activesheet.paste
end sub

Now for each sheet that needs this information you can run this macro to copy the formula into the rows that require it. Hope this works for you
This message was edited by bolo on 2002-10-05 20:16

 

[imported_unknown]

YEEHAA !!!
That sure did the trick.
Thank you very much, now I'm almost done with this little project of mine.

Viking S6