-
Jul 14th, 2019, 04:19 AM
#1
Thread Starter
New Member
Last edited by rider@1234; Jul 16th, 2019 at 02:08 PM.
-
Jul 14th, 2019, 07:38 AM
#2
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
-
Jul 14th, 2019, 07:43 AM
#3
Thread Starter
New Member
-
Jul 14th, 2019, 08:00 AM
#4
Thread Starter
New Member
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)
-
Jul 14th, 2019, 04:35 PM
#5
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
-
Jul 14th, 2019, 10:52 PM
#6
Thread Starter
New Member
Re: copy calculate paste
complete the row with symbol and data sir
-
Jul 15th, 2019, 04:36 AM
#7
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
-
Jul 15th, 2019, 12:53 PM
#8
Thread Starter
New Member
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
-
Jul 15th, 2019, 02:58 PM
#9
Thread Starter
New Member
Re: copy calculate paste
object doesn't support this property error is coming
plz have a relook in this code sir
-
Jul 15th, 2019, 03:57 PM
#10
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
-
Jul 15th, 2019, 04:09 PM
#11
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
-
Jul 15th, 2019, 11:36 PM
#12
Thread Starter
New Member
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
-
Jul 16th, 2019, 04:17 AM
#13
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
-
Jul 16th, 2019, 01:29 PM
#14
Thread Starter
New Member
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
-
Jul 16th, 2019, 04:16 PM
#15
Re: copy calculate paste
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
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
-
Jul 17th, 2019, 10:19 AM
#16
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|