Concatenation of Cell values

Hi,

Please help me in this. Here is my scenario

I am having 3 sheets. Each sheet contains some header. But out if these header i need to take values from only three header Namely ID,value/Data and Platform. In ID cell only one value is present. In Value/Data cell containg more than single line. I need to take the first line. In platform cell i may have more than one value in each line of a cell. So my output value is in a different sheet name as Sheet1-Mod in a single cell. Resultant value should come as ID_Platform_value/Data if only one platform value. If 2 values then it will come as ID_Platform1_value/Data and ID_Platform2_value/Data in next cell.Excel sheet row is a dynamic one. How to do it with VBA.

Refer attachment

Thanks in Advance,
Jeya




Please post Excel questions, in the Excel forum.

Thank you.

Hi Jeya and welcome to the forum! :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

I'm sure we can help you with this - I noticed that you haven't attached anything to your post?

Which part of your task are you having trouble with and what code have you written so far? For example:

Looping through the worksheets?
If they're dynamic, determining which columns have the headers?
Determining which rows contain the first values?
How to concatenate in VBA?
etc... etc...


We'll be happy to answer any specific questions you have but please don't ask us to write the whole project code for you! ;)

If you're getting errors with your code then please post the code together with the the full error message description and an indication of which line is throwing the error and we'll give you a hand with it.

Colin

I am new to VBA. Give me the VBA for a single sheet. in one sheet i am having 10 and other it is 100 rows.

I am having 3 sheets. Each sheet contains some header. But out if these header i need to take values from only three header Namely ID, value/Data and Platform. In a row, ID cell only one value is present. In Value/Data cell containing more line (Alt+Enter) I need to take the first line. In platform cell i may have more than one value in each line of a cell (Alt+Enter). So my output value is in a different sheet name as Sheet1-Mod in a single cell. Resultant value should come as ID_Platform_value/Data for only one platform value. If 2 platform values then it will come as ID_Platform1_value/Data and ID_Platform2_value/Data in next row. Excel sheet row is a dynamic one.

Thanks,
jeya

I am new to VBA. Give me the VBA for a single sheet.
Thanks,
jeya

Hi jeya,

Thanks for your reply and your specifications were quite clear the first time.

I'm sure it won't come as a big surprise to you that a lot of people who ask questions on this board are also new to VBA. As I pointed out, and as outlined in the posting guidelines which you agreed to when you joined, we don't write code to order here. However, we will gladly help you understand that which you need to know in order to be able to write the code yourself. And additionally, if you try some code and get stuck then we will certainly help you solve the problem. :)

So, in these three worksheets, are the three columns you wish to examine always in the same column, for example, Columns A, D, and F?

If these columns have values in, do the values always start from the same row, say Row 2?

If a column has more than one row of data which you want to have in the 'output', are there any empty cells between the first and the last row?

And when you say you are new to VBA, does that mean that you have never done any VBA before? Or have you done some work with the macro recorder? This information would be a great help to us.

Colin

Sorry for that.

Could you please help me in this.

These three columns i am examining may be in different column. But ID is in Column A and other two are in different Column.

Yes all the rows are starting from Row 2. So Row 1 is my header.

From value/Data column, i need to take the first row.

From Platform column, if a cell is having more than 1 row then take all the rows and populate as ID_Platform_value/Data, ID_Platform1_value/Data...

I have extensively worked on excel formulas but done few thing in macro.


Please help...

Thanks in advance,
Jeya

Sure, no problem Jeya.

Let's start with the easiest part - getting the value from the ID column which is always column A, and it is always Row 2. To do this on a worksheet called "Sheet1" we can get the value to appear in a messagebox as follows:


Sub GetValues()
MsgBox Worksheets("Sheet1").Range("A2").Value
End Sub


Now, for an example, what if I wanted to concatenate the value in that cell with the value in the cell below it? I know that you don't specifically want to do it for the ID column in your final code, but trying it now will be useful for us later on.

We can concatenate the two values like this:


Sub GetValues2()
MsgBox Worksheets("Sheet1").Range("A2").Value & Worksheets("Sheet1").Range("A3").Value
End Sub


But that gives the two values without a space in between them. So, how can we add an extra space? Like this:

Sub GetValues3()
MsgBox Worksheets("Sheet1").Range("A2").Value & " " & Worksheets("Sheet1").Range("A3").Value
End Sub


Finally, let's introduce a long variable into this code. This idea can be very handy when we're having to loop through cells. We concatenate the long variable within the string argument that identifies the range's address. This should give the same result as the last procedure:

Sub GetValues4()
Dim i as Long
i = 2
MsgBox Worksheets("Sheet1").Range("A" & i).Value & " " & Worksheets("Sheet1").Range("A" & i + 1).Value
'A2 'A3
End Sub




So, let's leave it there for starters. Have a look at these little examples and look up some of the terms in your VBA helpfile. Does it make sense so far? Is it giving you any ideas on how you might go about achieving your target?

Colin

Thanks for you reply. I am building code from this basic...

thanks,
jeya