CODEWITHSUNDEEP
excelvba
PMK
PMK

Reputation: 23

Paste values in Excel and not formula

I need to paste a value in a Excel spreadsheet and not the formula.
The code I have is:

.Cells(lRow, 2).Formula = _
"=IF(A" & lRow & "<"" "",A" & lRow & "&TEXT(E" & lRow & ",""mmddyy""),"""")"

The code is merging two cells into one cell, and is different for each row.

Upvotes: 1

Views: 99

Answers (3)

Xabier
Xabier

Reputation: 7735

Another possible way to get this done would be something like:

Sub foo()
'Sheet1.Cells(lRow, 2).Formula = "=IF(A" & lRow & "<"" "",A" & lRow & "&TEXT(E" & lRow & ",""mmddyy""),"""")"
Dim NewValue As Variant
lRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
If Range("A" & lRow) <> "" Then
    NewValue = Range("A" & lRow).Value & Format(Range("E" & lRow).Value, "mmddyy")
    Sheet1.Cells(lRow, 2).Value = NewValue
End If
End Sub

A word of warning the date values in column E should be formatted correctly for this to work as you expect.

Upvotes: 1

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Sub test()
    lRow = 2 
    If Range("A" & lRow).Value <> "" Then
        Range("B" & lRow).Value = Range("A" & lRow).Value & Application.WorksheetFunction.Text(Range("D" & lRow), "mmddyy")
    Else
        Range("B" & lRow).Value = ""
    End If
End Sub

Upvotes: 0

L42
L42

Reputation: 19727

It seems you still need the result of the formula so just add another line changing the formula to value. Something like:

.Cells(lRow, 2).Value = .Cells(lRow, 2).Value

Upvotes: 1

Related Questions