Hope this is an easy one...

[NewbieMan]

I have the following...

Range("A3").End(xlDown).Offset(0, 5).Select
ActiveCell.Copy
Range("U3").PasteSpecial

for the table...

[TABLE="width: 736"]
<tbody>[TR]
[TD]Joe Golding (13-16)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Season[/TD]
[TD]School[/TD]
[TD]Conf[/TD]
[TD]G[/TD]
[TD]W[/TD]
[TD]L[/TD]
[TD]W-L%[/TD]
[TD]SRS[/TD]
[TD]SOS[/TD]
[TD]AP Pre[/TD]
[TD]AP High[/TD]
[TD]AP Final[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]2013-14[/TD]
[TD]Abilene Christian[/TD]
[TD]Southland[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.355[/TD]
[TD="align: right"]-19.6[/TD]
[TD="align: right"]-4.12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2014-15[/TD]
[TD]Abilene Christian[/TD]
[TD]Southland[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0.323[/TD]
[TD="align: right"]-17.2[/TD]
[TD="align: right"]-6.34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2015-16[/TD]
[TD]Abilene Christian[/TD]
[TD]Southland[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0.419[/TD]
[TD="align: right"]-13.93[/TD]
[TD="align: right"]-7.53[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2016-17[/TD]
[TD]Abilene Christian[/TD]
[TD]Southland[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0.448[/TD]
[TD="align: right"]-11.86[/TD]
[TD="align: right"]-7.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017-18[/TD]
[TD]Abilene Christian[/TD]
[TD]Southland[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.571[/TD]
[TD="align: right"]-10.85[/TD]
[TD="align: right"]-6.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Career[/TD]
[TD]Abilene Christian[/TD]
[TD][/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]0.395[/TD]
[TD="align: right"]-14.69[/TD]
[TD="align: right"]-6.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My code gets me to the "loss"column which is 5 columns to the right of column A but I am picking up the Career Loss total which is 78 and pasting it into cell U3. I want to sum the entire range beginning beginning at Cell E3 (which is the cell directly under the 'L" in losses down to (and including) the losses for 2016-17 (BUT NOT 2017-18), and then taking that total value and pasting it into Cell U3. The value should be 75. Please note that for different coaches for longer and shorter careers the number of rows would obviously be different, so I am using this as a template but it will apply to others.

Thank you very very much in advance. Sorry for having trouble with this one.

 

[L. Howard]

This may do it for you.

Howard

Option Explicit
Sub Sum_Lcol()
Dim Lcol As Long
Lcol = Cells(Rows.Count, "F").End(xlUp).Row - 2
Range("U3").Value = Application.Sum(Range(Cells(2, 6), Cells(Lcol, 6)))
End Sub

 

[NewbieMan]

This may do it for you.

Howard

Option Explicit
Sub Sum_Lcol()
Dim Lcol As Long
Lcol = Cells(Rows.Count, "F").End(xlUp).Row - 2
Range("U3").Value = Application.Sum(Range(Cells(2, 6), Cells(Lcol, 6)))
End Sub

Thanks Howard however this unfortunately was not quite what I am looking for because although it may work with this specific coach, the problem comes with others who have more rows at the end of their careers so going up from the bottom is not fixed for every coach. I think the only way to avoid that problem is to grab the value from the top and sum down until one reaches the 2016-17 row. I will say that the column is consistent so the Wins and Losses are always in the same columns and the first year of a coach career starts at the same row no matter who the coach is...its the bottom part thats the problem.

Thanks for the effort though. If you have any further thoughts I would appreciate them. Im stuck on this. Very much appreciated.

BL

 

[L. Howard]

The code is supposed to do just what you say you want.

Lets say Coach Joe had twenty years coaching, His first year would be in row 2, then the 2017-18 season would be in row 21 and Career would be in row 22.
The code will sum the losses from F2 down to F20, is that correct?
I put a 1 in F2 and incremented it down to row 20 (for ease testing) so we are adding the numbers from 1 to 19, the total for F2 to F20 should be 190.

Now you can select and delete, say three or four years (rows) somewhere above the 2017-18 season, and move the two bottom rows up to get rid of the blank rows. Then run the code and check the total losses

See if that is the way it works on your sheet.

Howard

 

[NewbieMan]

hi Howard,

Thanks for the reply and explanation...here is an example of where it breaks down for me...

[TABLE="width: 635"]
<colgroup><col><col><col><col span="2"><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]2013-14[/TD]
[TD]Duke[/TD]
[TD]ACC[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0.743[/TD]
[TD="align: right"]19.51[/TD]
[TD="align: right"]8.48[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]2014-15[/TD]
[TD]Duke[/TD]
[TD]ACC[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.897[/TD]
[TD="align: right"]24.97[/TD]
[TD="align: right"]9.87[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]2015-16[/TD]
[TD]Duke[/TD]
[TD]ACC[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0.694[/TD]
[TD="align: right"]19.23[/TD]
[TD="align: right"]10.59[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]2016-17[/TD]
[TD]Duke[/TD]
[TD]ACC[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0.757[/TD]
[TD="align: right"]21.56[/TD]
[TD="align: right"]10.91[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]2017-18[/TD]
[TD]Duke[/TD]
[TD]ACC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21.2[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Career[/TD]
[TD]Overall[/TD]
[TD][/TD]
[TD="align: right"]1410[/TD]
[TD="align: right"]1080[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]0.766[/TD]
[TD="align: right"]18.19[/TD]
[TD="align: right"]7.68[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Army[/TD]
[TD][/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]0.553[/TD]
[TD="align: right"]-3.41[/TD]
[TD="align: right"]-3.04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Duke[/TD]
[TD][/TD]
[TD="align: right"]1278[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]271[/TD]
[TD="align: right"]0.788[/TD]
[TD="align: right"]21.03[/TD]
[TD="align: right"]9.09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What is being captured is for example 2160 Wins which is from the Wins column but what its adding is the 1080, 73 and 1007 = 2160 which is what is being returned. What I was looking for is cell values added summed from E3 (which is not visible) down to the 28 wins for 2016-17 with the result being populated in T3 for the wins. For the losses which was my original question the only difference would be populating it in sell U3. So for the coaches without a long history, it does certainly capture the correct info. For the veteran coaches who have additional cell under the career row its unfortunately not capturing the info.

Thanks again, and I sincerely appreciate the time your taking out with respect to this. Its much appreciated

 

[L. Howard]

Can you post an exact example of data like Coach Joe and an exact example of a Coach Old Timer.
And where are these data stats located on a sheet in relation to each other. Are they on separate sheets or all on one sheet? What columns, rows, etc., what sheet/s?

Maybe a link to an example workbook would be in order. You can use one of the link utilities like Drop Box to do that. You cannot post an attachment here.

Howard

 

[L. Howard]

Here is something to try.

In the code change the "F" to "A" as shown here. Then give that a try.

Lcol = Cells(Rows.Count, "[COLOR=#ff0000]A[/COLOR]").End(xlUp).Row - 2

Not sure it will work because I don't know the layout of the sheet/s. Worth a try though.

Otherwise back to Post #6 .

Howard

 

[NewbieMan]

Here is something to try.

In the code change the "F" to "A" as shown here. Then give that a try.

Lcol = Cells(Rows.Count, "[COLOR=#ff0000]A[/COLOR]").End(xlUp).Row - 2

Not sure it will work because I don't know the layout of the sheet/s. Worth a try though.

Otherwise back to Post #6 .

Howard

Mr Howard,

This last post did the trick. Changing the value to "A" solved the issue for all the coaches I tried. You are a gentleman for sticking with this. Sorry if the posts were not as thorough, I will make note of it for future. I really appreciate taking the time you sacrificed to help me solve this. Again a big thanks. Issue solved.

 

[L. Howard]

Okay, great. Glad it works for you.

You will need to know that there can be NO data in column A below the row holding Career. Otherwise the variable Lcol will return an incorrect row number needed to correctly adjust the sum range of column F.

Howard