How to loop through rows and columns and Concatenate two text cells in Excel VBA?

Question

I am fairly new to Excel Macros and I am looking for a way to loop through the row headings and columns headings and combine them into one cell for each row and column heading until I have combined all of them.

An example of the First Column cell would be "Your Organizations Title"

An Example of the First Row Cell Would be "22. Cheif Investment Officer"

An example of the first combined cell that I want on a new sheet would be this: "22. Chief Investment Officer (Your Organization's Title)

I then want the combined cells on the new sheet to offset one column to the right until it has iterated through all of the rows and columns.

I have just joined the forum and it will not let me post images or I would have. Perhaps this gives a better idea, here is my code now:

Sub Fill()

' Select cell A2, *first line of data*.
Set title = Sheets("Compensation, 3").Range("B6:B500")
Set descr = Sheets("Compensation, 3").Range("C5:AAA5")
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(title.Value)
  Do Until IsEmpty(descr.Value)
    ActiveCell.Offset(0, 1).Formula = _
      "=title.value & "" ("" & descr.value & "")"""
    Set descr = descr.Offset(0, 1)
  Loop
  Set title = title.Offset(1, 0)
Loop

End Sub

When I run it goes puts this into the active cell:
=title.value & " (" & descr.value & ")"
It does not recognize the variables and come up with the NAME error. It also goes into an infinite loop with no output besides the one cell.

Edit: I cannot answer my own question because I am new to the forum, but using a combination of your answers I have solved the problem! Here is the finished code:

Sub Fill()
  ' Select cell A2, *first line of data*.
  Set title = Sheets("Compensation, 3").Range("B6")
  Set descr = Sheets("Compensation, 3").Range("C5")
  offsetCtr = 0
  ' Set Do loop to stop when an empty cell is reached.
  Do Until IsEmpty(title.Value)
      Do Until IsEmpty(descr.Value)
         ActiveCell.Offset(0, offsetCtr).Formula = title.Value & " (" & descr.Value & ")"
         offsetCtr = offsetCtr + 1 
         Set descr = descr.Offset(0, 1)
     Loop
     Set descr = Sheets("Compensation, 3").Range("C5")
     Set title = title.Offset(1, 0)
  Loop

End Sub

Thank you so much!

what is wrong with your pseudocode? what kind of help you need? can you show screen shot presenting before >> after tables? — Kazimierz Jawor
– Kazimierz Jawor, Commented Jul 9, 2013 at 18:57
I have just joined the forum and it will not let me post images of I would have. — Clayton Borgmeyer
– Clayton Borgmeyer, Commented Jul 9, 2013 at 19:09
you could upload it anywhere and add a link in comment or as edition of your question. — Kazimierz Jawor
– Kazimierz Jawor, Commented Jul 9, 2013 at 19:11
Welcome, to SO. Since you've solved your problem with the help of the answers, you should accept one of the answers and possibly upvote answers. — CuberChase
– CuberChase, Commented Jul 9, 2013 at 22:48

shahkalpesh · Accepted Answer · 2013-07-09 19:55:26Z

Option Explicit
Sub GenerateAndPasteFormulaForTitleAndDescription( _
ByVal titlesRange As Range, ByVal descriptionRange As Range, _
ByVal startCellOnDestination As Range)
Dim title As Range
Dim descr As Range

Dim offsetCtr As Long

Dim formulaTemplate As String
Dim newFormula As String

formulaTemplate = "=CONCATENATE([1], '(', [2], ')')"


startCellOnDestination.Worksheet.EnableCalculation = False

For Each title In titlesRange.Cells
    For Each descr In descriptionRange.Cells
        If title.Value <> "" And descr.Value <> "" Then
            newFormula = Replace(formulaTemplate, "[1]", _
                title.Address(External:=True))
            newFormula = Replace(newFormula, "[2]", _
                descr.Address(External:=True))
            newFormula = Replace(newFormula, "'", Chr(34))

            startCellOnDestination.Offset(0, offsetCtr).Formula = newFormula
            offsetCtr = offsetCtr + 1
        End If
    Next
Next

startCellOnDestination.Worksheet.EnableCalculation = True
End Sub

Here is how to call the above procedure

GenerateAndPasteFormulaForTitleAndDescription _
   Sheets("Compensation, 3").Range("B6:B500"), _
   Sheets("Compensation, 3").Range("C5:AAA5"), _
   Sheets("new sheet").Range("B5")

EDIT: The code loops through combination of title and description, checks if both of them aren't empty and creates a formula. It pastes the formula into the start cell (Sheets("new sheet").Range("B5") in this case) and moved ahead and pastes the next formula in the column next to it

PowerUser · Accepted Answer · 2013-07-09 19:36:51Z

0

Basically, you are trying to use VBA objects in worksheet functions. It doesn't quite work that way.

Try replacing

"=title.value & "" ("" & descr.value & "")"""

with

=title.value & " (" & descr.value & ")"

answered Jul 9, 2013 at 19:36

PowerUser

11.8k20 gold badges67 silver badges103 bronze badges

Collectives™ on Stack Overflow

How to loop through rows and columns and Concatenate two text cells in Excel VBA?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related