Code: Select all
Sub STEP6()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Wb1 As Workbook, Wb2 As Workbook
Dim r2&, lr&, i&
Set Wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets.Item(1)
Set Wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Files\AlertCodes.xlsx")
Set Ws2 = Wb2.Worksheets.Item(4)
With Ws1
lr = .Cells(.Rows.Count, "I").End(xlUp).Row
For i = 2 To lr
' Reset r2
r2 = 0
' Avoid error messages
On Error Resume Next
' Try to get r2
r2 = WorksheetFunction.Match(.Cells(i, "I"), Ws2.[B:B], 0)
' Restore error handling
On Error GoTo 0
' Only set column K if r2 is valid
If r2 > 0 Then
If Ws2.Cells(r2, "D") = ">" Then
.Cells(i, "K").Value = .Cells(i, "D").Value - 0.01 * .Cells(i, "D").Value
Else
.Cells(i, "K").Value = .Cells(i, "D").Value + 0.01 * .Cells(i, "D").Value
End If
End If
Next i
End With
Wb1.Save
Wb1.Close
Wb2.Close
End Sub
This codes calculate 1% of column of column D of 1.xls
but what i wanted is instead of column D, it should calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx and paste the result to same place where the current macro is putting,rest everything will be same
Kindly note AlertCodes.xlsx doesn't have headers so keep a eye on the same
Thnx for the Help