Results 1 to 16 of 16

Thread: copy calculate paste

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    12

    copy calculate paste

    1111111111111111111
    Last edited by rider@1234; Jul 16th, 2019 at 02:08 PM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: copy calculate paste

    to clarify
    you want to loop all values in column E of first workbook
    try to find matching values in pl.xlsx
    all other columns referred to are in the first or second workbook?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    12

    Re: copy calculate paste

    yes u r right sir

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    12

    Re: copy calculate paste

    and one more thing first empty row means paste the data from column C ignore B column(dont delete B column just ignore B column and paste the data from Column C)

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: copy calculate paste

    and one more thing first empty row means paste the data from column C ignore B
    so next empty row is below existing data, or you want to complete the row with symbol and data from ap.xls into column C?

    the data is just the calculation result?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    12

    Re: copy calculate paste

    complete the row with symbol and data sir

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: copy calculate paste

    you can test this to see if it does what you want, if not i have misunderstood something
    Code:
    Set pl = Workbooks.Open(ThisWorkbook.Path & "\pl.xlsx").Sheets(1)
    Set ap = Workbooks.Open(ThisWorkbook.Path & "\ap.xls").Sheets(1)
    rw = 2
    lr = pl.Cells(pl.Rows.Count, 1).End(xlUp).Row
    Do Until rw = lr
        Set fnd = ap.Range("E:E").Find(pl.Cells(rw, 1))
        If Not fnd Is Nothing Then
        sp = fnd.Offset(, 10)
        If fnd.Offset(, 11) > sp Then sp = fnd.Offset(, 11)
          pl.Cells(rw, 3) = Abs(sp * 1.05 * fnd.Offset(, 7) + fnd.ofset(, 13))
        
        
        Else
    '    MsgBox pl.Cells(rw, 1) & " not found"
        End If
        rw = rw + 1
    Loop
    pl.Parent.Close True  ' save changes
    ap.Parent.Close False   ' nothing changed, don't save
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    12

    Re: copy calculate paste

    Thnx Sir
    but i am a beginner so plz provide me the complete code
    Sub
    code
    End Sub
    like this sir

  9. #9

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    12

    Re: copy calculate paste

    object doesn't support this property error is coming
    plz have a relook in this code sir

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: copy calculate paste

    object doesn't support
    which line?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: copy calculate paste

    pl.Cells(rw, 3) = Abs(sp * 1.05 * fnd.Offset(, 7) + fnd.ofset(, 13))
    typo on this line
    Code:
    pl.Cells(rw, 3) = Abs(sp * 1.05 * fnd.Offset(, 7) + fnd.Offset(, 13))
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    12

    Re: copy calculate paste

    Thnx Sir but still this code is not complete
    plz see the problem in the first post of this thread
    there are two conditions it is greater then and it is not greater then so plz relook sir

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: copy calculate paste

    there are two conditions it is greater then and it is not greater then so plz relook
    that should already been taken care of by
    sp = fnd.Offset(, 10)
    If fnd.Offset(, 11) > sp Then sp = fnd.Offset(, 11)
    i will check

    on testing, the higher value is always returned, as requested
    Last edited by westconn1; Jul 16th, 2019 at 04:24 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    12

    Re: copy calculate paste

    Plz see my first post this code is not working according to my post
    plz see my sample file
    Sir this code is not complete
    plz use dim in the code

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: copy calculate paste

    plz use dim in the code
    you can do that, just put in front of the other code, if you can not work out what type of variable to use make them all variant

    Plz see my first post this code is not working according to my post
    as this has now been removed, difficult to do

    plz see my sample file
    i was using your sample files for testing

    this code is not complete
    i do not provide a free code writing service, i provide code samples, suggestions and other assistance to help you, and others, to learn to write your own code

    you are doing this for your work or similar , so you are being pad for your results, so put some more effort in

    if you are getting the wrong results, post an additional workbook that shows the correct results for the original sample workbooks
    as indicated by the questions i posted earlier, i was having a problem understanding your desired result, so i posted code to do what i interpreted as your requirement
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: copy calculate paste

    Nobody is that much of a beginner. If you can't use the example posted in #7 and repeatedly ask for the entire sub to be written for you, then I'd have to say that you aren't interested in learning. The fact that you deleted the original post makes me think that you are simply trying to cheat. I was tempted to restore the original post, but didn't want to restore the links. Better to just close this. I'm not sure what your goal is, but you would be well advised to rethink what you are doing.
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width