0

I'm using Excel 2010, trying to build collection from range and then copy it into another worksheet using this code:

Set g_colTGDNames = New Collection

Worksheets("CONF").Activate
Range("TGDNames").Select
Do Until ActiveCell.Value = ""
    g_colTGDNames.Add ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
Loop

Worksheets("TGDOut").Activate
Range("SymbolsOUT").Select
For Each obj In g_colTGDNames
    ActiveCell.Value = CStr(obj)
    ActiveCell.Offset(1, 0).Select
Next

Collection builds successfuly, but after I switch worksheets I got error 1004 on line 

Range("SymbolsOUT").Select

I have no idea why, it works when i'm building collection. Any ideas?

JvdV
  • 70,606
  • 8
  • 39
  • 70
Siewak
  • 35
  • 1
  • 4
  • You don't need to select ranges first to build a collection. Read [this](https://stackoverflow.com/q/10714251/9758194). That being said. Do you actually have a named range called "SymbolsOUT" on that specific worksheet? – JvdV Sep 30 '19 at 06:39
  • Yes, I do. I've tried to just copy those to ranges with Set rngIN = Range("TGDNames") and Set rngOUT = Range("SymbolsOUT") and one on 1st worksheet works fine while second one returns same error – Siewak Sep 30 '19 at 06:47

1 Answers1

1

To answer your question, you get an error because you try to select a range that does not exist. Why ?

Range("SymbolsOUT").Select

means actually

'Select the range "SymbolsOUT" from the worksheet where your function is written
Me.Range("SymbolsOUT").Select

What you really want is 

ActiveSheet.Range("SymbolsOUT").Select

Now, the @JvdV comment is totally right : you don't need to use Select and Activate to achieve what you are doing. It makes the code messy and furthermore it is really a pain for performances :

Dim mycell as Range
Set mycell = Worksheets("TGDOut").Range("SymbolsOUT")
Do Until mycell.Value = ""
    g_colTGDNames.Add mycell.Value
    Set myCell = myCell.Offset(1, 0)
Loop
Olivier Depriester
  • 1,615
  • 1
  • 7
  • 20