Correct my VBA code...

[farmerscott]

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")
[COLOR=#0000ff]    Cells(x, 16).Text = result
[/COLOR]    
                
     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
                
        [COLOR=#0000ff]Cells(x, 16).FormulaR1C1 = "=Text(=RC[-8], ""mmm-yy"")"
[/COLOR]     
     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

 

[farmerscott]

Teeroy,

Nothing better than being in bed and hearingt the rain on a tin roof.

Not drought breaking rain but a good start.

FS