calculate copy and paste

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") 
Set wb2 = Workbooks.Open(fPath & "sample2.csv") 
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
    wb1.Close True
    wb2.Close True
End Sub

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


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

i have posted the question  and then i have deleted the same 

i tried to write the code but i got an error and i am not understanding what mistake i have done in this so plz have a look and let me know



When your code runs an error occurs, you say. In fact, I presume that the code crashes (you don't say). The normal way for for VBA to crash is to give a message. There will be an error number and an error description. This is vital information which you should provide in your question. But there is more: The message box quoting the error details has several buttons. One of them is captioned "Debug". If you click that button the screen will jump to the VB Editor and the code which is running. The line of code which caused the error will be highlighted. You should inform us of which line that is.
Variatus (rep: 4864) Jul 23, '19 at 12:32 am
application defined or object defined error
while debugging
For Each c In sh1.Range("E2", sh1.Cells(rows.Count, 5).End(xlUp))
this line is highlighted
Mam and sorry mam for not providing the complete details of the problems
style36 (rep: 24) Jul 23, '19 at 1:01 am
Add to Discussion


Selected Answer

The line of code giving the trouble contains imprecise syntax. It requires an additional reference to Sh1.

For Each C In Sh1.Range("E2", Sh1.Cells(Sh1.Rows.Count, 5).End(xlUp))

The difference is in that the above code counts the rows in Sh1 instead of the ActiveSheet as your original code does. Since two workbooks are opened by your code it may be hard to tell which is the active sheet at the time. Therefore you can't know in which sheet the rows are counted by your formula. However, if both workbooks were created by the same version of Excel the number of rows would be the same and, in any case, in your code, it will return a large number and therefore not cause an error. It's an imprecision, not the source of the error you have uncovered. In fact, your line of code works fine with me.

The error is likely to hail from the inavailability of Sh1 at the time of setting the range. Of course, Sh1 is a worksheet in Wb1 and wouldn't be found if the workbook wasn't opened properly. But if this were the case an error should occur on Set Sh1 = wb1.Sheets(1).

Examining this line of code  I find another imprecision in your specification of Sheets(1) instead of Worksheets(1). Your code requires a worksheet. I doubt that your Wb1 contains any other kinds of sheets and, frankly, don't know if Sh1.Cells(Sh1.Rows.Count, 5).End(xlUp) would fail on another kind of sheet if there was one. However, I also doubt that you would know the answer to that question. Therefore, why specify Sheets(1) when you know for sure that you mean Worksheets(1)?

I suggest to test the availability of Sh1 like this:-

    Set Sh1 = wb1.Sheets(1)
    MsgBox Sh1.Name

If this test produces the worksheet's name (instead of an error), you might try another test to find out if the range is set correctly.

    Set Sh1 = wb1.Sheets(1)
    MsgBox Sh1.Range("E2", Sh1.Cells(Sh1.Rows.Count, 5).End(xlUp)).Address

Finally, check if Wb1.Sheets(1) (or Wb1.Worksheets(1)) is accessible. It might be hidden or password protected, causing access to fail.

I noticed your liberal use of Offset. Offset should be used when you don't know your starting point. That isn't the case in your code where the starting point is always range C. C.Column = 5, C.Row gives you the row number. C.Offset(,8) therefore equals Sh1.Cells(C.Row, 13) or, as you might prefer, Sh1.Cells(C.Row, "M"). Of course the specified target is a cell, therefore a range, and therefore can't be <>"". What you are testing is the cell's Value property which you should mention consistently to make your code less prone to being misread.



I tried but i was unsuccessful Mam
Mam forget my code bcoz it contains error 
Plz u write the code in ur way 
so i can learn from it  
style36 (rep: 24) Jul 24, '19 at 2:58 am
I can't test your code because it opens files in your PC. If you aren't successful in what you tried, publish here what you tried and what result you got. I will guide you step by step but can't do it for you.
Variatus (rep: 4864) Jul 24, '19 at 5:16 am
Mam i am not a programmer like u one of my frnd is learning vba and i have this question so i told him and he gave me this code but it is not workable so i told him but he doesnt know what to do with this code now
I will be very thankful to u mam that u plz edit the code according to u and make it workable mam
style36 (rep: 24) Jul 24, '19 at 5:25 am
I can't make your code function because it must run on your computer, not only mine. Ask your friend to make the tests I asked for, or do them yourself. Advise any errors you get. Ignore my suggestions to improve precision. They are meant to teach write better code but will have no effect on the result to expect here and now.
Variatus (rep: 4864) Jul 24, '19 at 6:42 am
Thnx Alot Variatus Mam for ur great support and ur great explanation and guidance
style36 (rep: 24) Jul 24, '19 at 1:21 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login