Simple yet wrong

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can some please check this code and tell me what I'm doing wrong? The idea
is to go down the E column and check for the code 'AJ'. Then, when it
reaches the empty row, it should insert the SUM function 2 columns over (G)
and total everything from E3 to the end of the data. This data will be
imported and will vary in length.

Set TestRng = Range("E3")
If TestRng.Value = "AJ" Then
Do Until TestRng.Value <> "AJ"
Set TestRng = TestRng.Offset(1, 0)
Loop
End If

ThisWorkbook.Names.Add Name:="AjEnd", _
RefersTo:="=$E$3:TestRng", Visible:=False
TestRng.Offset(0, 2).Value =
Application.WorksheetFunction.Sum(AjEnd.Offset(0, 2))
 
Without testing it I think you have a problem with this statement...

RefersTo:="=$E$3:TestRng",
should be
RefersTo:="=$E$3:" & TestRng.Address,
 
I am a little unclear as to what exactly needs to be summed E3 to
(Column??:testRng.row).

Also did you want that to be a formula in the worksheet (whose result will
change if any of the values change) or a constant which is what you are
presently trying to insert?
 
I need the values from G3 to where ever the data ends to be totaled up. I'd
perfer the SUM() worksheet function, but I'll settle for whatever gives me
the total. (I may change the data by removinging a few lines after the macro
is run, but it is unlikely.)
 
TestRng.Offset(0, 2).Formula = "=Sum(G3:G" & TestRng.row & ")"

I think that should work for you.
 
Back
Top