CODEWITHSUNDEEP
stringvbaexcelmacros
MAK
MAK

Reputation: 7270

Excel Macro : Concatenate string with cell value using macro

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.

Upvotes: 0

Views: 1755

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 3

Related Questions