Solved

vba funcion for linking to cells instead of dSum

Posted on 2011-02-18
27
560 Views
Last Modified: 2012-06-27
Hi Experts,

I have the following code, which takes the User's input, via a dropdown list, and based on the selection made or "description" in column G, the corresponding number, either in column C or column E, is populated in a specific cell in a different worksheet of the same workbook.  Also, the code determines that if the amount is in Column E, this number is converted to a negative number.  Furthermore, if the User selects the same "description" in Column G for multiple line items, the code sums the corresponding cells, if both numbers are in Column C or it nets the amounts if these amounts are in different columns C or E and it places the total or net of these amounts in that one specific cell in a separate worksheet, as I stated previously.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "TB Import (A)" Then
    If Not Application.Intersect(Target, ['TB Import (A)'!C:G]) Is Nothing Then
        RecalculateAll 1
    End If
End If
If Sh.Name = "AJEs (I)" Then
    If Not Application.Intersect(Target, ['AJEs (I)'!D:H]) Is Nothing Then
        RecalculateAll 2
    End If
End If
If Sh.Name = "AJEs (I)" Then
    If Not Application.Intersect(Target, ['AJEs (I)'!R:V]) Is Nothing Then
        RecalculateAll 3
    End If
End If
End Sub

Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
    Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
    Select Case nType
    Case 1
        Set oDataColA = ['TB Import (A)'!C:C]
        Set oDataColB = ['TB Import (A)'!E:E]
        Set oCatCol = ['TB Import (A)'!G:G]
    Case 2
        Set oDataColA = ['AJEs (I)'!F:F]
        Set oDataColB = ['AJEs (I)'!H:H]
        Set oCatCol = ['AJEs (I)'!D:D]
    Case 3
        Set oDataColA = ['AJEs (I)'!T:T]
        Set oDataColB = ['AJEs (I)'!U:U]
        Set oCatCol = ['AJEs (I)'!R:R]
    Case Else
    End Select
    
	dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
    Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Open in new window


The code above works perfectly, but I would like to know which [b]vba funcion[/b] I could replace "dSum" with so that instead of reflecting the total or net amount, or a single number, a formula with cell addresses is populated in the corresponding worksheet.  

More specifically, in the attached excel spreadsheet you can see that in worksheet "TB Import (A)" I have assigned the "cash" description to three line items.  The amounts in these three line items net to $250 dollars, and as such, in worksheet "Balance Sheet (H)" in D6, there is an amount of $250, but what I would like to see instead in "Balance Sheet (H)" cell D6 is the following formula ='TB Import (A)'!C8+'TB Import (A)'!C9-'TB Import (A)'!E10.  The purpose of this change would be to have a better trail to follow, instead of just a number.

Please let me know if you require more detail or further explanation.

Any help  on this will be greatly appreciated.

Thanks,


Sura
 FinancialData-Template-3---Copy.xlsm
0
Comment
Question by:SuraDalbin
  • 17
  • 10
27 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34931941
So - you would like the formula to be pasted in as you've specified?  Why not put the SUMIFS equation in the spreadsheet, if you don't mind my asking?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931959
Very clever macro.

It would take one just as clever to find all the "Cash" items and show the math in that cell.  What if there were 20 items in the list marked with the description "Cash"?  The user could just go to the first tab and see all the cash items.  A SUMIFS formula in the balance sheet would do as much - not be as descriptive - but it would show the user what columns you were summing based on the CASH modifier in the description.

Do you really want to go down this route?

Dave
0
 

Author Comment

by:SuraDalbin
ID: 34931981
Dave how is it going,

Thanks much for your responses.  I was testing the sumif formula, but for some reason, it wasn't working for me.  Let me continue testing it here.  The cool thing about the code was that the calculations were everytime a change was made to the TB Import (A) worksheet.  Can this be replicated with the SUMIF formula.
0
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.

 
LVL 42

Expert Comment

