VLOOKUP AND LOOP IN VBA

[Alisan]

Dears,

It is my very first question in this forum and would be very glad if you could help me.
I need to write a macro which looks up to column C (A dropdown menu, which is selected manually), and if the column C is called "Header", I want that Macro merges the cells between E and W on that specific line.
I wrote the below code, but it doesn't work because there is no loop and no vlookup as you see.
The below code only works for line 34, but I want from Excel that it checks every line from C18 until C267, and when any of these cells are called "Header", I want that Excel merges the columns E-W on that line.

Would you be able to help me ?

My best regards, Alisan


Sub mergeCellsBasedOnCriteria()

Dim val As String
Dim rng As Range

Set rng = Range("E34:W34")

For Each cell In rng
val = "Header"
Next cell

With rng
.Merge
.Value = Trim(val)
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

End Sub

 

[Momentman]

What purpose is the VLOOKUP serving?
When you merge the cells, you know you pretty much lose every other data and it retains only the value in the first cell (column E in this case)

 

[Alisan]

What purpose is the VLOOKUP serving?
When you merge the cells, you know you pretty much lose every other data and it retains only the value in the first cell (column E in this case)

Hello Momentman,

Thank you very much for your reply.

Actually there is a formula in columns E-W and when the column C is selected as “Header”, cells between columns E-W are becoming automatically empty, so therefore I will not lose any data because they will be empty anyway.

 

[Momentman]

Hello Momentman,

Thank you very much for your reply.

Actually there is a formula in columns E-W and when the column C is selected as “Header”, cells between columns E-W are becoming automatically empty, so therefore I will not lose any data because they will be empty anyway.

You can try something like this

I added a few of your lines. it is always good to test on a copy of the data

Sub loopandMarge()
    Dim lastrow As Long, I As Long
    lastrow = Range("C" & Rows.Count).End(xlUp).Row
    
    Application.DisplayAlerts = False
    For I = 1 To lastrow
        If Cells(I, "C") = "header" Then
            With Range("E" & I & ":W" & I)
                .Merge
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = True
                .Font.Bold = True
            End With
        End If
    Next I
    Application.DisplayAlerts = True
End Sub

 

[Alisan]

You can try something like this

I added a few of your lines. it is always good to test on a copy of the data

Sub loopandMarge()
    Dim lastrow As Long, I As Long
    lastrow = Range("C" & Rows.Count).End(xlUp).Row
   
    Application.DisplayAlerts = False
    For I = 1 To lastrow
        If Cells(I, "C") = "header" Then
            With Range("E" & I & ":W" & I)
                .Merge
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = True
                .Font.Bold = True
            End With
        End If
    Next I
    Application.DisplayAlerts = True
End Sub

Hello Momentman,

thank you very much for your help.
I will try it tomorrow morning.

my best regards

 

[Alisan]

Hello Momentman,

Thank you very much, it works now, I am very appreciated.
My best regards, Alisan

 

[Alisan]

Can I give you maybe a rating as thank you ? (And how can I do it ? since I am very new on this platform I don't know exactly how to )

 

[Momentman]

Can I give you maybe a rating as thank you ? (And how can I do it ? since I am very new on this platform I don't know exactly how to )

If the solution meets your needs, you should see a checkmark "Mark as solution", to the right of my reply(post) above so others who visit this post can see at least a solution that worked if they have same challenge