How to turn found cell into a range?

Question

I would really appreciate it if you could help because I really would like to get this to work. I have some code here that is meant to extract information from one part of a spreadsheet and paste it into a table on another part of the spreadsheet.

For i = 1 To 24
        Range("J11:J100").Select
        Set foundCell = Cells.Find(What:="PL " & i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        If Not foundCell Is Nothing Then
            foundCell.Activate
            foundCell.Copy
            Range("P" & (i + 10)).Select
            ActiveSheet.Paste
        End If
        Next

The main issue is that I would like to also copy information from surrounding columns using foundCell as a reference eg. foundCell.Offset(, -4).Copy

I added this but it doesn't work (addendum is marked up):

    For i = 1 To 24
            Range("J11:J100").Select
            Set foundCell = Cells.Find(What:="PL " & i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
            If Not foundCell Is Nothing Then
                foundCell.Activate
                foundCell.Copy
                Range("P" & (i + 10)).Select
                ActiveSheet.Paste
' BEGIN ADDITION
                foundCell.Offset(, -4).Copy
                Range("S" & (i + 10)).Select
                ActiveSheet.Paste
'END ADDITION
            End If
            Next

I tested around and found that the foundCell is instead now referencing the range that the data was pasted to rather than the original range in which it was found.

Can anyone help me? I'm open to anything. I feel like the best solution would be to turn foundCell into a range, but I don't know how to do that.

score 3 · Accepted Answer · answered Jun 15 '16 at 19:21

I think that this will do what you want. After finding the required cell in the range J11:J100, it then looks at the cell in column F corresponding to the found cell and pastes both of them into the required cells.

Sub PasteTest()
Dim i As Integer
Dim SearchRange, foundCell As Range

    Set SearchRange = Range("J11:J100")
    With SearchRange
        For i = 1 To 24
            Set foundCell = .Cells.Find("PL " & i)
            If Not (foundCell Is Nothing) Then
                Range("P" & (i + 10)) = foundCell
                Set foundCell = foundCell.Offset(, -4)
                Range("S" & (i + 10)) = foundCell
            End If
        Next
End With

End Sub

As with the other answer, I've avoided the use of select as it only slows down the code. Using multiple (named) ranges allows you to copy data without the use of the clipboard too.

Do you know if there's a way to paste the linked reference to the cell rather than the data in the cell itself? — GT., Jun 15 '16 at 19:34
To achieve this simply change the line `Range("P" & (i + 10)) = foundCell` to `Range("P" & (i + 10)).Formula = "=" & foundCell.Address` and `Range("S" & (i + 10)) = foundCell` to `Range("S" & (i + 10)).Formula = "=" & foundCell.Address`. — Ralph, Jun 15 '16 at 19:36

score 2 · Answer 2 · edited May 23 '17 at 11:58

2

The following should solve your problem:

For i = 1 To 24
    Set foundCell = Cells.Find(What:="PL " & i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If Not foundCell Is Nothing Then
        foundCell.Copy Destination:=foundCell.Parent.Range("P" & (i + 10))
        ' BEGIN ADDITION
        If foundCell.Column > 4 Then
            foundCell.Offset(0, -4).Copy Destination:=foundCell.Parent.Range("S" & (i + 10))
        End If
        'END ADDITION
    End If
Next i

Basically, the problem were the select you had in your code. You might want to read the following post on how to avoid them in the future: How to avoid using Select in Excel VBA macros

edited May 23 '17 at 11:58

Community

1
1

answered Jun 15 '16 at 18:46

Ralph

9,284
4
32
42

Cool! It works! What's the deal with the 'If foundCell.Column > 4 Then' line though? – GT. Jun 15 '16 at 19:06
This line is just there for good coding practice. Imagine that `foundCell` is in column `C`. Then the next line of code would throw an error because `foundCell.Offset(0, -4)` would refer to the column before `A`. Since there is no such column you would get an error instead. I guess you are pretty certain that `foundCell` should be always in column `D` (or beyond). Yet, you can test it by placing into column `C` (for example `C1` the value `PL 2` and then run the code with / without the `If` clause and you will see the difference. – Ralph Jun 15 '16 at 19:12
Hmm actually I don't know why it stopped working... I'm still confused. For some reason it's only copying over certain values – GT. Jun 15 '16 at 19:25
The values it copies over change depending on which cell I have selected when I start my macro – GT. Jun 15 '16 at 19:26
That's because your `.Find` is starting with the search in the `ActiveCell`. I thought this is what you wanted / needed. If that's not the case then you should change that and set the search range to something different. – Ralph Jun 15 '16 at 19:34
1

Sorry for being unclear. I really do appreciate the help, though. I upvoted your answer. Thanks Ralph. – GT. Jun 15 '16 at 21:29

How to turn found cell into a range?

2 Answers2