| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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)) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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)) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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)) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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)) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple multiplication of 2 cells = wrong product | Excel Discussion (Misc queries) | |||
| What is wrong with this simple formula? | Excel Discussion (Misc queries) | |||
| Simple SUMIF formula gone wrong! | Excel Worksheet Functions | |||
| Simple VBA - What's wrong?? | Excel Programming | |||
| Help! What's wrong with this simple copy and paste | Excel Programming | |||
Hybrid Mode
