Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: COPYING A FORMULA IN CELL

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When i copy a formula in varying length of report it copies it till the end if i highlight a column and paste the formula in it. for eg i am concatenating column a & b and the i copy =CONCATENATE($A1,$B1) then i highlight column d and cut and paste it then it copies till the end ie 65000+ rows. problem is at the time of set print it thinks that there are so many rows now how to get rid of this problem and i am using macro

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The message might not be clear so posting again
    1. I am trying to copy concatenate formula ie concatenation of col a and b
    2. after i copied the formula i select the col so lets say i am trying to paste it in d
    so i select d:d at the top and say paste
    3. now it paste till the end of sheet ie 65000 rows + i want it to paste only till where there is data. i am doing d:d select because my report is going to be of varying length depending on the data sent into excel.

    Any thoughts??
    Thanks

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please help me on this
    Thanks

  4. #4
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Copy the formula you wish to paste.

    Press control + G for the goto box.

    Type in the range you want to paste to (for example, if your rows go to 500, type d1:d500) and it will select that range. Then paste your code.

    Hope this helps,
    Kristy

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    but the problem is i dont know the range it could be varying depending on the query results. i am directing the query results into excel and then formatting it using macro

  6. #6
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ugh. I missed that part. I unfortunately can't do macros, sorry (wish I could, though).

    I'm assuming you may have to add something to your existing macro to do that, though.
    Kristy

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone help me on this?

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    any help will be apreciated

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Guest

    Try this code. It gives a hard value rather than a formula, but it can be changed to a formula if you wish.

    For Each cell In [A:A]
    If cell.Value <> "" Then
    cell.Offset(0, 3).Value = cell.Value & cell.Offset(0, 1).Value
    End If
    Next
    End Sub

    If you want to use the concatenate formula use this code. The difference is that the above macro will make a number 1525 by joining 15 and 25 but the concatenate code below gives 1525 as text

    For Each cell In [A:A]
    If cell.Value <> "" Then
    cell.Offset(0, 3).Formula = "=CONCATENATE(RC[-3],RC[-2])"
    End If
    Next
    End Sub

    Note that the 3 in the first offset statement refers to column D (3 columns to the right of A), change this number to the number of your destination column.


    Hope this helps
    regards
    Derek


    [ This Message was edited by: Derek on 2002-04-09 17:59 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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