VBA advise - Isnumeric function

[neveu]

hello,
I've designed a macro that would copy values based on some criterias but it doesn't work as expected.

I need to do the following:
if the value in cell A2 is blank and the value in cell B2 is a number, then to copy the value of A1 in A2

for ex: 1111 12334
12356

to become:1111 12334
1111 12356

I've written a code that does this, but the only problem is that it applies the algorithm also when on cell B i have blank.
i have used the Isnumeric function to test if a value is numeric or not. i've observed that when applying this function on a blank field it returns true.

what can i do to avoid this issue?

here is the code

Sub copy_customer_name_acrros_s2()
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If IsNumeric(Cells(i, 2).Value) = True And Cells(i, 1).Value = Empty Then
    Cells(i, 1).Value = Cells(i - 1, 1).Value
End If
Next i
End Sub

 

[JackDanIce]

Tested and works for me, try:

Sub copy_customer_name_acrros_s2()
 
Dim i As Long
 
For i = 2 To Range("A" & Rows.Count).End(xlUp).row
    If IsNumeric(Range("B" & i)) And Len(Range("A" & i)) < 1 Then _
        Range("A" & i) = Range("A" & i - 1)
Next i
 
End Sub

I highly recommend you initialise your variables before using them in your code, instead of just using them and leaving it up to Excel to determine what they (which I think usually defaults to type variant)

 

[neveu]

thank you very much. it works

i've found a solution that worked

i've added Cells(i, 3).Value <> "" in the IF statement so that it will avoid blank cells.

thank you very much

 

[JackDanIce]

i've added Cells(i, 3).Value <> "" in the IF statement so that it will avoid blank cells.

thank you very much

Why are you testing column C for non-empty cells? You didn't mention that in your initial posting..

 

[pgc01]

hello,
... i have used the Isnumeric function to test if a value is numeric or not.

Hi

Just so it is clear: IsNumeric() does not test if the value is a number, it tests if the value can be converted to a number.

In the code I post the variable s is not a number type, it is a string and its value even has a space before the digits, but since it can be converted to a number, IsNumeric() will return True.

Sub Test()
Dim s As String
 
s = " 123"
MsgBox IsNumeric(s)
End Sub