Thank you, AliGW
Here is the workbook
2018-2019 Church Finances RAB.xlsm
I'm in the process of designing it; hence there are some items not finished (like a button to remove data from some pages, and reviewing for other errors)
Regards,
Neil
Thank you, AliGW
Here is the workbook
2018-2019 Church Finances RAB.xlsm
I'm in the process of designing it; hence there are some items not finished (like a button to remove data from some pages, and reviewing for other errors)
Regards,
Neil
Hi all,
Here is a screen snip of part of my workbook. August is on Row 1
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Screen Shot 2019-07-05 at 6.36.59 pm.png","data-attachmentid":1220059}[/ATTACH]
Here is my formula in cell I126, which is the December "Less: Expenditure"
=-SUMIFS(Transactions!$H:$H,Transactions!$A:$A,">="&H24,Transactions!$A:$A,"<="&H30)-SUMIFS(Transactions!$J:$J,Transactions!$A:$A,">="&H24,Transactions!$A:$A,"<="&H30)
What seems to happen is it seems to be using cells H13 and H19 instead of H24 and H30.
Here is a snip of the two relevant lines in the second quarter (in Australia, we run from June to July)
[ATTACH=JSON]{"alt":"Click image for larger version Name:\tScreen Shot 2019-07-05 at 5.37.36 pm.png Views:\t3 Size:\t30.9 KB ID:\t1220055","data-align":"none","data-attachmentid":"1220055","data-size":"full","title":"Screen Shot 2019-07-05 at 5.37.36 pm.png"}[/ATTACH]
There are no merged cells, so:
Date = Column A
Expend = Column H
GST = Column J
Can someone please help me find the answer?
Thank you,
Neil
PS: Don't bother buying this - it is better to go out and buy a Windows PC or Laptop. Things like create/edit UserForms are missing from VBA and Evaluate Formula is missing from Excel - not very good at all :confused:
Hi all
Here is my code, but the AlreadyOpen is not being turned to True if the workbook is open.
' The code below is from the following sources:
' https://www.ozgrid.com/forum/forum/help-forums/excel-general/31023-open-workbook-if-closed-close-workbook-if-open
' https://www.encodedna.com/excel/how-to-open-a-file-dialog-box-in-excel-using-vba.htm
AlreadyOpen = False
For Each wb In Workbooks
If wb.Name = "Job Expenditure Review.xlsm" Then
AlreadyOpen = True
Exit For
End If
With fd
.Filters.Clear ' Clear all the filters (if applied before).
' Give the dialog box a title, word for doc or Excel for excel files.
.Title = "Select a Text File to Open"
' Apply filter to show only a particular type of files.
' For example, *.doc? to show only word files or
' *.xlsx? to show only excel files.
.Filters.Add "Excel Workbook Files", "*.xlsm?", 1
' Do not allow users to select more than one file.
' Set the value as "True" to select multiple files.
.AllowMultiSelect = False
If .Show = True Then
sFile = .SelectedItems(1)
End If
End With
If sFile <> "" Then
Workbooks.Open sFile ' Open the file.
End If
LastRow = Range("O" & Rows.Count).End(xlUp).Row
Range("A2:P" & LastRow).ClearContents
sFile = ""
Next wb
'
Display More
Any help would be appreciated.
Thank you,
Here is the Test Sub() I created first, and this works ok:
Sub test5()
Dim WbookCheck As Workbook, fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
On Error Resume Next
Set WbookCheck = Workbooks("Job Expenditure Review.xlsm")
On Error GoTo 0
With fd
.Filters.Clear ' Clear all the filters (if applied before).
' Give the dialog box a title, word for doc or Excel for excel files.
.Title = "Select a Text File to Open"
' Apply filter to show only a particular type of files.
' For example, *.doc? to show only word files or
' *.xlsx? to show only excel files.
.Filters.Add "Excel Workbook Files", "*.xlsm?", 1
' Do not allow users to select more than one file.
' Set the value as "True" to select multiple files.
.AllowMultiSelect = False
Dim sFile As String
If .Show = True Then
sFile = .SelectedItems(1)
End If
End With
If sFile <> "" Then
Workbooks.Open sFile ' Open the file.
End If
If WbookCheck Is Nothing Then 'Because the wb is closed, open it
' Workbooks.Open "d:\clients\time sheets\Time Sheets.xls"
ElseIf Application.ActiveWorkbook.Name = WbookCheck.Name Then
WbookCheck.Close SaveChanges:=True
Else
WbookCheck.Activate
End If
End Sub
Display More
btw, in my actual code, I reference the sources used for code which is not my own
Hi all,
Here is my code for checking if a workbook is open; unfortunately, it is failing on the second Set line below:
Dim LastRow As Long, wb As Workbook, WbookCheck As Workbook, fd As Office.FileDialog, MsgStr, sFile As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
On Error GoTo ErrorHandler:
Set WbookCheck = Workbooks("Job Expenditure Review.xlsm")
When using F8 scrolling through the procedure, the variable WbookCheck is Nothing, and that is why it is failing.
It was working before, so I think I must not have something in the right order - and cannot remember what order they were in before, LOL.
Can someone see my mistake?
LOL, thank you so much!
Hi all,
I have a text box in which I was hoping users to enter text for a new Asset.
Sometimes, there will need to be a carriage return for, say, a Rego/Tag number, etc.
Is there anyway to do this in a MultiLine textbox?
Perhaps I should have some kind of loop to capture inputs from the user, a button to add another line, then add the rows to the textbox?
Thank you,
SOLVED!
Thanks, Kjbox!
Hi all,
Here is the part of my code in the UserForm object which faults if the UserForm is closed without entering any data:
Private Sub GST_Incl_txb_Exit(ByVal Cancel As MSForms.ReturnBoolean)
GST_Incl_txb.Text = Format$(GST_Incl_txb.Text, "#,##0.00")
GST_Excl_txb.Value = (GST_Incl_txb / 1.1)
GST_Excl_txb.Text = Format$(GST_Excl_txb.Text, "#,##0.00")
GST_txb.Value = (GST_Incl_txb / 11)
GST_txb.Text = Format$(GST_txb.Text, "#,##0.00")
End Sub
Display More
The second line is causing it, as there is an error dividing nothing by 1.1 - and I do understand why.
There needs to be some kind of error trapping where the line (and the line two lines down, too) is ignored if there is nothing in the GST_Incl_txb textbox, but nothing I've tried seems to work.
Does anyone have any ideas?
Thank you,
Excellent, KjBox! That worked well, thank you!
I know it seems wrong to hard-code cell references, but after my code massages the data into a set format, I need to add additional formulae to the extracted data.
Thanks, KjBox,
I'll look up the FiilDown, too, as this may be better.
Hi all,
Here is my code, which does not generate the desired result:
Dim c, rng As Range, i, j As Long, Txt1, Txt2 As String
i = Range("A" & Rows.Count).End(xlUp).Row
Txt1 = "E"
Txt2 = "G"
Set rng = Sheet1.Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)
For j = 2 To i
For Each c In rng
Cells(j, 9).Formula = "=" & Txt1 & j & Txt2 & j
Next c
Next j
The result, in J2 for example, is:
='E2'+'G2'
So, while the result is very close, I need to have the formula without the ' characters.
Any help would be appreciated!
Hi Mumps,
Your code worked really well, and I like the use of an Array() to populate the Row.
There is an extra step required, however, but I really needed to know how to fix my initial Find() problem.
Here is a cut down version of my sheet:
[ATTACH]n1218834[/ATTACH]
As you can see, there are some times when there are multiple rows under the Account heading. While 98% of them will work great with your formula, I need to know how to account for these multiple rows.
Thank you,
Neil
Thanks Mumps!
That worked great!
I'll study this to find out how everything works.
BTW, I usually leave putting ScreenUpdating in until I have the macro working - found that out the hard way, LOL!
There is a follow up request to this same procedure, should I put it here, or begin a new Topic?
Thanks,
Neil
Thanks AliGW,
I'll give that a go throughout today!
While I was initially skeptical about using a data base type of setup, the fact the data is all date-based, the LOOKUP() should work really well.
BTW I have to be careful how much I put into these sheets, because in the industry in which I work, Rural Local Government (small), it is extremely rare to find someone (who will eventually replace me) who has any background in the use of PowerQuery, etc.
Regards,
Neil
Thanks AliGW,
Do you have any URL's or how to look for examples or suggestions?
Hi all,
[ATTACH]n1218810[/ATTACH]
Attached is a cutdown of my worksheet, which keeps a track of investments. There can be only a few, or many investments, depending on cashflow. I am trying to find out how to incorporate HLOOKUP() to use in formulae instead of having to create many Range Names.
Here is an example.
For Investment 4036, in cell B26 I would like to have the formula return the interest for July for that investment by using cell A26.
So far, the only way I have worked out to return the Interest value for July is by using Range Names instead of HLOOKUP().
Is there a way to use HLOOKUP() in this formula?
Each year I have to create a new schedule for the coming year, but it will be a pain having to create Range Names for every investment. I want to create these formulae at the start of the year for all available Ledgers.
Thank you,
Neil
Hi all,
Here is my code:
Sub FormatForCommitments()
'
' This macro removes only Wrapped Text and Merged Cells
' from the entire worksheet, then finds the first RMS
' Ordered Works commitment, then inputs a formula into
' column G.
'
' Set Variables
Dim i As Long
' Dim j As Long
Dim LastRow As Long
' Change formatting to remove merged and wrap text, then add simple column headings
With Range("A1", ActiveCell.SpecialCells(xlLastCell))
.WrapText = False
.MergeCells = False
End With
Range("A1").Value = "1"
Range("B1").Value = "2"
Range("C1").Value = "3"
Range("D1").Value = "4"
Range("E1").Value = "5"
Range("F1").Value = "6"
Range("G1").Value = "7"
' Find and set last row number into i
LastRow = Range("F" & Rows.Count).End(xlUp).Row
' Select Column A, find each Job Number with the format 15???, then use a loop to add the formula to column G
Columns(1).Select
For i = 9 To LastRow
'Find the first Ordered Work Commitment
Selection.Find(What:="15???", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("G" & i).Formula = "=(F" & i & "+1)"
Next i
End Sub
Display More
The first routines work well. The Formula routine certaily adds a formula, but it adds it to every cell in column G.
The routine should:
Instead of this, it is doing the following:
Is anyone able to help me here?
Thank you,
Neil
Hi all,
There is a dump of financial data each week which are Commitments (for expenditure) which can be up to 700 rows. These are goods which have been ordered, but not yet invoiced from the supplier.
They are organised in rows where column A contains the expense number, which is always a five digit number.
I have to input a formula into column G whenever a row in column A starts with a 5 digit number beginning with 15. So, all the numbers in column A are unique and will range from 10000 to 99999, and I'm only looking for 15000 to 15999.
I have attached an example of the raw data (coloured light blue).
Here is my code, and it's the Find() routine which is failing on finding the first 15XXX number:
For i = 9 To LastRow
'Find the first Ordered Work Commitment
Selection.Find(What:="15???", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("G" & i).Formula = "=(F" & i & "+1)"
Next i
Each Job Number can have multiple purchase orders, and I really need the last line to be the amount to report in Column G.
While the code above generates a formula just to capture the first purchase order amount, my coding skiils are nowhere good enough to generate this properly. I was going to ask for help with variable row numbers next - so that's why the code is so skinny atm.
The attached sheet includes what I want the result to look like (column G):
I have other routines which pick up this figure, so it's just generating the correct forumla based on a variable amount of rows.
Any help would be appreciated.
Thank you,
Neil