copy paste by vba

[Legacy 450108]

If column E matches with column C and column M has a blank cell then ignore that


If column E of sample1.xls matches with column C of sample2.csv and column M of sample1.xls contains (-) minus sign example(-1.00,-2.00 etc) then see column O of sample1.xls and calculate the 1% of column O of sample1.xls and add the 1% of column O of sample1.xls with itself and paste the result to column L of sample2.csv


Or


If column E of sample1.xls matches with column C of sample2.csv and column M of sample1.xls doesnt contains (-) minus sign example(1.00,2.00 etc) then see column P of sample1.xls calculate the 1% of column P of sample1.xls and subtract 1% of column P of sample1.xls with itself and paste the result to column L of sample2.csv
save and colse all the workbooks
i have to do the same by vba
sample1.xls contains headers in the first row so ignore first row
sample2.csv doesnt contains any headers
vba is placed in a seperate file
all files are located in same place
only 1 file is opened vba code placed file so we have to open the the sample1.xls file and sample2.csv file and then we have to do the process

 

[JLGWhiz]

Check the file names to be sure they are correct before running this code. It is untested.

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, fPath As String
fPath = ThisWorkbook.Path & "\"
Set wb1 = Workbooks.Open(fPath & "sample1.xls") 'Verify file name
Set wb2 = Workbooks.Open(fPath & "sample2.csv") 'Verify file name
Set sh1 = wb1.Sheets(1)
Set sh2 = wb2.Sheets(1)
    For Each c In sh1.Range("E2", sh1.Cells(Rows.Count, 5).End(xlUp))
        If c.Offset(, 8) <> "" Then
        Set fn = sh2.Range("C1", sh2.Cells(Rows.Count, 3).End(xlUp)).Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If c.Offset(, 8) < 0 Then
                    fn.Offset(, 9) = c.Offset(, 10).Value + (c.Offset(, 10) * 0.01)
                ElseIf c.Offset(, 8) > 0 Then
                    fn.Offset(, 9) = c.Offset(, 11).Value - (c.Offset(, 11) * 0.01)
                End If
            End If
        End If
    Next
    wb1.Close True
    wb2.Close True
End Sub

 

[Legacy 450108]

Thnx Jlgwhiz Sir thnx for the support but i am getting error while runing the code
I tried to edit the code and make it workable but i was unsuccessful so plz have a relook in the vba code

 

[JLGWhiz]

What is the error message and what line of code is highlighted when you click the debug button? Please be specific when describing your issues. Remember that we cannot see your worksheet so you must give accurate details in your posts.

 

[Legacy 450108]

application defined or object defined error
while debugging
For Each c In sh1.Range("E2", sh1.Cells(rows.Count, 3).End(xlUp))
this line is highlighted

 

[JLGWhiz]

application defined or object defined error
while debugging
For Each c In sh1.Range("E2", sh1.Cells(rows.Count, 3).End(xlUp))
this line is highlighted

If you look at Post #2 you will see that the 3 should be a 5. I don't know how that would get changed if you copied the code and pasted it into the code module. If you are retyping the code then it is subject to typo errors. Just change the 3 to a 5 and it should work.

 

[Legacy 450108]

yes sir it was 5 but when i ran that code i am getting error so i tried editing the code but i was unsuccessful
so plz recheck the code i have putted the same code which u sent and i am getting error


Error - application defined or object defined error

 

[JLGWhiz]

I don't believe I can do any more without seeing the workbooks, but I will try to set up a simulation and run the code. If I find anything that needs to change I will post back here.

 

[JLGWhiz]

I set up a simulation with two files having data in the columns as specified in Post #1 and the code being run from a third file. No errors occured during the test so I cannot resolve the problem you are having with it. I have no other suggestions to offer.
Regards, JLG

 

[Legacy 450108]

I am not as knowledgeful like u
but this code will not run(will not work) as per ur code
plz recheck this cods sir