Correct my VBA code...

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
819
Office Version
  1. 365
Platform
  1. 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

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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Thanks, you were right.

Just don't understand why I needed it as I already had

Code:
Worksheets("Concat").Activate

At the top of the code.

cheers

FarmerScott
 
Upvote 0
Just don't understand why I needed it [explicit worksheet qualifier] as I already had Worksheets("Concat").Activate [at] the top of the code.

You probably put your macro into a worksheet object -- for example, right-clicked on the worksheet tab, then clicked on View Code -- instead of in a regular module (in VBA, click on Insert, then Module).

In a worksheet object, the default qualifier is "Me.", where "Me" is the worksheet. Thus, Cells(...) is Me.Cells.

In a regular module, the default qualifier is "ActiveSheet.". So Cells(...) is ActiveSheet.Cells(...).

You probably want to move the macro to a regular module to avoid such mistakes in the future.

Also, then you can remove all the Worksheet("Concat") references, which will make your code more efficient. Maybe not so important in this macro. But a useful lesson in general.

Also, variable x should be type Long, not type Integer. That allows for worksheets with more than 32K rows.

In general, always use Long for integers and Double for non-integers.
 
Last edited:
Upvote 0
Hi Scott
Could you not evaulate the Text function to the cell
I haven't got Excel at the moment, so check the syntax
Code:
For x = 2 To lr2
        monthyear = Worksheets("Concat").Cells(x, 8).Value
        Cells(x, 16) = Evaluate("=Text(monthyear, ""mmm-yy"")")
     Next x
 
Upvote 0
The Text property of a Range object is read only, that's why you are getting an error.
Try setting the NumberFormat of the cell to Text first then inserting the date using the VBA Format function. e.g

Code:
Cells(x, 16).NumberFormat = "@"
Cells(x, 16).Value= Format(monthyear, "mmm-yy")
 
Upvote 0
Hi Joeu2004,

Yes, you have it in 1. I had the code in a worksheet rather than a module. It is a bad habbit.



Can you help me in relation to the code above? It is part of a larger code that allows me to input actual costs against budgetted figures.



I have a series of transactions which I want to sum by month and cost code, using sumifs. (I can't use a pivot table.)

The column header is a date from a series fill 1/1/14 to 1/6/14 but formatted Jan-14 to Jun-14.

The transactions have dates such as 30/6/14, 1/5/14, 31/4/14 but formatted as 30-Jun-14, 1-May-14, 31-Apr-14.


I need a way to so that the transaction dates can be 'matched' to the column month. I have tried converting the date of the transaction by using the line


Code:
criteriarange.FormulaR1C1 = "=Text(R[1]C[-8], ""mmm-yy"")"

but this changes the date from 30/6/14 (formatted as 30-Jun-14) to 14/6/14 (formatted as 14-Jun).

thanks,

FarmerScott
 
Upvote 0
Hi Michael,

Your code errored so I changed it to-

Code:
For x = 2 To lr2
        monthyear = Worksheets("Concat").Cells(x, 8).Value
        Worksheets("Concat").Cells(x, 16) = Evaluate("=Text(monthyear, ""mmm-yy"")")
     Next x

But all it now gives me is "#name?" in the cell.

cheers

FarmerScott
 
Upvote 0
Ok, I think the "=" is the problem there !
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top