copy and paste the data conditionally

[roger@]

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

[Rudi]

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.

[c@1234]

I dont know much
but i will try and post what i learned till today

[roger@]

i am unable to do that

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 how to open a file and rest i dont know
i know only this much
So plz have a look and do needful

[HansV]

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.

[roger@]

sir i have never asked this question sir

[roger@]

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

[HansV]

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.

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

[roger@]

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