Copy last cell with number and Paste

[eaje92]

Hi, I need help in using vba to copy the last cell which contains only a number and then pasting it to the row beside. Assuming the value below the last cell in column one gets updated with a value
For example,

Initial
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Column 1[/TD]
[TD="align: center"]Column 2[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]NA (this value would get updated) [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]NA[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]NA[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]NA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


After 1 iteration
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Column 1[/TD]
[TD="align: center"]Column 2[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"]*32*
[/TD]
[/TR]
[TR]
[TD="align: center"]NA (Next cell to get updated)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]NA[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]NA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like vba to search for the last cell in column 1 which has a number(not "NA") and then copy and pasting it in column 2.

Much appreciated!

 

[Scott Huish]

This is assuming columns A and B:

Sub LastNumber()
On Error Resume Next
Dim x
x = Evaluate("LOOKUP(9.999999999E+307,A:A)")
If Not (IsError(x)) Then Range("B" & Rows.Count).End(xlUp).Offset(1) = x
End Sub

 

[offthelip]

this will do it;

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
For i = lastrow To 1 Step -1
 If IsNumeric(inarr(i, 1)) Then
  Cells(i, 2) = inarr(i, 1)
  Exit For
 End If
Next i


End Sub