Posts by NGM

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
'

Any help would be appreciated.

Thank you,

LOL, found the error. When F8'ing through the Test Sub, it was clear the following line was being ignored:

Set WbookCheck = Workbooks("Job Expenditure Review.xlsm")

So, deleting this line fixed the error!! " :stare:

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

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

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

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:

• Find the first cell in column A which is in the format 15???
• Go across to column G and input a formula which returns the number in colum F, but the next row

Instead of this, it is doing the following:

• Finding the first used cell in column A
• Input a formula in column G of that same row, but which returns the value of the adjacent cell in column F
• And adds one to that value

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