Excel VBA a lot of problems

0
votes

So I'm new to VBA, and I am trying to get a macro to compare cells, and output a counter in the column next to it. Here is my code: 

Sub Duplicate_Count()

'Find the last used row in a Column: column A in this example

Dim LastRow As Long
Dim value1 As String
Dim value2 As String
Dim counter As Integer
counter = 1

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
End With

'Search down row for duplicates
Dim i As Long

For i = 1 To LastRow

    'Sets value1 and value2 to be compared
    value1 = Worksheets("Sheet1").Cells(i, "L").Value
    value2 = Worksheets("Sheet1").Cells(i + 1, "L").Value

    'If values are not diferent then counter will not increment
    If value1 <> value2 Then
        counter = counter + 1
    End If

    'Sets the n colom to count, duplicates should not increment the counter
    Sheet1.Cells(i, "N") = counter

Next i

End Sub

Okay so this code runs, and it looks it works, column "N" starts to populate, but the program freezes up, and I do not know if it is just because the file is so large that it takes a lot of time, or if something is wrong. If i restart the program i get run-time error '-2147417848 (80010108)': Method '_Default" of object "Range" failed. Any idea what that means?

Any help would be greatly appreciated, hopefully, I'm not just making dumb mistakes.

EDIT: Sub Duplicate_Count() 'Find the last used row in a Column: column A in this example

Dim LastRow As Long
Dim value1 As String
Dim value2 As String
Dim counter As Long
counter = 0
Dim sht As Worksheet
Set sht = Worksheets("Sheet1")

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
End With

'Search down row for duplicates
Dim i As Long

For i = 1 To LastRow

    'Sets value1 and value2 to be compared
    value1 = Worksheets("Sheet1").Cells(i, "L").Value
    value2 = Worksheets("Sheet1").Cells(i + 1, "L").Value

    'If values are not diferent then counter will not increment
    If value1 <> value2 Then
        counter = counter + 1
    End If

    'Sets the n colom to count, duplicates should not increment the counter
    sht.Cells(i, "N") = counter

Next i

End Sub

This code crashes every time, and will occasionally give me an error of run-time error '-2147417848 (80010108)': Method '_Default" of object "Range" failed. I have no idea how to fix that... or what it even means.

2
excelvba
The first potential problem I notice is that you set counter to 1 each time you loop, do you want it to do that? At the same time, you don't need to Dim each variable every time you loop - Dexloft
No i realize that that could be moved out of the loop, thanks! - Josh
You could use a simple =COUNTIF($A$1:$A$1000,A1) perhaps? - jkpieterse
Okay Dexloft so that seems to have fixed it. It is a huge file so it is taking a long time to finish, I will let you know if i have any other issues. Thanks again! - Josh
jkpieterse, that might work, but the file im going to run this in changes length and i prefer to not have to edit the function every file i need to run the macro in. that is if i understand what you posted... - Josh

2 Answers

0
votes

I don't get an error when I run your code, but I made some changes I think might fix it. Try this and let me know what happens!

 Sub TommysMacro()
    'Find the last used row in a Column: column A in this example
    Dim LastRow As Long
    Dim counter As Integer

    LastRow = Sheets("Sheet1").Cells(65536, "L").End(xlUp).Row + 1

    'Sets values to be compared
    ReDim cellValue(1 To LastRow) As String
    For i = 1 To LastRow
        cellValue(i) = Worksheets("Sheet1").Cells(i, "L").Value
    Next i

    'Search down row for duplicates
    For i = 1 To LastRow - 1

        'If values are not diferent then counter will not increment
        If cellValue(i) <> cellValue(i + 1) Then
            counter = counter + 1
        End If

        'Sets the n column to count, duplicates should not increment the counter
        Sheets("Sheet1").Cells(i, "N").Value = counter

    Next i

End Sub

I just changed it after I saw your comment about it being a large column, I think this should be much faster!

0
votes

Alrighty this is the code I finished with:

Sub Duplicate_Count()

Dim LastRow As Long
Dim value1 As String
Dim value2 As String
Dim counter As Long
counter = 0
Dim sht As Worksheet
Set sht = Worksheets("Sheet1")

'Find the last used row in Column L
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
End With

'Search down row for duplicates
Dim i As Long

For i = 1 To LastRow - 1


    'Sets value1 and value2 to be compared
    value1 = Worksheets("Sheet1").Cells(i, "L").Value
    value2 = Worksheets("Sheet1").Cells(i + 1, "L").Value

    'If values are not diferent then counter will not increment
    If value1 <> value2 Then
        counter = counter + 1
    End If

    'Sets the n colom to count, duplicates should not increment the counter
    sht.Cells(i + 1, "N") = counter

Next i

End Sub

Thank you so much everyone for your help! Turns out that the values are strings because I had some headers being looked at, and setting the worksheet was one of my biggest problems, as to it having the run time error, i believe that is just because the document is so long. I let it sit for 30 min and it completed just fine. Thanks again for the help everyone !