Ranges in Macro

[annep]

I exported a check register from Quickbooks to excel and I am trying to automate converting it into an ASCII text file.
Needless to say, the number of checks will never be the same, so I have to write this macro with relative addresses.
I have every column formatted correctly. The data right now is in A2 to E194 with row 1 emply. I have to create a header column in row 1 with the total amount of the checks as well as an item count. The auto macro created the following sum in c2

Sub Macro8()
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[193]C)"
End Sub

I actually did Sum (End Down).
But I can see that the macro is referencing 193 lines.
I need this function to be completely flexible.
Help,
Anne

[Bryan Hessey]

I exported a check register from Quickbooks to excel and I am trying to automate converting it into an ASCII text file.
Needless to say, the number of checks will never be the same, so I have to write this macro with relative addresses.
I have every column formatted correctly. The data right now is in A2 to E194 with row 1 emply. I have to create a header column in row 1 with the total amount of the checks as well as an item count. The auto macro created the following sum in c2

Sub Macro8()
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[193]C)"
End Sub

I actually did Sum (End Down).
But I can see that the macro is referencing 193 lines.
I need this function to be completely flexible.
Help,
Anne

Please Login or Register  to view this content.

should be useful
--

[annep]

I understand the last row part. Can you explain to me what the formular does.
I don't seem to find any explanation;

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow - 1
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[" & lastrow & "]C)"

I understand with the first 2 lines the last row is determined.
ActiveCell is the current position

"=SUM(R[1]C:R[" & lastrow & "]C)"

What are the meaning of the letters in the "Sum="
I would like to understand, so I can reuse the info.
Thanks,
Anne

[stevebriz]

It sums the values form the cell below your active cell to to the last used row of column A.

[annep]

Another problem
I was hoping I could do this by myself. I inserted a column next to the amount and I need to determine if the check was voided.

Range("E3").Select
ActiveCell.FormulaR1C1 = "=IF(""d3""=0,""V"",""N"")"
filld

Using this routine to copy it down.
Sub filld()
'Simulate Ctrl+D (fill down), D.McRitchie 2005-06-14 programming
' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown
End Sub

When it copies it down, it retains the D3, but the D3 needs to be relative. How do I change the D3 to be relative?
Anne