Identify the content of the cell and copy correspondent value via Excel Macro

166 views Asked by At

I tried to create a vba code which helps me to identify whether Range("A1:A5")has any number value, if it identifies the content has a number copy the Range("D10")and paste the same value in each Range(B1:B5")

Since I am new to the vba writing, the code I developed does not work well.

How do I solve following matter?

Sub Findvalues()

 Dim rng As Range, Cell As Range

 Set rng = Range("A1:A3")            'Cells data content'
 For Each Cell In rng

 If Cell.Value = "@" Then           'To identify whether cell has a number'
 Range("A10").Select                'copy ("A10") value
 Selection.Copy  

 Range("B1:B5").Select       'This is the line needs to be corrected'
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End If
Next Cell
End Sub
2

There are 2 answers

1
Scott Craner On BEST ANSWER

This will fill B in with what is in A10 if A has a number:

Sub Findvalues()
    With Worksheets("Sheet7") 'change to your sheet
        Dim rng As Range
        Set rng = .Range("A1:A5")            'Cells data content'
        .Range("B1:B5") = .Evaluate("IF(ISNUMBER(" & rng.Address & "),A10,"""")")
    End With
End Sub
2
Tate Garringer On

If you want to test for a number in Cell.Value, try IsNumeric(). You can also change your Select-Copy method to let Range("B1:B5") = Range("A10") since you're only copying values.

Sub Findvalues()

Dim rng As Range, Cell As Range

Set rng = Range("A1:A3")
For Each Cell In rng
    If IsNumeric(Cell.Value) Then
        Range("B1:B5") = Range("A10")
    End If
Next Cell
End Sub

To accommodate your comment,

Sub Findvalues()

Dim rng As Range, Cell As Range

Set rng = Range("A1:A5")
For Each Cell In rng
    If IsNumeric(Cell.Value) Then
        Cell.Offset(0,1) = Range("A10")
    End If
Next Cell
End Sub