Excel Macro : Concatenate string with cell value using macro

Question

I am using Excel 2010.

I have the following macro which is used to concatenate the string with cell values.

Sub Mac1()
Dim cell As Range
For Each cell In Range("D3", Range("D65536").End(xlUp))
    If cell.Value = "" Then
        cell.Value = ""
    Else
        cell.Value = cell.Value & " Day"
    End If
Next
End Sub

Note: Getting the string Day appended each and every time when i run the macro.

The expected result should be if the cell is empty then no string to be concatenate with the cell, if the cell is non empty then it should concatenate string Day at only one time with the cell value at the end.

user4039065 · Accepted Answer · 2016-07-05 05:15:30Z

3

Try is as a nested If to check if the string already ends in " Day".

Sub Mac1()
    Dim cell As Range

    With Worksheets("SHeet1")   'KNOW WHAT WORKSHEET YOU ARE ON!!!!!!
        For Each cell In .Range("D3", .Range("D65536").End(xlUp))
            If CBool(Len(cell.Value)) Then
                If Right(LCase(cell.Value), 4) <> " day" Then
                    cell.Value = cell.Value & " Day"
                End If
            End If
        Next cell
    End With
End Sub

answered Jul 5, 2016 at 5:15

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

MAK Over a year ago

Hey! Can you please help me out for this : stackoverflow.com/questions/38199207/…

Collectives™ on Stack Overflow

Excel Macro : Concatenate string with cell value using macro

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related