by:dlmille
ID: 34931986
Absolutely - let me make the change and show you.  Glad you sent the note, cause I was trying to build the formula,lol!

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931995
Put this in cell D6 of your balance sheet:

=SUMIFS('TB Import (A)'!$C$7:$C$93,'TB Import (A)'!$G$7:$G$93,"Asset:Cash")-SUMIFS('TB Import (A)'!$D$7:$D$93,'TB Import (A)'!$G$7:$G$93,"Asset:Cash")

Dave
0
 

Author Comment

by:SuraDalbin
ID: 34931999
Dave,

Regarding your second post.  Yes, it's actually the part that it's not "as descriptive" with which I'm having a problem.  From a manager's point of view, whose are a bit "old school" if you will, they'd like to be able to easily double click on a cell and have that action show the multiple cells that make up a number.  It's more of a "I don't trust that you did it right" mentality, even though it's formula-driven.  I don't know if that makes sense.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34932005
Correction:

=SUMIFS('TB Import (A)'!$C$7:$C$93,'TB Import (A)'!$G$7:$G$93,"Asset:Cash")-SUMIFS('TB Import (A)'!$E$7:$E$93,'TB Import (A)'!$G$7:$G$93,"Asset:Cash")
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34932008
You could have a "hidden" column (like column 0) that has the account you're looking for, thus changing the formula to:

=SUMIFS('TB Import (A)'!$C$7:$C$93,'TB Import (A)'!$G$7:$G$93,$O6)-SUMIFS('TB Import (A)'!$E$7:$E$93,'TB Import (A)'!$G$7:$G$93,$O6)

