Code:Public BOExcellFilePath As String Public APExcellFilePath As String Public Sub ApplciationProgram() On Error GoTo ErrorHandler Application.ScreenUpdating = False Dim wb1 As Workbook Dim wb2 As Workbook Dim ws1 As Worksheet Dim ws2 As Worksheet Dim ExcellFilePath As String Dim TempValue As Double Dim RowCount1 As Integer Dim ColumnCount1 As Integer Dim RowCount2 As Integer Dim ColumnCount2 As Integer Dim BOmyArray() As Variant Dim APmyArray() As Variant BOExcellFilePath = "C:\Users\WolfieeeStyle\Desktop\BasketOrder.xlsx" APExcellFilePath = "C:\Users\WolfieeeStyle\Desktop\ap.xls" Set wb1 = Workbooks.Open(Filename:=APExcellFilePath) Set ws1 = wb1.Sheets(1) RowCount1 = ws1.UsedRange.Rows.Count ColumnCount1 = ws1.UsedRange.Columns.Count APmyArray = ws1.Range("A1:U" & ColumnCount1).Value wb1.Close SaveChanges:=False Set wb1 = Nothing Set wb2 = Workbooks.Open(Filename:=BOExcellFilePath) Set ws2 = wb2.Sheets(1) RowCount2 = ws2.UsedRange.Rows.Count ColumnCount2 = ws2.UsedRange.Columns.Count BOmyArray = ws2.Range("A1:Y" & ColumnCount2).Value For i = 1 To RowCount2 TempValue = 0 For j = 2 To RowCount1 If (APmyArray(j, 5) = BOmyArray(i, 3)) Then If (BOmyArray(i, 10) = "BUY") Then If (APmyArray(j, 15) <> "") Then TempValue = APmyArray(j, 15) + APmyArray(j, 15) * 0.01 If (TempValue < BOmyArray(i, 12)) Then ws2.Activate ws2.Cells(i, 12).Value = TempValue End If End If ElseIf (BOmyArray(i, 10) = "SELL") Then If (APmyArray(j, 16) <> "") Then TempValue = APmyArray(j, 16) - APmyArray(j, 16) * 0.01 If (TempValue > BOmyArray(i, 12)) Then ws2.Activate ws2.Cells(i, 12).Value = TempValue End If End If End If End If Next Next Application.ScreenUpdating = True MsgBox "Program successfully completed " Exit Sub ErrorHandler: MsgBox "UNKNOWN ERROR - Error# " & Err.Number & " : " & Err.Description End Sub
Problem Solved




Reply With Quote

Bookmarks