Copying an Array of cell values to another sheet

[jimayers]

Hi everyone...I'm having trouble with the following code. My intent is to click on any cell in column "E" on activesheet, that will triger a "find" on sheet("SM") and copy the found value, plus the cell four to the "found cells" left and 6 cells to the its right. The vaslues in these 3 cells need to be copied to the target row on the activesheet in cells "A" "B" and "C"

Private Sub Lookup(Target As Range)
Application.ScreenUpdating = False
Dim pFind As Range
Set pFind = Sheets("SM").Range("E:E").find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart)
If pFind Is Nothing Then MsgBox ("CD# not found on SM Sheet.")
If pFind Is Nothing Then Target.Offset(, -4).Interior.ColorIndex = 6
If pFind Is Nothing Then Exit Sub
[B]Set pFind = pFind.Array(Target.Offset(0, -4), Target, Target.Offset(0, 6)) [/B]'[U]it is hanging up here[/U]
Target.Value = pFind.Array.Value

'Range("A3").Activate
Application.ScreenUpdating = True
End Sub

I think the "array" is hanging me up and I do not know how to use it...or perhaps there is a better way.
Thanks for any help - Jim A

 

[cerfani]

just fyi... you can do this...

If something Then
    'put lots of code here
    'as many lines as you need
End If

example...

If pFind Is Nothing Then
    MsgBox ("CD# not found on SM Sheet.")
    Target.Offset(, -4).Interior.ColorIndex = 6
    Exit Sub
End If

btw Find returns a range...

so what u want to do is... range1.Value = range2.Value

it will work fine if they are the same shape

you should always refer to the functions documentation on msdn if you never used... https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

oh and btw... Range.Array does not exist... you need to refer to documentation... I would not rely on the excel devs to name things properly and make them intuitive lol

 

[jimayers]

OK..I acomplished what I wanted with 3 statements like:

Target.Offset(0,-1).value = pFind.Offset(0,-4).value

and so on.
I cleaned up my If Then statemnst too. I guess I just wanted to try something fancier.
Thanks - Jim A

 

[cerfani]

i will admit, putting single lines like you did is nicer to read but you are asking the cpu to do more work. For small macros and processes, it is irrelevant but you should always try to write efficient code. No prob, hope I helped.