CODEWITHSUNDEEP
excelvba
Clayton Borgmeyer
Clayton Borgmeyer

Reputation: 11

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

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!

Upvotes: 1

Views: 10922

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33484

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

Upvotes: 1

PowerUser
PowerUser

Reputation: 11801

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 & ")"

Upvotes: 0

Related Questions