Solved

vba funcion for linking to cells instead of dSum

Posted on 2011-02-18
27
558 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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
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 41

Expert Comment

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

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
Got it!

I'm attempting it right now.

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

Expert Comment

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

Dave
0
 

Author Comment

by:SuraDalbin
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now