Taking the 51st Line and Multiplying by 2

[childofthe1980s]

Hello:

Below, I have a macro in VBA that takes rows of data and averages them.

Columns C and E contain the averages.

I need to add programming that takes every row of averages from the 51st row
to the last row of data and multiplies them by 2, long story short.

Keep in mind that the spreadsheet that results from the programming below
does not contain contiguous row numbers. The first row of data, as of this
moment, is "5". The next row is "9", then "13", then "17", "21", "25". Last
month, the first row number was "4", then "7", then "12", .....

So, there is no consistency with the row numbers. That's what makes
throwing in a calculation of "times 2" for the 51st row onward complicated.

Anyway, what programming do I add to tell Excel to take the 51st and greater
rows of data in the spreadsheet and multiply the values in the records of
columns C and E by 2?

Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(3,
5) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Selection.Sort Key1:=Range("E2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("C:C").Select
Selection.NumberFormat = "0"
Columns("E:E").Select
Selection.NumberFormat = "0"
Columns("A:A").ColumnWidth = 17.86
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select

childofthe1980s

 

[Benjamin]

Will something like this work, it pastes the answer to columns G & H?




Option Explicit
'Code by Benjamin

Sub Average_X_2()

Dim Count As String
Dim lastrow As Integer

Dim CurrentValueofC As String
Dim CurrentValueofE As String





Count = "51" 'Start at Row 51
lastrow = ActiveWorkbook.Sheets(1).Cells(65536, 3).End(xlUp).Row
MsgBox lastrow


Do Until Count > lastrow
CurrentValueofC = ThisWorkbook.Sheets(1).Cells(Count, 3).Value
'Count, 7 'Change the * to the Column you
want to paste to a=1, b=2, etc
ThisWorkbook.Sheets(1).Cells(Count, 7).Value = CurrentValueofC * 2


CurrentValueofE = ThisWorkbook.Sheets(1).Cells(Count, 5)
'Count, 8 'Change the * to the Column
you want to paste to a=1, b=2, etc
ThisWorkbook.Sheets(1).Cells(Count, 8).Value = CurrentValueofE * 2

Count = Count + 1
Loop

End Sub

 

[childofthe1980s]

I tried this.....but, I did not see any results pasted into G & H.

 

[childofthe1980s]

Oh, and I want the first 51 rows to remain as they are. This would not be
"new" programming but "additional" programming.

childofthe1980s

 

[childofthe1980s]

And, I should be more specific when I say "row 51". I do not mean row number
51. I mean the 51st line in the spreadsheet. This could, for all we know,
be row number 399 for example.

 

[childofthe1980s]

Actually, you can disregard my first and second reply here. I was able to
get your programming to work.

But, that one issue on the row numbers remains.

I do not mean row number 51. I mean the 51st line in the spreadsheet. This
could, for all we know, be row number 399 for example.

Thanks, again, Benjamin!

 

[childofthe1980s]

I have this "project" almost done except for this issue with the macro not
finding the "true" 51st row. (Oh, and this needs to exclude the 1st row that
has column headings.)

Anyway, below is my revised code that I have worked on this afternoon based
on what you gave me. (And, I THANK YOU very much for this!!!)

I have taken the "times 2" data and placed them in columns G and H.

You will see at the end that I am having Excel "guess" what the 51st row is.
I am referring to two of the last lines of my code, as follows:
Range("G5:H184").Select
Selection.ClearContents

I would like to see if there is a way for Excel to be more exact in terms of
finding the 51st row of data. Of course, since this is average and
summarized data, like I said, this maybe the best that we can hope for.

Here's the code:

Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(3, 5) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Selection.Sort Key1:=Range("E2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("C:C").Select
Selection.NumberFormat = "0"
Columns("E:E").Select
Selection.NumberFormat = "0"
Columns("A:A").ColumnWidth = 17.86
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select


Dim Count As String
Dim lastrow As Integer

Dim CurrentValueofC As String
Dim CurrentValueofE As String


Count = "52" 'Start at Row 52
lastrow = ActiveWorkbook.Sheets(1).Cells(65536, 3).End(xlUp).Row
'MsgBox lastrow


Do Until Count > lastrow
CurrentValueofC = ThisWorkbook.Sheets(1).Cells(Count, 3).Value
'Count, 7 'Change the * to the Column you want to paste to a=1, b=2, etc
ThisWorkbook.Sheets(1).Cells(Count, 7).Value = CurrentValueofC * 2


CurrentValueofE = ThisWorkbook.Sheets(1).Cells(Count, 5)
'Count, 8 'Change the * to the Column you want to paste to a=1, b=2, etc
ThisWorkbook.Sheets(1).Cells(Count, 8).Value = CurrentValueofE * 2

Count = Count + 1
Loop

Columns("G:G").Select
Selection.NumberFormat = "0"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Biweekly Average"
Columns("G:G").ColumnWidth = 17.86
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").Select
Selection.NumberFormat = "0"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Biweekly Difference"
Columns("H:H").ColumnWidth = 17.86
Columns("H:H").EntireColumn.AutoFit
Range("G5:H184").Select
Selection.ClearContents
ActiveWindow.LargeScroll Down:=0
Range("A1").Select
End Sub

 

[Patrick Molloy]

you can tidy up - and make it more legible - in this I just doubled A and E
for cell > row 52
You do not need to 'select' a cell or column/row to use it.
Option Explicit
Sub Average_X_2()
Dim Count As Long
Dim lastrow As Long
Count = 51 ' Start at Row 51
lastrow = ActiveWorkbook.Sheets(1).Cells(65536, 3).End(xlUp).Row
Do Until Count > lastrow
WITH ThisWorkbook.Sheets(1).Cells(Count, "C")
.Value = 2* .Value
END WITH
WITH ThisWorkbook.Sheets(1).Cells(Count, "E")
.Value = 2 * .Value
END WITH
Count = Count + 1
Loop

Columns("A:G").EntireColumn.AutoFit
Columns("A:G").NumberFormat = "0"

Range("A1") = "Biweekly Average"

Range("E1") = "Biweekly Difference"

End Sub


I'm not sure what you mean about the "exact" 51st row.
do you mean instead of this
Count = 51 ' Start at Row 51
some thing like
rw = 0
do until count>= 51
rw=rw+1
if cells(rw,"A")<>"" then count = count+1
loop
Count = rw ' reset count as its used downstream

 

[Benjamin]

Did you get it figured out? Hope it finally started working right for you.
-BC