vba is placed in different files
all files are located in same path
files are not opened
if column B of sample.xls matches with column C of sample.csv and column J of sample.csv contains ABC and column L of sample.csv is empty then add 0.05 to column E of sample.xls and paste the total amount to coulmn L of sample.csv
And
if column B of sample.xls matches with column C of sample.csv and column J of sample.csv contains XYZ and column L of sample.csv is empty then subtract 0.05 to column F of sample.xls and paste the total amount to coulmn L of sample.csv
all this i have to do by vba so plz have a look and do needful
save and close all the files
copy and paste the data conditionally
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: copy and paste the data conditionally
One of the purposes of this forum is to help one to learn to help themselves. It will not help you to learn for yourself if you just continually post instructions for members here to do all the coding. It would be rewarding for us to see that you are trying by posting some attempts at coding something to start with and then asking us for assistance with the code than to just give us instructions and let us build the whole solution for you. How about starting with a recorded macro, study up the code and doing some cleanup on it, then, when you get stuck, post a question and we can provide some guidance. I'm sure that you have learned some things already by the amount of coding that has already been provided to you in your previous posts. How about putting some of that knowledge to practice and we can guide you from there.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 25
- Joined: 30 May 2019, 18:59
Re: copy and paste the data conditionally
I dont know much
but i will try and post what i learned till today
but i will try and post what i learned till today
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: copy and paste the data conditionally
i am unable to do that
i know how to open a file and rest i dont know
i know only this much
So plz have a look and do needful
Code: Select all
Sub abc()
Dim wsh As Worksheet
Set wsh = Workbooks.Open(ThisWorkbook.Path & "\Sample.xls").Worksheets(1)
End If
End Sub
i know only this much
So plz have a look and do needful
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: copy and paste the data conditionally
I would recommend that you get a professional Excel developer to write the code for you. That will be much more efficient than asking more or less the same question over and over again.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: copy and paste the data conditionally
sir i have never asked this question sir
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: copy and paste the data conditionally
Sir i have tried everywhere i saw many examples variations not 100% same as my question but i was unsuccessful in making the code so it is my request plz consider this question and have a look and do needful
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: copy and paste the data conditionally
Hi leonardo1234/umbug/roger@/c@1234,
I will give you a macro this time. But as mentioned before in this and other threads, it is not the purpose of a forum like this to do that all the time, especially if you aren't willing or able to learn anything from the replies that you receive.
I have commented the code extensively.
Warning: since I haven't seen your files, I cannot guarantee that the macro does what you want.
I will give you a macro this time. But as mentioned before in this and other threads, it is not the purpose of a forum like this to do that all the time, especially if you aren't willing or able to learn anything from the replies that you receive.
I have commented the code extensively.
Warning: since I haven't seen your files, I cannot guarantee that the macro does what you want.
Code: Select all
Sub CopyData()
' Declarations
Dim wbk1 As Workbook
Dim wsh1 As Worksheet
Dim lngLast1 As Long
Dim rng1 As Range
Dim wbk2 As Workbook
Dim wsh2 As Worksheet
Dim lngLast2 As Long
Dim r2 As Long
' Speed up execution by not showing what we do
Application.ScreenUpdating = False
' Open Sample.xls
Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\Sample.xls")
Set wsh1 = wbk1.Worksheets(1)
' Last used row
lngLast1 = wsh1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
' Open Sample.csv
Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\Sample.csv")
Set wsh2 = wbk2.Worksheets(1)
' Last used row
lngLast2 = wsh2.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
' Loop through the cells of column C of the CSV file
For r2 = 2 To lngLast2
' Is column L blank?
If wsh2.Range("L" & r2).Value = "" Then
' Search for cell value in column B of the XLS workbook
Set rng1 = wsh1.Range("B:B").Find(What:=wsh2.Range("C" & r2).Value, LookAt:=xlWhole)
' Do we have a match?
If Not rng1 Is Nothing Then
Select Case wsh2.Range("J" & r2).Value
Case "ABC"
wsh2.Range("L" & r2).Value = rng1.Offset(0, 3) + 0.05
Case "XYZ"
wsh2.Range("L" & r2).Value = rng1.Offset(0, 4) - 0.05
End Select
End If
End If
Next r2
' Close Sample.xls without saving it
wbk1.Close SaveChanges:=False
' Close AND save Sample.csv
Application.DisplayAlerts = False
wbk2.Close SaveChanges:=True
Application.DisplayAlerts = True
' Turn on screen updating
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: copy and paste the data conditionally
Thnx alot HansV Sir for giving ur precious time and great great support to this post
One of best of best vba forum and one of best best of best Programmers
Hats off to all the eilleens lounge team for giving the support and guiding me
Rudi Sir thnx
One of best of best vba forum and one of best best of best Programmers
Hats off to all the eilleens lounge team for giving the support and guiding me
Rudi Sir thnx