Using a macro to add a count and sum on a dynamic range
 

Hi all,
I am an macro / vba extreme newbie so please don't laugh. We receive a
comma delimited txt file and I am writing a macro to take care of a log of
the generic formatting issues we use on every file to get it ready for use.
As it stands right now I have a start date in column H and an end date in I
and the macro is already subtracting the two to give me a length in column J.
What I am trying to do is add a sum and a count after the last row in column
J. The problem is that every file will have a different number of records.

Following is the tail end section of my code, like I said earlier, I am very
new at this so please don't laugh. Thank you in advance for your help.

ActiveCell.FormulaR1C1 = "LOS"
Range("J6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("J6").Select
Selection.NumberFormat = "General"
Selection.Copy
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("j6:j" & lr).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Using a macro to add a count and sum on a dynamic range
 

On Mar 12, 3:41 pm, Surrealdogma
wrote:
Hi all,
I am an macro / vba extreme newbie so please don't laugh. We receive a
comma delimited txt file and I am writing a macro to take care of a log of
the generic formatting issues we use on every file to get it ready for use.
As it stands right now I have a start date in column H and an end date in I
and the macro is already subtracting the two to give me a length in column J.
What I am trying to do is add a sum and a count after the last row in column
J. The problem is that every file will have a different number of records.

Following is the tail end section of my code, like I said earlier, I am very
new at this so please don't laugh. Thank you in advance for your help.

ActiveCell.FormulaR1C1 = "LOS"
Range("J6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("J6").Select
Selection.NumberFormat = "General"
Selection.Copy
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("j6:j" & lr).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Here are a couple of general use functions I have that should help
you.
I'm not clear on your problem exactly. These 2 functions should be
self explanatory .
Put these in a module and pass in the VBA (name).
If this was not your problem, state the problem a little more clearly.
I should be able to help.

Your statement: "The problem is that every file will have a different
number of records."

Implies you don't know what the last row is?

Function findLastColumn(ws As Worksheet) As Long
Dim lastColumn As Long
lastColumn = 0

Dim autoFilterToggled As Boolean
autoFilterToggled = False

' If autofilter is on, Turn off, and turn it back on after done.
If (ws.AutoFilterMode) Then
ws.UsedRange.AutoFilter
autoFilterToggled = True
End If

'Unhide all columns, because Find Function fails if columns are hidden
ws.UsedRange.EntireColumn.Hidden = False

If WorksheetFunction.CountA(ws.Cells) 0 Then
'Search for any entry, by searching backwards by Columns.
lastColumn = ws.Cells.Find(what:="*", After:=Range("A1"),
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).column
End If

If (autoFilterToggled) Then
ws.UsedRange.AutoFilter
End If

findLastColumn = lastColumn
End Function

Function findLastRow(ws As Worksheet)
Dim lastRow As Long
lastRow = 0

Dim autoFilterToggled As Boolean
autoFilterToggled = False

' If autofilter is on, Turn off, and turn it back on after done.
If (ws.AutoFilterMode) Then
ws.UsedRange.AutoFilter
autoFilterToggled = True
End If

'Unhide all columns, because Find Function fails if columns are hidden
ws.UsedRange.EntireColumn.Hidden = False

If WorksheetFunction.CountA(ws.Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
lastRow = ws.Cells.Find(what:="*", After:=Range("A1"),
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).row
End If

If (autoFilterToggled) Then
ws.UsedRange.AutoFilter
End If

findLastRow = lastRow
End Function

Using a macro to add a count and sum on a dynamic range
 

I want to run this macro to to take care of a lot of the generic formatting
issues we use on every file to get it ready for use. As it stands right now I
have a start date in column H and an end date in I and the macro is already
subtracting the two to give me a length in column J. What I am trying to do
is add a sum and a count after the last row in column J. The problem is that
every file will have a different number of records.

Let's say we have 2 files, file 1 and file 2. File 1 has 900 rows and file
2 has 30,000 rows. All the data in these files start on row 6 (1:5 are
headers and other info) Ideally in File one this macro would enter
"=sum(J6:J900)" in cell J901 but would enter "=sum(J6:J30000)" cell J30,001
in file 2.

I hope that clears up what I am trying to do. Secondly, and I apologize
before hand but I could not get your code to work

SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).column

Was showing up in red and displaying an error message. I am assuming that I
either should be replacing something in your code but I am not sure what or
that I am entering it in the wrong section.

Thank you again for your help and patience

" wrote:

On Mar 12, 3:41 pm, Surrealdogma
wrote:
Hi all,
I am an macro / vba extreme newbie so please don't laugh. We receive a
comma delimited txt file and I am writing a macro to take care of a log of
the generic formatting issues we use on every file to get it ready for use.
As it stands right now I have a start date in column H and an end date in I
and the macro is already subtracting the two to give me a length in column J.
What I am trying to do is add a sum and a count after the last row in column
J. The problem is that every file will have a different number of records.

Following is the tail end section of my code, like I said earlier, I am very
new at this so please don't laugh. Thank you in advance for your help.

ActiveCell.FormulaR1C1 = "LOS"
Range("J6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("J6").Select
Selection.NumberFormat = "General"
Selection.Copy
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("j6:j" & lr).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Here are a couple of general use functions I have that should help
you.
I'm not clear on your problem exactly. These 2 functions should be
self explanatory .
Put these in a module and pass in the VBA (name).
If this was not your problem, state the problem a little more clearly.
I should be able to help.

Your statement: "The problem is that every file will have a different
number of records."

Implies you don't know what the last row is?

Function findLastColumn(ws As Worksheet) As Long
Dim lastColumn As Long
lastColumn = 0

Dim autoFilterToggled As Boolean
autoFilterToggled = False

' If autofilter is on, Turn off, and turn it back on after done.
If (ws.AutoFilterMode) Then
ws.UsedRange.AutoFilter
autoFilterToggled = True
End If

'Unhide all columns, because Find Function fails if columns are hidden
ws.UsedRange.EntireColumn.Hidden = False

If WorksheetFunction.CountA(ws.Cells) 0 Then
'Search for any entry, by searching backwards by Columns.
lastColumn = ws.Cells.Find(what:="*", After:=Range("A1"),
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).column
End If

If (autoFilterToggled) Then
ws.UsedRange.AutoFilter
End If

findLastColumn = lastColumn
End Function

Function findLastRow(ws As Worksheet)
Dim lastRow As Long
lastRow = 0

Dim autoFilterToggled As Boolean
autoFilterToggled = False

' If autofilter is on, Turn off, and turn it back on after done.
If (ws.AutoFilterMode) Then
ws.UsedRange.AutoFilter
autoFilterToggled = True
End If

'Unhide all columns, because Find Function fails if columns are hidden
ws.UsedRange.EntireColumn.Hidden = False

If WorksheetFunction.CountA(ws.Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
lastRow = ws.Cells.Find(what:="*", After:=Range("A1"),
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).row
End If

If (autoFilterToggled) Then
ws.UsedRange.AutoFilter
End If

findLastRow = lastRow
End Function