Excel: Correct my VBA code...


Hi Everybody, I have been staring at this for too long. The following 2 codes tries to get a date and turn it into text, that will in the future be used in a SUMIF formula. Code 1

Sub sumif()
Dim criteria As String
Dim criteria2 As String
Dim sumrange As Range
Dim criteriarange As Range
Dim criteriarange2 As Range
Dim Cells As Range
Worksheets("Concat").Activate
lr2 = Worksheets("Concat").Cells(Rows.Count, "I").End(xlUp).Row
lr3 = Worksheets("Concat").Cells(Rows.Count, "D").End(xlUp).Row
Application.ScreenUpdating = False
Set sumrange = Worksheets("Concat").Range("L2:L" & lr2)
Set criteriarange = Worksheets("Concat").Range("P2:P" & lr2)
Set criteriarange2 = Worksheets("Concat").Range("M2:M" & lr2)
Set Range1 = Worksheets("Concat").Range("E1:E" & lr3)
Dim x As Integer
For x = 2 To lr2
        monthyear = Worksheets("Concat").Cells(x, 8).Value
        result = Application.WorksheetFunction.Text(monthyear, "mmm-yy")
    Cells(x, 16).Text = result
    
                
     Next x
     
        
        
    
    For x = 2 To 120
        Cells(x, 5).Value = WorksheetFunction.sumif(criteriarange, "=" & Cells(x, 15), sumrange)
        
    Next x
Application.ScreenUpdating = False
End Sub
This code gives an Error 91 on the blue line of code when stepping thru the code. I changed the code to below but still getting an error 91 on the blue line of code. Code 2
Sub sumif()
Dim criteria As String
Dim criteria2 As String
Dim sumrange As Range
Dim criteriarange As Range
Dim criteriarange2 As Range
Dim Cells As Range
Worksheets("Concat").Activate
lr2 = Worksheets("Concat").Cells(Rows.Count, "I").End(xlUp).Row
lr3 = Worksheets("Concat").Cells(Rows.Count, "D").End(xlUp).Row
Application.ScreenUpdating = False
Set sumrange = Worksheets("Concat").Range("L2:L" & lr2)
Set criteriarange = Worksheets("Concat").Range("P2:P" & lr2)
Set criteriarange2 = Worksheets("Concat").Range("M2:M" & lr2)
Set Range1 = Worksheets("Concat").Range("E1:E" & lr3)
Dim x As Integer
For x = 2 To lr2
                
        Cells(x, 16).FormulaR1C1 = "=Text(=RC[-8], ""mmm-yy"")"
     
     Next x
     
        
        
    
    For x = 2 To 120
        Cells(x, 5).Value = WorksheetFunction.sumif(criteriarange, "=" & Cells(x, 15), sumrange)
        
    Next x
Application.ScreenUpdating = False
End Sub
thanks FarmerScott


This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of August 17, 2014.


For more resources for Microsoft Excel