See attached - balance sheet (note, I changed your subroutines in the VBA so they wouldn't FIRE by renaming the function to functionXXX

Dave
FinancialData-Template-3-r1.xlsm
0
 

Author Comment

by:SuraDalbin
ID: 34932016
Dave,

Thanks for your suggestions.  These work fine, but the problem that my managers are having, is that they are not able to, with the click of one button, see the cells that make up the $250 in cash, from my original example.  See, the SUMIF formula works like a charm, but it's more of a "behind the scenes" approach, as is the code I had originally.  They'd like to be able to double click on cell D6 in "Balance Sheet (H)" and see all the cells in "TB Import (A)" that make up this total.  It's unnecessary if you ask me, but hey, they're the ones that have the last say in this.  Any thoughts?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34932316
So if they look at TB Import, they can see the linkage right there.  How about if they double click on it, it takes them to the TB Import sheet and highlights the cells being referenced?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34932359
Nasty, but do-able:  See the code I added after your dSum assignment.  I did it just for the CASH case, so you can take it from there, I think.  I created a function that built the formula based on what was in the TB Import sheet.

Does this work for you?

Cheers,

Dave

Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
    Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
    Select Case nType
    Case 1
        Set oDataColA = ['TB Import (A)'!C:C]
        Set oDataColB = ['TB Import (A)'!E:E]
        Set oCatCol = ['TB Import (A)'!G:G]
    Case 2
        Set oDataColA = ['AJEs (I)'!F:F]
        Set oDataColB = ['AJEs (I)'!H:H]
        Set oCatCol = ['AJEs (I)'!D:D]
    Case 3
        Set oDataColA = ['AJEs (I)'!T:T]
        Set oDataColB = ['AJEs (I)'!U:U]
        Set oCatCol = ['AJEs (I)'!R:R]
    Case Else
    End Select
   
    dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
    Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum
   
    If nType = 1 Then
        Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow("'TB Import (A)'!")
    End If
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Function UpdateNoDSUMShow(mySheet As String) As String
Dim myCell As Range, myFormula As String

    For Each myCell In Range(Range(mySheet & "G7").Address, Range(mySheet & "G65535").End(xlUp).Address)
        If Cells(myCell.Row, "C").Value <> 0 Then
            myFormula = myFormula & " + " & mySheet & "C" & myCell.Row
        ElseIf Cells(myCell.Row, "E").Value <> 0 Then
            myFormula = myFormula & " - " & mySheet & "E" & myCell.Row
        End If
    Next myCell
   
    UpdateNoDSUMShow = myFormula
End Function
FinancialData-Template-3-r1.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34932386
I'm getting closer...

Dave
Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
    Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
    Select Case nType
    Case 1
        'Set oDataColA = ['TB Import (A)'!C:C]
        'Set oDataColB = ['TB Import (A)'!E:E]
        'Set oCatCol = ['TB Import (A)'!G:G]
        Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow("'TB Import (A)'!", oThisCat.Value, "G", "C", "E")
    Case 2
        'Set oDataColA = ['AJEs (I)'!F:F]
        'Set oDataColB = ['AJEs (I)'!H:H]
        'Set oCatCol = ['AJEs (I)'!D:D]
        Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow("'TB Import (A)'!", oThisCat.Value, "D", "F", "H")
    Case 3
        'Set oDataColA = ['AJEs (I)'!T:T]
        'Set oDataColB = ['AJEs (I)'!U:U]
        'Set oCatCol = ['AJEs (I)'!R:R]
        Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow("'TB Import (A)'!", oThisCat.Value, "R", "T", "U")
    Case Else
    End Select
    
    'dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
    'Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum
    
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Function UpdateNoDSUMShow(mySheet As String, myAccount As String, descrCol As String, debitCol As String, creditCol As String) As String
Dim myCell As Range, myFormula As String

    For Each myCell In Range(Range(mySheet & descrCol & "7").Address, Range(mySheet & descrCol & "65535").End(xlUp).Address)
        If myCell.Value = myAccount Then
            If Cells(myCell.Row, debitCol).Value <> 0 Then
                myFormula = myFormula & " + " & mySheet & debitCol & myCell.Row
            ElseIf Cells(myCell.Row, creditCol).Value <> 0 Then
                myFormula = myFormula & " - " & mySheet & creditCol & myCell.Row
            End If
        End If
        Debug.Print myFormula
    Next myCell
    
    UpdateNoDSUMShow = myFormula
End Function

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 34932418
Ok, here goes.  I think I did it.  You didn't have data but for the TB Import sheet, so that's all I tested.  It should go similarly for the other sheets.

Based on type, I set the KeySheet name, the description column to test, the debit column and the credit column

Then, I assigned the formula for the destination cell to be based on the function call, passing these 4 parameters.

The function UpdateNoDSUMShow(mySheet As String, myAccount As String, descrCol As String, debitCol As String, creditCol As String) As String

searches in the sheet - mySheet, for myAccount to be used, looking in the descrCol.  If it matches, it looks for non-zero value in the debit Col and thus build formula using + sign.  Otherwise, if finds nonzero in the credit Col, it builds formula using the - sign.  I assume credit or debit but not both on a single entry.

When the formula is finished being built (e.g., the range is examined) then it passes it back.  A null formula = "" will place a 0 in that cell.

The range it searches is from row 7 to the last used row (from bottom to xlup) of the range descrCol range, as both sheets start in row 7.

See code below, and attached file.
Enjoy!

Dave
Private Sub RecalculateAll(nType As Integer)
Dim nCtr As Integer, dSum As Double, oDataColA As Range, oDataColB As Range, oCatCol As Range
Dim oThisCat As Range
Dim keySheet As String, descrCol As String, debitCol As String, creditCol As String

Application.EnableEvents = False
Application.ScreenUpdating = False

For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
    Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
    Select Case nType
    Case 1
        'Set oDataColA = ['TB Import (A)'!C:C]
        'Set oDataColB = ['TB Import (A)'!E:E]
        'Set oCatCol = ['TB Import (A)'!G:G]
        keySheet = "'TB Import (A)'!"
        descrCol = "G"
        debitCol = "C"
        creditCol = "E"
    Case 2
        'Set oDataColA = ['AJEs (I)'!F:F]
        'Set oDataColB = ['AJEs (I)'!H:H]
        'Set oCatCol = ['AJEs (I)'!D:D]
        keySheet = "'AJEs (I)'"
        descrCol = "D"
        debitCol = "F"
        creditCol = "H"
    Case 3
        'Set oDataColA = ['AJEs (I)'!T:T]
        'Set oDataColB = ['AJEs (I)'!U:U]
        'Set oCatCol = ['AJEs (I)'!R:R]
        keySheet = "'AJEs (I)'"
        descrCol = "R"
        debitCol = "T"
        creditCol = "U"
    Case Else
    End Select
    
    Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Formula = "=" & UpdateNoDSUMShow(keySheet, oThisCat.Value, descrCol, debitCol, creditCol)
    'dSum = Application.WorksheetFunction.SumIfs(oDataColA, oCatCol, "=" & oThisCat.Value) - Application.WorksheetFunction.SumIfs(oDataColB, oCatCol, "=" & oThisCat.Value)
    'Application.Range("'" & oThisCat.Offset(, 1).Value & "'!" & oThisCat.Offset(, nType + 1).Value).Value = dSum
    
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Function UpdateNoDSUMShow(mySheet As String, myAccount As String, descrCol As String, debitCol As String, creditCol As String) As String
Dim myCell As Range, myFormula As String

    For Each myCell In Range(Range(mySheet & descrCol & "7").Address, Range(mySheet & descrCol & "65535").End(xlUp).Address)
        If myCell.Value = myAccount Then
            If Cells(myCell.Row, debitCol).Value <> 0 Then
                myFormula = myFormula & " + " & mySheet & debitCol & myCell.Row
            ElseIf Cells(myCell.Row, creditCol).Value <> 0 Then
                myFormula = myFormula & " - " & mySheet & creditCol & myCell.Row
            End If
        End If
        Debug.Print myFormula
    Next myCell
    
    If myFormula = "" Then myFormula = "0"
    
    UpdateNoDSUMShow = myFormula
End Function

Open in new window

FinancialData-Template-3-r2.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34932430
PS - I recommend putting non-event functions into a MODULE.  Generally speaking, only the Worksheet_Change or other subs should be in the Sheet codepage, and the rest into a module as public RecalculateALL subroutine and private UpdateNoDSUMShow (so not user viewable) function.

Dave
0
 

Author Comment

by:SuraDalbin
ID: 34933802
Hi Dave,

I apologize for the delay in my response, but my day is barely getting started here :).  I'll test this last code you provided and let you know.  From the top I can tell you that yes, it'd be awesome if the code takes the reviewer to the "TB Import (A)" worksheet and highlights it.

