Paste values in Excel and not formula

1
votes

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.

3
excelvba
Edited your question for you, but next time, to include a code, indent it by 4 spaces and you're all good. Anyhow, posted an answer based on how I understand your question. – L42
@pmk interesting that you are using my answer but did not mark it as correct. If you get a correct answer then you should mark is as such by clicking the check mark by the answer. – Scott Craner
Ok will do thanks. I am new here but learning. Also the answer is not correct yet, still working on it. – PMK

3 Answers

1
votes

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.

1
votes

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
0
votes
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