Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
Hello All,
I am working on automating a Journal Entry Tool and could use some help with the last part of my tool.
In need of code that will loop thru a range of cells and find a matching value to the Business Unit and log the cell.address in a sum formula.
Full Description:
1. Column A is my Business Unit
2. Column B is my Offset to column A, so if column B contains the text "Offset" this is a row that will need this Dynamic Offsetting Sum formula
3. Column C is my Amount Column
Sample Data
Sheet1
<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Business Unit (Drop Down)[/TD]
[TD="align: center"]Offset Entry (Select if line is an offset entry)[/TD]
[TD="align: center"]Amt[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]300[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]-250[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]150[/TD]
</tbody>
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
My Working Code to create the Sum Formula - This works with the User manually selecting which cells are matches
I am working on automating a Journal Entry Tool and could use some help with the last part of my tool.
In need of code that will loop thru a range of cells and find a matching value to the Business Unit and log the cell.address in a sum formula.
Full Description:
1. Column A is my Business Unit
2. Column B is my Offset to column A, so if column B contains the text "Offset" this is a row that will need this Dynamic Offsetting Sum formula
3. Column C is my Amount Column
Sample Data
Sheet1
A | B | C | |
Apple | |||
Banana | |||
Pear | |||
Apple | Offset (Here is sample answer | ||
Pear | Offset | ||
Banana | Offset | ||
Apple |
<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Business Unit (Drop Down)[/TD]
[TD="align: center"]Offset Entry (Select if line is an offset entry)[/TD]
[TD="align: center"]Amt[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]300[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]-250[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]150[/TD]
</tbody>
Spreadsheet Formulas | ||||
<tbody> </tbody> |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
My Working Code to create the Sum Formula - This works with the User manually selecting which cells are matches
Code:
Sub Concatenate_Formula()
Dim rSelected As Range, rOutput As Range, c As Range
Dim sArgs As String, sArgSep As String, sSeparator As String, sTitle As String
Dim bCol As Boolean, bRow As Boolean
Dim vbAnswer As VbMsgBoxResult
Dim lTrim As Long
'Set variables
Set rOutput = ActiveCell
bCol = False
bRow = False
sSeparator = ""
sTitle = "CONCATENATE"
'Prompt user to select cells for formula
On Error Resume Next
Set rSelected = Application.InputBox(Prompt:= _
"Select cells to create formula", _
Title:=sTitle & " Creator", Type:=8)
On Error GoTo 0
'Only run if cells were selected and cancel button was not pressed
If Not rSelected Is Nothing Then
'Set argument separator for concatenate or ampersand formula
sArgSep = ","
'Create string of cell references
For Each c In rSelected.Cells
sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
If sSeparator <> "" Then
sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
End If
Next
'Trim extra argument separator at the end of formula
lTrim = IIf(sSeparator <> "", 4 + Len(sSeparator), 1)
sArgs = Left(sArgs, Len(sArgs) - lTrim)
rOutput.Formula = "=-Sum(" & sArgs & ")"
End If
End Sub
Last edited: