Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Option Explicit Sub FetchAccountBalances() Dim SourceWb, TargetWb As Workbook Dim SourceWs, TargetWs As Worksheet Dim SourceLastRow, TargetLastRow, AccountNo, AccountNoRow, BalanceRow, ac, i, x As Long Dim AccountBalance As Currency Dim Closing As String Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False 'set workbooks Set TargetWb = ActiveWorkbook Set SourceWb = Workbooks.Open(FileName:="C:\!ee\test balances.xls") 'set worksheets Set TargetWs = TargetWb.Sheets("CashReconciliation") Set SourceWs = SourceWb.Sheets("Sheet1") 'get last rows SourceLastRow = SourceWs.Range("K" & Rows.Count).End(xlUp).Row TargetLastRow = TargetWs.Range("B" & Rows.Count).End(xlUp).Row Closing = "CLOSING" 'search balance and write to balance cell on target sheet TargetWs.Activate For ac = TargetLastRow To 2 Step -1 AccountNo = TargetWs.Cells(ac, 2).Value 'set account number to fetch balance For i = 1 To SourceLastRow If CStr(SourceWs.Cells(i, 11).Value) = AccountNo Then AccountNoRow = i AccountNo = CStr(SourceWs.Cells(i, 11).Value) For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count If SourceWs.Cells(x, 3).Value = Closing Then AccountBalance = SourceWs.Cells(x, 15).Value i = x Exit For End If Next x End If Next i TargetWs.Cells(ac, 4) = AccountBalance Next ac SourceWb.Close False Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End Sub
Join the community of 500,000 technology professionals and ask your questions.