Array with Cells() as the parameters

Question

I have created an array holding Cells() locations as parameters.

When I run the code below it doesn't grab those parameter as I'd hope. Is there something I'm doing wrong or will what I'm trying to do not work this way? The other arrays you see are working accordingly so please ignore those.

Dim varDept As Variant
Dim varSheet As Variant
Dim varCell As Variant 
Set DRag = Range("D2:D200")


varCell = Array(Cells(9, 1), Cells(15, 1), Cells(21, 1))

For Each Custom In DRag
    If PDrag = Empty And Custom = varDept(0) And Custom.Offset(0, -1) = "551098218" Then
        PDrag = Custom.Address(0, 0) & "," & Custom.Offset(0, -3).Address(0, 0)
    ElseIf Custom = varDept(0) And Custom.Offset(0, -1) = "551098218" Then
        PDrag = PDrag & "," & Custom.Address(0, 0) & "," & Custom.Offset(0, -3).Address(0, 0)
    End If
Next Custom

If PDrag = Empty Then Exit Sub

PDrag = Replace(PDrag, "D", "E")
Range(PDrag).Select
Selection.Copy
Worksheets(varSheet(0)).Activate
varCell(0).Select
Selection.PasteSpecial xlPasteAll

What are you trying to do? Normally there's no need to select. — BigBen, Feb 07 '20 at 18:32
What you have there will select cell(9, 1), what do you want it to do? — Warcupine, Feb 07 '20 at 18:34
So this is just a portion of what I have, but the aim is to grab a cell and paste some already copied information into it. — user2898324, Feb 07 '20 at 18:34
So you are saying I need to store the value of the current array location to its own variable and then I can use .select ? Sorry I am very new to any type of coding at all and I have been learning as I go. It just errors out on that line of code for varCell(0).Select — user2898324, Feb 07 '20 at 18:44
[Don't use Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — tigeravatar, Feb 07 '20 at 18:46
Is the error Runtime 1004? That means you are trying to select a cell that is on a different sheet from the currently active sheet. — Warcupine, Feb 07 '20 at 18:49
Run-time error '424': Object required. I'll edit my post and show more of the code if needed. — user2898324, Feb 07 '20 at 18:52
Did you by chance delete cells before this? More code would be helpful. — BigBen, Feb 07 '20 at 18:52
Empty is just checking to see if the PDrag is empty when doing the For.. Each loop. its just the initial check. — user2898324, Feb 07 '20 at 19:05
Personally I would declare `PDrag` as a string and test `PDrag = ""` — Scott Craner, Feb 07 '20 at 19:06
Actually looking at what you want `PDrag` to do , I would use `Union` instead and make `PDrag` a range object. — Scott Craner, Feb 07 '20 at 19:08

Array with Cells() as the parameters

0 Answers0