VBA Excel Run-Time Error 6: Overflow help. Sort Data.

[killpaddy]

I am trying to write some VBA code to sort a list of 'materials' (in column A) so that if it appears in another, longer list of 'materials' (column C) the particular cell in A and the cell next to it in column B (the stock value) are cut and placed in column D (and E). However there is an error occuring 'Run-Time error 6: overflow' at the row where i define 'A2' (A2 = Worksheets(1).Range("A" & "1:A" ...).

(also in the second 'ElseIf' there is code to move the cut cells to column F (and G) if there is a repeat value in column A)
Please note: the order of Column C cannot be changed and is essential it remains the same

Dim i As Long
Dim LastRowA As Long
Dim A1 As Long
Dim A2 As Long
Dim C1 As Long
Dim C2 As Long
Dim z As Long

i = 1
z = Columns("A").Find("").row

Columns("A").Sort Columns("A"), xlAscending
LastRowA = Range("A" & i & ":A" & z).Find("").row - 1

For i = 1 To LastRowA

    Worksheets(1).Activate        
    A1 = Worksheets(1).Range("A" & i)
    A2 = Worksheets(1).Range("A" & "1:A" & z).Value.Find(Trim(Range("C" & i).Value))   '''Error Here'''
    C1 = Worksheets(1).Range("C" & i)
    C2 = Worksheets(1).Range("C" & "1:C" & z).Value.Find(Trim(Range("A" & i).Value))   '''[Error presumably here aswell]'

    If C2 Is Nothing And A2 Is Nothing Then
       
            A1.Cut Worksheets(2).Range("A" & i)
            Exists = False
    
    ElseIf C2 Is Nothing And Not A2 Is Nothing Then
        
        Range("A" & A2.row & ",B" & A2.row).Cut Range("D" & i)
     
        Exists = True
        
    ElseIf A2 Is Nothing And Not C2 Is Nothing Then
    
        If Range("A" & i).Value Like Range("A" & i - 1).Value Then
        
            Range("A" & C2.row & ",B" & C2.row).Cut Range("F" & i)
        Else
       
            Range("A" & C2.row & ",B" & C2.row).Cut Range("D" & i)
        End If
            
        Exists = True
        
    Else  '''''''''''''''''''''not essential right now, but complete in future
        Exists = True
        
    End If

Next

How can I eliminate this error, I have tried everything I can think of such as changing some of the 'Dim' s?

Also will the rest of this code work for what I want?

 

[shg]

A2 = Worksheets(1).Range("A" & "1:A" & z)[COLOR=#ff0000].Value.Find[/COLOR](Trim(Range("C" & i).Value))

The Find method applies to a range, not an array. What are you trying to do?

In general, the result of a Find should always be assigned to a range variable so it can be tested (if the Find fails, Nothing will be assigned).

    If C2 Is Nothing And A2 Is Nothing Then

Nothing applies only to object variables, not scalar variables.

 

[killpaddy]

Im assigning A1,A2,C1 and C2 (maybe i should have used less confusing names for these; they are just meant as a way of labeling two cells in column A or C not 'cell A2' or 'cell C2' on the worksheet) the values above in the code just so I dont have to repeatedly write them in the 'If' function, making it neater code.

Im trying to give them a range, for example 'A2' (when i = 1) will be the cell where the value of 'cell C1' is found in coulumn A.

I have changed the first part of the code to

Dim i As Long
Dim LastRowA As Long
Dim A1 As Range
Dim A2 As Range
Dim C1 As Range
Dim C2 As Range
Dim Exists As Boolean
Dim Repeat As Boolean
Dim z As Long

And I have put 'set' infront of each of the A1 = ...,A2 = ...,C1 = ... and C2 = ...., but it is just returning the Value of the cell range, NOT the cell reference itself