Simple yet wrong
 

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))

Simple yet wrong
 

Sorry, That should say "total everything G3 to the end of the data"

"Stephen" wrote:

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))

Simple yet wrong
 

Without testing it I think you have a problem with this statement...

RefersTo:="=$E$3:TestRng",
should be
RefersTo:="=$E$3:" & TestRng.Address,
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Sorry, That should say "total everything G3 to the end of the data"

"Stephen" wrote:

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))

Simple yet wrong
 

Thanks, I believe that helped, but I'm still having a problem with the last
line.

"Jim Thomlinson" wrote:

Without testing it I think you have a problem with this statement...

RefersTo:="=$E$3:TestRng",
should be
RefersTo:="=$E$3:" & TestRng.Address,
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Sorry, That should say "total everything G3 to the end of the data"

"Stephen" wrote:

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))

Simple yet wrong
 

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?
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Thanks, I believe that helped, but I'm still having a problem with the last
line.

"Jim Thomlinson" wrote:

Without testing it I think you have a problem with this statement...

RefersTo:="=$E$3:TestRng",
should be
RefersTo:="=$E$3:" & TestRng.Address,
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Sorry, That should say "total everything G3 to the end of the data"

"Stephen" wrote:

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))

Simple yet wrong
 

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.)

"Jim Thomlinson" wrote:

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?
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Thanks, I believe that helped, but I'm still having a problem with the last
line.

"Jim Thomlinson" wrote:

Without testing it I think you have a problem with this statement...

RefersTo:="=$E$3:TestRng",
should be
RefersTo:="=$E$3:" & TestRng.Address,
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Sorry, That should say "total everything G3 to the end of the data"

"Stephen" wrote:

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))

Simple yet wrong
 

TestRng.Offset(0, 2).Formula = "=Sum(G3:G" & TestRng.row & ")"

I think that should work for you.
--
HTH...

Jim Thomlinson


"Stephen" wrote:

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.)

"Jim Thomlinson" wrote:

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?
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Thanks, I believe that helped, but I'm still having a problem with the last
line.

"Jim Thomlinson" wrote:

Without testing it I think you have a problem with this statement...

RefersTo:="=$E$3:TestRng",
should be
RefersTo:="=$E$3:" & TestRng.Address,
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Sorry, That should say "total everything G3 to the end of the data"

"Stephen" wrote:

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))