Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a formula to a macro
I have been building upon Ron's macro that merges worksheets together into a
master worksheet. But I need to add 2 more steps to it. I need for it to sum column E. The problem is that the range will vary each time the macro is run. I still do not understand how to do the code for finding the last row. I also need for it to add an if statement that puts a value in one of two columns based on a value in a third column. My formula that I have been entering manually is: =If F1 = "R" then G1=E1 else H1=E1. I need this formula to loop through each row in the table, but the end of the table varies. I really appreciate all the help I have received from you guys. This is my very first attempt to do any programming. THANKS BUNCHES |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a formula to a macro
G'day Marcia
Rather than specifying a range, select the whole column. =Sum(E:E) HTH Mark. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a formula to a macro
Hi Marcia
Have a look here on last cells: http://www.ozgrid.com/VBA/ExcelRanges.htm -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "NoodNutt" wrote: G'day Marcia Rather than specifying a range, select the whole column. =Sum(E:E) HTH Mark. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a formula to a macro
Are you talking about Ron DeBruin? His site includes in it's eaxamples
a function that returns the number of the last row: Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function It's very easy to use and highly effective. The only situation I've found where it didn't work perfectly is on a filtered dataset. Here's some code that does what you mentioned in your post: Sub way() Dim lastR As Long Dim ws As Worksheet Dim sumE As Double Set ws = Sheet1 'put your sheet reference here lastR = LastRow(ws) 'get the number of the last row 'use the number of the last row to define the range sumE = Application.Sum(ws.Range("e2:e" & lastR)) 'you didn't mention what you were doing with the sum _ of column e, so I just stored it in variable sumE Dim c As Range 'again use the number of the last row to define the range For Each c In ws.Range("f2:f" & lastR) If c.Value = "R" Then c.Offset(0, 1).Value = c.Offset(0, -1).Value Else c.Offset(0, 2).Value = c.Offset(0, -1).Value End If Next c End Sub Function LastRow(sh As Worksheet) 'copied from _ http://www.rondebruin.nl/copy1.htm _ near the bottom of the page On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding to a Macro | Excel Discussion (Misc queries) | |||
macro adding formula to new sheet | Excel Discussion (Misc queries) | |||
adding a formula to a macro | Excel Discussion (Misc queries) | |||
adding a formula in macro | Excel Programming | |||
Adding a character to a defined name with a formula or macro | Excel Discussion (Misc queries) |