?
Solved

Excel VBA to extract cell values from source sheet and write to target sheet based on criteria

Posted on 2011-05-06
7
Medium Priority
?
432 Views
Last Modified: 2012-05-11
I have tried to adapt the solution on http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27018020.html from jocasio123 to search the account balances for more than just one number.

It should start to search for the balance of account number in B7 of target sheet and write balance to D7, then search for balance of account stored in target sheet B6 and write in D6, etc.

I was only partly successfull, somehow it seems I messed up the original code, as only 0's are returned...

any help appreciated.

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

Open in new window

cash-balance-reconciliation.xls
test-balances.xls
0
Comment
Question by:BrdgBldr
  • 4
  • 3
7 Comments
 
LVL 15

Accepted Solution

by:
Juan Ocasio earned 1000 total points
ID: 35711952
Here you go!

This will allow you to select the sourcefile (so it's not hard coded any more) and will allow you to add more account numbers to the target file if you have some.

Good luck!
cash-balance-reconciliation.xls
0
 

Author Comment

by:BrdgBldr
ID: 35712832
Thank you very much jocasio123, it works perfect!

Just out of curiosity two questions:

1) is there any reason why you did remove my approach of setting TargetWb, TargetWs and SourceWb, SourceWs?

2) what is the reason that you dimmed TargetWb as string and changed it from TargetWb. to Workbooks(TargetWb)...?

Just in order that maybe I might learn something!  :o)
0
 

Author Closing Comment

by:BrdgBldr
ID: 35712833
thanks a lot!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35713020
I modified the assignments of the variables because I did not want to hard code the value.  I didn't need to create a reference to the worksheets.

Also, I'm just used to referencing workbook and worksheet (and even cell) objects by passing the name of them to the methods. There really wasnt a right or wrong answer as long as the problem is solved.  I'm there are more efficient ways to get that particular problem solve (maybe even using your methods) but I've been away from vba programming for so long :-).

Glad I could be of help!!!

Good luck!
0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35713021
I modified the assignments of the variables because I did not want to hard code the value.  I didn't need to create a reference to the worksheets.

Also, I'm just used to referencing workbook and worksheet (and even cell) objects by passing the name of them to the methods. There really wasnt a right or wrong answer as long as the problem is solved.  I'm there are more efficient ways to get that particular problem solve (maybe even using your methods) but I've been away from vba programming for so long :-).

Glad I could be of help!!!

Good luck!
0
 

Author Comment

by:BrdgBldr
ID: 35713023
fully understood! thanks!
0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35713035
No problem!  Good luck to ya!
0

Featured Post

Free Tool: Port Scanner

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question