0

Below is my code:

Sub yenilikleri_ekle()

    Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
    Set sh1 = ThisWorkbook.Worksheets("Input")
    Set sh2 = ThisWorkbook.Worksheets("Ayarlar")
    lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
    Set rng = sh1.Range("B5:B" & lr)
    For Each c In rng
        If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
            sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1) = c.Value

        End If
    Next
End Sub

What it does is matching cells in sh1 and sh2. If it's not in sh2 it adds the Cell.Value to sh2. The code is working fine until the cell has a text more then 255 chars. (Until it's a long text)

When the text is long, it returns an error "Run-time error 1004: Unable to get countif property of the worksheet" which comes as nonsense to me. I couldn't fix the problem for 2 days and need your help. I added Dim for c but no change, still same error. I changed to CountA but it then became unresponsive.

Thanks in advance for your help.

pnuts
  • 58,317
  • 11
  • 87
  • 139
ahmet
  • 256
  • 2
  • 17
  • does this helps http://stackoverflow.com/questions/10635048/excel-error-1004-unable-to-get-property-of-worksheetfunction-class-appear – lordkain Aug 19 '14 at 07:23
  • i read that before asking, not helped :( – ahmet Aug 19 '14 at 07:33
  • The second parameter of CountIf is not simply a string. It will be parsed because it can contain wildcard characters and criterias like ">32". So the reason of this error is really the big string in c.Value. If the goal is, to get the content of worksheet "Input" column "B" without duplicates, then you could use RemoveDuplicates. – Axel Richter Aug 19 '14 at 08:12
  • isn't there any other way to improve the code ? or do i need to simply add all and remove duplicates then ? – ahmet Aug 19 '14 at 08:13

1 Answers1

0

If the goal is to have ThisWorkbook.Worksheets("Input").Range("B:B") in ThisWorkbook.Worksheets("Ayarlar").Range("A:A") without duplicates and ThisWorkbook.Worksheets("Ayarlar").Range("A:A") is empty before, then simply this should work:

With ThisWorkbook
    .Worksheets("Input").Columns("B:B").Copy Destination:=.Worksheets("Ayarlar").Range("A1")
    .Worksheets("Ayarlar").Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
End With

Greetings

Axel

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • this does not work as i want.It deletes the values which already there.I don't want that it deletes the previous values.I want that it only add missing cell.values.The formula that you sent is just comparing 2 sheets and doing exact matches. Greetings – ahmet Aug 19 '14 at 10:54