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

Question

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

If Cell.Value = "@" Then will only return true if the cell has `@` in it. The actual symbol. — Scott Craner, Jan 17 '19 at 20:28
So if any of the cells in A1:A3 has a number then copy that one value into ALL B1:B5, or only the corresponding rows. If A1 is a number then B1 gets the value but if A2 is not a number then B2 stays null? — Scott Craner, Jan 17 '19 at 20:34
Yes, you are 100% correct. I need to paste the value only for the number cells as an example if A1 has a number then B1 gets the value if A2 does not have a number then B2 should be blank. — Nilusha M., Jan 17 '19 at 21:38
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Jan 18 '19 at 07:24

Tate Garringer · Answer 1 · 2019-01-17T22:58:59.630

3

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

edited Jan 17 '19 at 22:58

answered Jan 17 '19 at 20:34

Tate Garringer

1,509
1
6
9

I would Really appreciate for answering my problem but I need to paste it only for the number cells not in every cells in B1 to B5. If A1 has value then B1 gets if A2 has no value then B2 should be blank. – Nilusha M. Jan 17 '19 at 21:42
Added to the answer to accommodate your comment. – Tate Garringer Jan 17 '19 at 22:59

score 1 · Accepted Answer · answered Jan 17 '19 at 22:00

1

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

answered Jan 17 '19 at 22:00

Scott Craner

148,073
10
49
81

Thank you for the assistance actually it worked well. – Nilusha M. Jan 18 '19 at 16:12

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

2 Answers2