Testing now, be right back :)
0
 

Author Comment

by:SuraDalbin
ID: 34934120
I'm back.

Thanks for your help with this Dave.

For worksheet TB Import (A), the User will always have both an account name (Column B) and a Debit (Column C) or Credit (Column E).  For worksheet "AJEs (I)", however, the User will enter amounts and will select a description for each entry.  Because of this, I'm getting a "Run-time error '1004': Method "Range" of Object '_Global' failed and it highlights the
For Each myCell In Range(Range(mySheet & descrCol & "7").Address, Range(mySheet & descrCol & "65535").End(xlUp).Address)

Open in new window

.

So the main difference between these worksheets is that the AJEs (I) worksheet will always be empty, or in other words, there will not be amounts because these amounts vary from project to project.  The User will enter amounts, Debit or Credit, and assign a description.

Is there a way to account for this in the AJEs worksheet?

Thanks again,


Sura

0
 
LVL 42

Expert Comment

by:dlmille
ID: 34934212
I'm not sure - how about some test data?  Walk me through it.

I don't even know if what you had before worked, as there was no data in this area.

Dave
0
 

Author Comment

by:SuraDalbin
ID: 34934280
So in the excel file you provided in your previous post, if you look at the Balance Sheet (H) cell D6, the amount shown is (82,860.77), and it shows the formula, awesome.  As part of completing this project, we might need to increase this amount by say, $20,000, this is just an example.  To do this, we would go to worksheet "AJEs (I)" to D8 and select from the dropdown list, "Assets:Cash" and type $20,000 in F8.

