Numeric field

[VbaHell]

Hello all

I hope you can help on this please.

I am using excel 2007 and in column "B" I have data that can be text or numeric or text and numeric. I need some VBA code please that will copy to column "C" only the numeric fields from column "B"

 

[njimack]

Sub Copy_Numeric()
Dim r As Range
Dim LR As Long
Dim i As Long
Application.ScreenUpdating = False
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If IsNumeric(Cells(i, 3)) Then Cells(i, 2) = Cells(i, 3)
Next i
Application.ScreenUpdating = True
End Sub

 

[VoG]

Try

Sub Test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        If IsNumeric(.Value) Then .Copy Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1)
    End With
Next i
End Sub

 

[VbaHell]

Thank you so much to both of you

 

[VbaHell]

This code works well but the only problem is that it drops the answer in the next available cell which is not always in line with the original row.
Is it possible to drop the answer in the same row please

 

[VoG]

Try

Sub Test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        If IsNumeric(.Value) Then .Offset(, 1).Value = .Value
    End With
Next i
End Sub

 

[VbaHell]

Thank you VOG I will try this out

 

[VbaHell]

Vog

This code appears to be not working, I can not see in the code statement were it tells you to drop the information into column C

 

[VoG]

The .Offset(,1).Value=.Value does that.

Results of a quick test

Excel Workbook
	B	C
1	a
2	1	1
3	2	2
4	3	3
5	e
6	3	3
7	4	4
8	r
Sheet1

 

[VbaHell]

That is amazing, you have saved me so much work
Many Thanks