Excel VBA, searching values in specific cells in a named range

Question

I'm rather new to all of this and have looked everywhere.

I have a named range "Ruptures" (N14:P60) in sheet 7 in which I would like to find in column P values greater than 0 and paste corresponding cells N:P in sheet 9 F:H. I can't seem to search only column P.

This is what I've tried so far:

Dim cell As Variant
Dim count As Long
count = 0

With Sheet7

Set Rng = Sheet7.Range("Ruptures")

For Each cell In Rng

'But I only want to check cells from the P column (this is where I am stumped)

If cell.Value > 0 Then
Range(cell.Offset(0, -2), cell.Offset(0, 0)).Copy

Sheet9.Activate
Range("F14", "H14").Offset(count, 0).PasteSpecial (xlPasteValues)

count = count + 1

Sheet7.Activate

Next

End With

End If

End Sub

Thank so much in advance and have a great day ! :)

Ursula

It looks like your `End If` should be before your `Next`, for an immediate glance — PartyHatPanda, Jan 05 '17 at 21:11

score 0 · Answer 1 · edited May 23 '17 at 11:46

This is iterating over the entire (multi-column) range:

Set Rng = Sheet7.Range("Ruptures")
For Each cell In Rng

You can limit this to the third column using this:

For Each cell in Rng.Columns(3)

I would also simplify the copy/paste to avoid unnecessary worksheet Activate and to use direct Value assignment from one range to the other (see here for more detail as well). And as noted in the comments, your Next statement can't be inside the If block:

For Each cell in Rng.Columns(3)

    If cell.Value > 0 Then
        Sheet9.Range("F14", "H14").Offset(count, 0).Value = Range(cell.Offset(0, -2), cell.Offset(0, 0)).Value
        count = count + 1
    End If

Next

This could be further refined using Resize methods:

For Each cell in Rng.Columns(3)

    If cell.Value > 0 Then
        Sheet9.Range("F14").Offset(count).Resize(1,3).Value = cell.Offset(0,-2).Resize(1,3).Value
        count = count + 1
    End If

Next

Thank you very much! It didn't work for me probably because of all the other code involved. But, thanks to your answer, I was able to think things through differently and was able to make this project work. Thanks again! :) — Ursula, Jan 07 '17 at 22:09

Excel VBA, searching values in specific cells in a named range

1 Answers1