Results 1 to 4 of 4

Thread: Copy

  1. #1
    Join Date
    Sep 2005
    Posts
    313

    Unanswered: Copy

    Hi,

    .Worksheets(1).Cells(10, 1) = 2
    .Worksheets(1).Cells(11, 1) = 3
    .Worksheets(1).Cells(12, 1) = "=sum(" & .Worksheets(1).Cells(10, 1).Address & ":" & .Worksheets(1).Cells(11, 1).Address & ")"


    .Worksheets(1).Cells(10, 2) = 1
    .Worksheets(1).Cells(11, 2) = 6
    .Worksheets(1).Cells(12, 1).Copy
    .Worksheets(1).Cells(12, 2).PasteSpecial xlPasteFormats

    It cannot copy the format to there because it has the dollor sign, =Sum($A$15:$A$16)

    Which command code can represent =Sum(A15:A16)?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Are you sure you are trying to paste formats and not formulas? Assuming the latter this little bodge seems to do the trick:

    Code:
            .Worksheets(1).Cells(10, 1).Value = 2
            .Worksheets(1).Cells(11, 1).Value = 3
            .Worksheets(1).Cells(12, 1).Formula = Replace("=sum(" & .Worksheets(1).Cells(10, 1).Address & ":" & .Worksheets(1).Cells(11, 1).Address & ")", "$", "")
            
            .Worksheets(1).Cells(10, 2).Value = 1
            .Worksheets(1).Cells(11, 2).Value = 6
            .Worksheets(1).Cells(12, 1).Copy
            .Worksheets(1).Cells(12, 2).PasteSpecial xlPasteFormulas
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just a quick note, to enter relational formulas yopu need to use R1C1 notation (I dont know of any other way ???), so this

    .Worksheets(1).Cells(12, 1).Formula = Replace("=sum(" & .Worksheets(1).Cells(10, 1).Address & ":" & .Worksheets(1).Cells(11, 1).Address & ")", "$", "")

    is replaced with this

    .Worksheets(1).Cells(12, 1) = "=sum(R[-2]C[0]:R[-1]C[0])"

    If you use R1C1 without the square bracket it returns absolute addessing. The squre brackets contain the relational offset from the cell the formular is being entered into ie R[-2] refers to the row two above the formula cell. Variables can be concatenated into the string if necessary.

    This will allow relational copy and paste.

    However, I think pootle's 'Replace' is probable the simpler option (as you don't have to work out any offsets- one less thing to go wrong!), which I will bear in mind for future reference (if senility doesn't get me first).

    (Shouldn't this be in excel?)

    MTB

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MikeTheBike
    to enter relational formulas yopu need to use R1C1 notation
    I did not know that (as you could tell). I actually came across it the other day but could not get my feeble brain to figure out what it meant. Nice
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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