Macro needed to Concatenate data in 2 separate tables of 2 separate sheets
Hello,
Please I need your help to to concatenate data -with id number- in 2 separate table of 2 separate sheets.
I've attached below a file INFO to explain you.
I've also attached a "real" file (the number of columns may change) BUT cutted because the rows in the original file are more than 37000 (for better identify the two columns I've marked yellow in both sheets)
Thank you very much for help!
Last edited by 823; 02-08-2015 at 07:44 AM.
Check it out,
Please Login or Register to view this content.
Hi 823,
I am not sure if what I have attached is what you are after but I have done it using formulas instead of a macro.
Regards
peterrc
Originally Posted by davesexcel
It seems that It works, thanks
But in the code If I change DB with another cell ex GY It doesn't works anymore.....WHY?
thanksSub UseFormula()
Dim ws As Worksheet
Dim Rws As Long, Frng As Range
Set ws = Worksheets("1")
With ws
Rws = .Cells(Rows.Count, "A").End(xlUp).Row
Set Frng = .Range(.Cells(2, "GY"), .Cells(Rws, "GY"))
Frng = "=IFERROR(VLOOKUP(RC[-105],'2'!C[-105]:C[-104],2,0),""Not Found"")"
Frng.Value = Frng.Value
End With
End Sub
Thanks you very much!!
It works very well with your forumla!!!
Last edited by 823; 02-08-2015 at 10:15 AM.
Why would you change it to "GY" your sample showed "DB" ?
You have to change the formula as well,
Comment this part of the code
Frng.Value = Frng.Value
-then run the code to see what the formulas looks like.
Please Login or Register to view this content.
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
Guess it was supposed to be GY
Please Login or Register to view this content.
thanks davesexcel
I saw that If I need to change columns (DB to GY) the only thing to change is the column's name (GY) and some numbers...
where I can find/count the numbers 128-127 OR 206-205 ?
Please Login or Register to view this content.Please Login or Register to view this content.
Please I need to understand where I can count that numbers..... If I need to apply the macro to others file...
thanks!
Last edited by 823; 02-08-2015 at 10:50 AM.
Perhaps this will help
Pic1.jpg
To see what the formula looks like in VBA, this is the easiest way.
Run the macro recorder, select the cell with the formula, and select inside the formula bar.
Hit enter key.
Stop the macro recorder.
See what the formula looks like in VBA
recordmacro.jpgFormulaVBA.jpgVBAFormula.jpg
Thanks!
but where I can read in excel that column GY is NUMBER 207 OR 206 ????
In a blank cell somewhere, you can enter this formula:
=COLUMN(GY1)
Hope this helps.
Pete
Are you sure?
With
=COLUMN(GY1)
It appears: #NAME?
It doesn't give me the column's number!
Perhaps you have mis-spelt the word COLUMN.
Pete
no....I tryed another time, on my excel (2013) It retourns #NAME?
any other way to know column number please?
Please Login or Register to view this content.
when you type
=colu
does in not autocomplete as =column
As you are in Italy, perhaps you need the Italian version of the word COLUMN (sorry, I don't know what it is).
Pete
Italian i think It is "COLONNE" OR "COLONNA" but It retourns me always number "1"
Please Login or Register to view this content.
this works, thanks!
Sorry DAVESEXCEL,
so to insert the results in AP column I have to use this code I've modified myself....
Please Login or Register to view this content.
Can you check It please if It is correct for AP column?
THANKS
Last edited by 823; 02-08-2015 at 12:46 PM.
Did you try it?
Formula:
Please Login or Register to view this content.
Why not use my previous post that explains how see what your formula looks like in VBA
But you can simplify it by using
Please Login or Register to view this content.
WAW!
so with this macro:
To change the "column result", I've just to change the column's name (for ex. DB); It is right?Please Login or Register to view this content.
Great! It is very simple!
VERY THANKS!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks