Hi Excel Experts, I'm new here in VBAX so this is my first post. Sincere apologies if this has already been posted before but I have been looking for a answer to this problem without success. Well, I need to concatenate all cells in the column "A" of the Sheet2 to each cell in the column "A"in the Sheet1 and put all results in the Sheet3. For example, in the Sheet1 we have the values: A, B, C and D, in the Sheet2 we have the values Y and Z, so in the Sheet3 we must have the combinations A-Y, B-Y, C-Y, D-Y, A-Z, B-Z, C-Z, D-Z. Im trying to use the For Each Next structure but I really don't know how to proceed in this case. Please, help me!
Welcome to vbaexpress! I am sure that you will be most happy you joined, as there are great folks here who will go out of their way to help.
As to your first question, see if this helps.
[VBA]
Option Explicit
Sub example()
Dim rngSheet1 As Range
Dim rngSheet2 As Range
Dim EaCellIn1 As Range
Dim EaCellIn2 As Range
With Sheet1 'using the sheet's CodeName, OR, ThisWorkbook.Worksheets("Sheet1")
'// Presumes a header row. Set a reference to a range, starting at row 2 in //
'// Column A, and ending in the last cell with data in the same column. //
Set rngSheet1 = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With
With Sheet2 'OR ThisWorkbook.Worksheets("Sheet2")
Set rngSheet2 = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With
'// Now we run thru a nested loop, placing ea returned/concatenated value in the //
'// next available cell in sheet 3 //
For Each EaCellIn2 In rngSheet2.Cells
For Each EaCellIn1 In rngSheet1.Cells
Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Offset(1).Value = _
EaCellIn1.Value & EaCellIn2.Value
Next
Next
End Sub[/VBA]
Hi Mark! Thanks for giving the welcome, I'm sure that you're right about the users of this forum, your fast reply is a prove of this! About the code above, PERFECT, I tested here and it works. The only thing I did is declare the variant Sheet3 as "Dim Sheet3 as Worksheet" and "Set Sheet3 = Sheets("Sheet3"). Thanks for your help, I'm giving my first steps in VBA to Excel so there's a lot of things that I have no idea how to do.
Sheet3 is not a Variant and should not be dimmed. It refers to a specific sheet
[vba]
Dim Sh3 as Worksheet
Set Sh3 = Sheets("Sheet3")
'or, correct but unnecessary, unless you really want this named variant
Set Sh3 = Sheet3
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
I used the wrong word up there, Sheet3 is not really a variant, but in this case, if Sheet1, Sheet2 and Sheet3 are not declared, the compilator displays a error message ("Compilation Error - Variant not defined") when reaches the line with the declaration "With". Am I wrong?
Please notice that I included a reference to using each sheet's CodeName in my post.
[vba]
With Sheet1 'using the sheet's CodeName, OR, ThisWorkbook.Worksheets("Sheet1")
[/vba]
You can look up CodeName in Help, but basically, one could think of this property as the object's (the sheet object) actual name. Just like 'Userform1' (in English version) would be the default name of a newly created Userform.
If your Excel is in a different language, then the default Codenames would be different I believe. In the image below, see how the upper '(Name)' property has 'Sheet1' listed (this is the codename), whereas the second Name property has 'Sheet2 (2)' listed (this one is actually just the current "name" or caption on the sheet's tab.
Concatenate multiple cells to each cell
Reply With Quote
