Macro - performing simple calc. on ever-changing data

Posted by Jeff on January 09, 2002 3:44 PM

Hi. I've decided to take the plunge and start using a macro to do some of my daily tasks. I'm importing a daily text report, sorting it to columns, calculating an average on one of the columns and calculating a sum on another. The problem is that the final cells change daily. For example, my average today might include J1:J200, but tomorrow I would need the average for J1:j250. Right now, Excel is just using the cell ranges from the sheet in which I record the macro instead of changing the range to fit the new data.

Any ideas?

Thanks a lot,
Jeff

Posted by Nate Oliver on January 09, 2002 4:12 PM

Hi Jeff, good decision! This problem looked familiar and the code below works for me. Two things, you may want to change the msgbox to a more appropriate message. Second, I put extra code in here to make your average number bolded, simply change the 'false' to true (delete this line if you don't want it. Good luck.

Cheers!

Sub Pretty_Average()

If Range("j1") &LT;&GT; "" Then
Range("j1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Money", RefersToR1C1:=Selection
Range("j1").Select
If Range("j2") &LT;&GT; "" Then
Selection.End(xlDown).Select
End If
Selection.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=average(Money)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Selection.Font.Bold = False
ActiveWorkbook.Names("Money").Delete
Range("a1").Select
Else: Range("a1").Select
MsgBox prompt:="Looks like someone forgot the data...Show me the Data.", _
title:="Oops..."
End If
End Sub

Posted by Nate Oliver on January 10, 2002 9:09 AM

Probably should add one more if statement

This is better

Sub Pretty_Average()

If Range("j1") &LT;&GT; "" Then
Range("j1").Select
If Range("j2") &LT;&GT; "" Then
Range(Selection, Selection.End(xlDown)).Select
End If
ActiveWorkbook.Names.Add Name:="Money", RefersToR1C1:=Selection
Range("j1").Select
If Range("j2") &LT;&GT; "" Then
Selection.End(xlDown).Select
End If
Selection.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=average(Money)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Selection.Font.Bold = False
ActiveWorkbook.Names("Money").Delete
Range("a1").Select
Else: Range("a1").Select
MsgBox prompt:="Looks like someone forgot the data...Show me the Data.", _
title:="Oops..."
End If
End Sub

Cheers!

Posted by Jeff on January 10, 2002 10:41 AM

Re: Probably should add one more if statement

Thanks a lot for your help, Nate. After a little tweaking to meet my exact needs, this worked like a charm.

Jeff

Posted by Nate Oliver on January 10, 2002 11:37 AM

Re: Probably should add one more if statement

You're welcome. Regards,

Nate Thanks a lot for your help, Nate. After a little tweaking to meet my exact needs, this worked like a charm. Jeff : This is better :