Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Sub Sample()
Dim oSht As Worksheet
Dim StartRow As Long, lastRow As Long
Dim strSearch As String
Dim aCell As Range, bCell As Range
Dim KRange As Range, CRange As Range
On Error GoTo Err
Set oSht = Sheets("Sheet1")
lastRow = oSht.Range("K" & Rows.Count).End(xlUp).Row
Set KRange = oSht.Range("K1:K" & lastRow)
'<~~ Account number
strSearch = "0038797031"
Set aCell = KRange.Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'<~~ If Account number Found
If Not aCell Is Nothing Then
StartRow = aCell.Row
lastRow = oSht.Range("C" & Rows.Count).End(xlUp).Row
If lastRow < StartRow Then Exit Sub
Set CRange = oSht.Range("C" & StartRow & ":C" & lastRow)
strSearch = "CLOSING"
Set bCell = CRange.Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not bCell Is Nothing Then
'~~> This will give you the value from Col O in the relevant row
MsgBox oSht.Range("O" & bCell.Row).Value
End If
End If
Exit Sub
Err:
MsgBox Err.Description
End Sub
Sub FetchAccountBalances()
Dim SourceWb, TargetWb As Workbook
Dim SourceWs, TargetWs As Worksheet
Dim LastRow, AccountNo, AccountNoRow, BalanceRow As Long
Dim Closing As String
Set TargetWb = ActiveWorkbook
Set TargetWs = TargetWb.Sheets("CashReconciliation")
Set SourceWb = Workbooks("EE_Test_Balances.xls")
Set SourceWs = SourceWb.Sheets("Sheet1")
SourceWs.Activate
LastRow = SourceWs.Range("K" & Rows.Count).End(xlUp).Row
AccountNo = "1238193921"
Closing = "CLOSING"
For i = 1 To LastRow
If SourceWs.Range("K" & i).Value = AccountNo Then AccountNoRow = i
Next i
For b = AccountNoRow To LastRow
If SourceWs.Range("C" & b).Value = Closing Then BalanceRow = b
Next b
MsgBox ("Account No. " & AccountNo & "balance is on row " & BalanceRow)
End Sub
Sub FetchAccountBalances()
Dim SourceWb, TargetWb As Workbook
Dim SourceWs, TargetWs As Worksheet
Dim LastRow, AccountNo, AccountNoRow, BalanceRow As Long
Dim Closing As String
Dim Accountnum As Long
Set TargetWb = ActiveWorkbook
'Set TargetWs = TargetWb.Sheets("CashReconciliation")
Set SourceWb = Workbooks("EE-Test-Balances.xls")
Set SourceWs = SourceWb.Sheets("Sheet1")
SourceWs.Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
AccountNo = "1238193921"
Closing = "CLOSING"
For i = 1 To LastRow
If CStr(Cells(i, 11).Value) = AccountNo Then
AccountNoRow = i
For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count
If Cells(x, 3).Value = Closing Then
MsgBox Cells(x, 15).Value
i = x
Exit For
End If
Next x
End If
Next i
'For b = AccountNoRow To LastRow
' If SourceWs.Range("C" & b).Value = Closing Then BalanceRow = b
'Next b
'MsgBox ("Account No. " & AccountNo & "balance is on row " & BalanceRow)
End Sub
For i = 1 To LastRow
If CStr(Cells(i, 11).Value) = AccountNo Then
AccountNoRow = i
For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count
If Cells(x, 3).Value = Closing Then
MsgBox Cells(x, 15).Value
i = x
Exit For
End If
Next x
End If
Next i
Sub FetchAccountBalances()
Dim SourceWb, TargetWb As Workbook
Dim SourceWs, TargetWs As Worksheet
Dim LastRow As Integer
Dim Closing As String
Dim AccountNo, AccountNo1, AccountNo2 As String
Set TargetWb = ActiveWorkbook
Set SourceWb = Workbooks("EE-Test-Balances.xls")
Set SourceWs = SourceWb.Sheets("Sheet1")
SourceWs.Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
AccountNo1 = "1238193921"
AccountNo2 = "3414314314"
Closing = "CLOSING"
For i = 1 To LastRow
If CStr(Cells(i, 11).Value) = AccountNo1 Or CStr(Cells(i, 11).Value) = AccountNo2 Then
AccountNoRow = i
AccountNo = CStr(Cells(i, 11).Value)
For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count
If Cells(x, 3).Value = Closing Then
MsgBox "Account " & AccountNo & " has a balance of " & Cells(x, 15).Value
i = x
Exit For
End If
Next x
End If
Next i
End Sub
Set TargetRange1 = TargetWs.Range("ClientUsd1")
Set TargetRange2 = TargetWs.Range("ClientUsd2")
Set TargetRange3 = TargetWs.Range("ClientUsd3")
Set TargetRange4 = TargetWs.Range("ClientUsd4")
Set TargetRange5 = TargetWs.Range("ClientUsd5")
Set TargetRange6 = TargetWs.Range("ClientUsd6")
Closing = "CLOSING"
For a = 1 To 6
For i = 1 To LastRow
If CStr(Cells(i, 11).Value) = AccountNo & a Then
AccountNoRow = i
AccountNo = CStr(Cells(i, 11).Value)
For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count
If Cells(x, 3).Value = Closing Then
AccountBalance = Cells(x, 15).Value
i = x
Exit For
End If
Next x
End If
Next i
TargetRange & a = AccountBalance
Next a