View original (French)

How to rename a column using VBA

strikas06 Posted messages 22 Status Member -  
strikas06 Posted messages 22 Status Member -
Hello everyone,

as a beginner in Excel, I would like to know how to replace the values in a column with the values attached to them. For example, in my first column, I have the values 1, 2, 3, 4, 5, 6, 7, 8, 9 and these numbers correspond to RESPECTIVE student names.

In fact, in another file (2), I have my Sheet1 with in column A the values 1, 2, 3, 4, 5, 6, 7, 8, 9 and in the same order, I have in column B the names of the students.

I would like a macro that will replace (in my first file) the numbers with the associated names by looking up the correspondence in Sheet1 of my second file.

It's a bit complicated but quite interesting to do! But I'm still not good enough to do it alone...

Thanks for any help, have a great day everyone.

2 answers

Answer 1 / 2
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Hello

Are you sure you want to use VBA to do that when a simple VLOOKUP would suffice...?

--
Michel
0
Answer 2 / 2
strikas06 Posted messages 22 Status Member
 
Hi Michel!

Yes, actually using a macro will allow me to do this more quickly instead of dragging the formula each time... And it will take up less space in my file :).

Thank you for taking an interest in my question anyway!
0
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Sorry to insist, but you write the formula in the column on the line of the first number and double-click on the small black square at the bottom right of the cell.

It will take up less space if your workbook exceeds 2 MB, otherwise, the gain is minimal.

Okay, I'll make you something in VBA since it seems to be a matter of life or death; a little patience
;o)
0
strikas06 Posted messages 22 Status Member
 
:))))

Thank you very much Michel! In fact, the binder I'm going to create is going to be very heavy since every day I'll be adding about 100 lines to my table and it needs to last for the entire 2013 period... I think it will weigh more than 2MB.

Thank you very much! Of course, I will be patient ;)
0
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
  
Sub macro_to_please_anyway() Dim EndRow As Integer, Cptr As Integer With Sheets(2) EndRow = .Columns("A").Find("*", , , , , xlPrevious).Row T_list = .Range("A2:B" & EndRow).Value End With Sheets(1).Range("A2").Resize(UBound(T_list), 2) = T_list End Sub
0
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
I'm going to add about 100 lines to my table

???? not specified at the beginning: you're worrying me.....
0
strikas06 Posted messages 22 Status Member
 
Yes, I know... The task I was given is so lengthy to explain that I couldn't describe everything in the message unfortunately :(. Thank you very much, Michel! I will try to integrate this macro into mine!
0