Adding a formula to a macro

[marcia2026]

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

 

[NoodNutt]

G'day Marcia

Rather than specifying a range, select the whole column.

=Sum(E:E)

HTH
Mark.

 

[Wigi]

Hi Marcia

Have a look here on last cells: http://www.ozgrid.com/VBA/ExcelRanges.htm

 

[ward376]

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