We then go back to Balance Sheet (H) and this $20,000 should be reflected in F6.  Also, when you click on this F6, we should see =+'AJEs (I)!D8
0
 

Author Comment

by:SuraDalbin
ID: 34934288
Correction, to
Also, when you click on this F6, we should see =+'AJEs (I)!D8

The formula shown in Balance Sheet (H) F8 should be =+'AJEs (I)'F8
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34934426
ok let me see...  I never tested type 2 and 3 as I stated.  Let's make 'em work!

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34934452
Sometimes its the little things, like syntax errors:

keySheet = "'AJEs (I)'!" <- see that Exclamation point, and the single quotes around the sheet names.. In the build, I build sheet, address and the key sheet has to be formatted like this for it to work (so I don't have to do "'" & mySheet. & "'!" & column & "7" <- a bit of a pain to code, but once it works its ok.

I wanted to save myself the trouble, so I made keySheet (which is mySheet in the function call) have the single quotations and exclamation point!

So, here's the resolved code:
For nCtr = 2 To Application.Range("Descriptions!A1").End(xlDown).Row
    Set oThisCat = Application.Range("Descriptions!A:A").Cells(nCtr)
    Select Case nType
    Case 1
        'Set oDataColA = ['TB Import (A)'!C:C]
        'Set oDataColB = ['TB Import (A)'!E:E]
        'Set oCatCol = ['TB Import (A)'!G:G]
        keySheet = "'TB Import (A)'!"        descrCol = "G"
        debitCol = "C"
        creditCol = "E"
    Case 2
        'Set oDataColA = ['AJEs (I)'!F:F]
        'Set oDataColB = ['AJEs (I)'!H:H]
        'Set oCatCol = ['AJEs (I)'!D:D]
        keySheet = "'AJEs (I)'!"        descrCol = "D"
        debitCol = "F"
        creditCol = "H"

I hope you don't mind, but I moved the functioning code to a module, to help against future "why doesn't it work" issues, which can happen in the SHEETCODE page...

Enjoy!

Dave
FinancialData-Template-3-r3.xlsm
0
 

Author Comment

by:SuraDalbin
ID: 34934616
OH YES!

This works perfectly :)

Thank you SO much!

One quick question:  As you know, currently I have three cases for which the code is "listening" if you will, even thought this is not flash, right, but anyway.  Technically, if I were to add one more case, the code should work, correct.  This new case, will be another sets of columns in the AJEs (I) worksheet.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34934655
It should work.  If you have additional issues, just post a RELATED question and I'll be alerted and jump on it.

If theres a bug on the existing work, just post here.

Dave
0
 

Author Comment

by:SuraDalbin
ID: 34934659
Got it!

I'm attempting it right now.

Thanks a million Dave, you are the best! :)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34934748
Can you explain how you rated this post?  Just trying to understand your "Good".  Thx.

Dave
0
 

Author Comment

by:SuraDalbin
ID: 34934812
I apologize!  I'm at work and I just awarded the points and did not go through the ratings.  How can I go back.  No, your input and Help was EXCELLENT Dave.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34934964
No worries - I think the Rating # is based on how many messages are in the form and no impact on the solution provider.  I did think that "Good" or "Excellent" was a direct result of your input, which is why I asked...

Dave
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need Help with Mapping ONLY 9 26
Turn date into age 17 33
How do you check for multiple possibilities in Excel 7 28
HOW I CAN MANUALLY Format AN COLUMN ! 2 18
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

828 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