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

 

[Trebor76]

Hi Scott,

For reference - here's Michael's code tweaked:

Sheets("Concat").Range("P" & x).Value = Evaluate("Text(Concat!I" & x & ",""mmm-yy"")")

HTH

Robert

 

[Teeroy]

Hi Scott,

You're welcome. This thread generated a lot of discussion on different aspects of your original code and was quite interesting.

Teeroy

 

[farmerscott]

Michael, Robert, Teeroy and Joeu2004...thanks for the help. Much appreciated. Got me over the hump.


Teeroy, I agree the thread bought our some good discussion and ideas. I find it facinating how people can solve a VBA problem from so many angles. VBA has great flexibility in this regard but get the syntax marginally wrong........

cheers

FarmerScott

 

[Teeroy]

The differences are usually in the way people think rather than the language.

If you think the syntax can be difficult in VBA try C < shudder ><shudder>, Python, or to a lesser extent FORTRAN. The compiled languages are generally much faster but are more structurally strict.</shudder>

 

[Michael M]

Ahh, FORTRAN
I remember walking down to the computer room with my box of cards, ready to feed in my code.
UUURGH !!!

 

[Teeroy]

My FORTRAN was at least on a keyboard, usually midnight (the only time we were allowed processing time was 11pm-5am) on the company mainframe doing uni assignments.

 

[farmerscott]

The differences are usually in the way people think rather than the language.

If you think the syntax can be difficult in VBA try C < shudder ><SHUDDER>, Python, or to a lesser extent FORTRAN. The compiled languages are generally much faster but are more structurally strict.</SHUDDER>

Teeroy,

sounds like I don't have enough hair to go down that path.......I have enough to master in VBA.

Just a quick question..... Excel is regarded as the best for number crunching but how evolved is C++ and Java when it comes to such features.

There was thread here the other day on people wanting a front end for Excel, that I found interesting.

thx

FarmerScott

 

[Teeroy]

Teeroy,

sounds like I don't have enough hair to go down that path.......I have enough to master in VBA.

Just a quick question..... Excel is regarded as the best for number crunching but how evolved is C++ and Java when it comes to such features.

There was thread here the other day on people wanting a front end for Excel, that I found interesting.

thx

FarmerScott

FarmerScott,

You take for granted all the functions that are available in excel; in C you either import a library to get a function you need or you write your own. I'm certainly no expert on C, I was learning it for fun (sick I know ) as I couldn't get my head around it when I had a subject on it at uni (a long time ago, in a galaxy far, far away...). I haven't done any C++ or Java (but it looks C'ish).

FYI another useful number crunching program, especially for algebra and matrices, is Smath Studio. It's like a freeware MathCAD.

Why would you want a front end for excel? You just create a UserForm to do that.

Teeroy.

 

[farmerscott]

Hi Teeroy,

thanks for the comments.

I would like to learn some C++ and Java so I can write some little programs for the farm. Most of the farm software I don't like. For example when we were looking for a financial package they gave great tax info for the accountant but poor information that I could retrieve for management purposes, so I just wrote an Access Dbase to cover both.

I think the guys wanted something to front end Excel so it could be a stand alone .exe and have no problems with people changing things.

FS

 

[Teeroy]

Personally I would use the Access database. You can then easily link between access and excel if you need to do some data manipulation.

If you Google Excel2exe you can find a number of programs that claim to make an exe from excel. I thought it would be infringing MS copyright of the program.

ps: I hope you are getting some of the rain spread over NSW.