farmerscott
Well-known Member
- Joined
- Jan 26, 2013
- Messages
- 819
- Office Version
- 365
- Platform
- Windows
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
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
thanks
FarmerScott
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
Code:
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
Code:
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