+ Reply to Thread
Results 1 to 5 of 5

Ranges in Macro

  1. #1
    Registered User
    Join Date
    01-23-2005
    Posts
    40

    Ranges in Macro

    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

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by 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
    Please Login or Register  to view this content.
    should be useful
    --

  3. #3
    Registered User
    Join Date
    01-23-2005
    Posts
    40
    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

  4. #4
    Forum Contributor stevebriz's Avatar
    Join Date
    09-07-2006
    Location
    Santiago Chile
    Posts
    389
    It sums the values form the cell below your active cell to to the last used row of column A.
    VBA - The Power Behind the Grid

    Posting a sample of your workbook makes it easier to look at the Issue.

  5. #5
    Registered User
    Join Date
    01-23-2005
    Posts
    40
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1