0

I'm trying to copy a column of data from one sheet into an empty sheet containing only the matching "tickers" for a large list...once this is done I'm looking to delete all duplicates or do not copy them at all.

A) Could someone help figure out why I'm getting an error when setting the "Ticker"

B) What would the most efficient method be for removing the duplicates?

Dim BottomRow As Long
Dim TopRow As Long
Dim col As Integer
Dim Ticker As String
Dim RngY As Range

TopRow = 6

For col = 4 To 3 + (2 * 26) Step 2

Ticker = .Cell(TopRow - 1, col - 3).Value
BottomRow = .Cells(.Rows.Count, col).End(xlUp).row
.Range(.Cells(TopRow, col), .Cells(BottomRow, col)).Copy

Worksheets("TSX-CleanDate").Activate
RngY = Worksheets("Source").Range("3A:3XFD").Find(Ticker, lookat:=xlPart)

.Cells(4, RngY).Paste

Next
Arslanbekov Denis
  • 1,674
  • 12
  • 26
OMO
  • 5
  • 6
  • There is no worksheet reference to qualift the `.` in `.cells` or `.range` –  Oct 19 '18 at 16:45
  • You are using `.Cell` and `.Range`, but, as far as I can it is not referring to anything since your are not using `With... End With` See [this](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/with-end-with-statement) for details – cybernetic.nomad Oct 19 '18 at 16:45
  • Are you missing the `With` block somewhere in the posted code? – Comintern Oct 19 '18 at 16:45
  • 1
    Also the first `.Cell` should be `.cells` – Jchang43 Oct 19 '18 at 16:45

1 Answers1

1

The reason you are getting an error when setting Ticker is because you are not referencing any worksheet in your code.

Ticker = .Cell(TopRow - 1, col - 3).Value

That .Cell part would only work if your code was in a With block, but since it's not, you need to fully qualify the worksheet like so (also, you should be using Cells instead of Cell):

Ticker = Sheets("Sheet1").Cells(TopRow - 1, col - 3).Value

As for part B, check out this answer.

Joseph
  • 5,070
  • 1
  • 25
  • 26
  • You are repeating the original `cell` vs `cells` made in the original (mentioned in comments above by @Brotato) –  Oct 19 '18 at 16:51
  • @Jeeped I realized that after I posted the answer. We were all within a minute of each other :) – Joseph Oct 19 '18 at 17:28