copy paste by vba

L

Legacy 450108

Guest
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,052
Office Version
2013
Platform
Windows
Check the file names to be sure they are correct before running this code. It is untested.

Code:
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
 
L

Legacy 450108

Guest
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,052
Office Version
2013
Platform
Windows
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.
 
L

Legacy 450108

Guest
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,052
Office Version
2013
Platform
Windows
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.
 
L

Legacy 450108

Guest
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,052
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,052
Office Version
2013
Platform
Windows
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
 
L

Legacy 450108

Guest
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,464
Messages
5,487,032
Members
407,576
Latest member
aapnarritesh

This Week's Hot Topics

Top