Posts by NGM

• SUMIFS() Not Working in Excel (Office 365) on MacBook

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

• SUMIFS() Not Working in Excel (Office 365) on MacBook

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

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:

• Code Checking if Workbook is Open is not Working

Hi all

Here is my code, but the AlreadyOpen is not being turned to True if the workbook is open.

Any help would be appreciated.

Thank you,

• Checking if wb is open - code failing

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

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

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

• Checking if wb is open - code failing

Here is the Test Sub() I created first, and this works ok:

btw, in my actual code, I reference the sources used for code which is not my own

• Checking if wb is open - code failing

Hi all,

Here is my code for checking if a workbook is open; unfortunately, it is failing on the second Set line below:

Code
1. Dim LastRow As Long, wb As Workbook, WbookCheck As Workbook, fd As Office.FileDialog, MsgStr, sFile As String
2. Set fd = Application.FileDialog(msoFileDialogFilePicker)
3. On Error GoTo ErrorHandler:
4. 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?

• Text Box in UserForm - Carriage Returns in MultiLine?

LOL, thank you so much!

• Text Box in UserForm - Carriage Returns in MultiLine?

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,

• Problem when Exiting UserForm

SOLVED!

Thanks, Kjbox!

• Problem when Exiting UserForm

Hi all,

Here is the part of my code in the UserForm object which faults if the UserForm is closed without entering any data:

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,

• Use Variables to Write Formula

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.

• Use Variables to Write Formula

Thanks, KjBox,

I'll look up the FiilDown, too, as this may be better.

• Use Variables to Write Formula

Hi all,

Here is my code, which does not generate the desired result:

Code
1. Dim c, rng As Range, i, j As Long, Txt1, Txt2 As String
2. i = Range("A" & Rows.Count).End(xlUp).Row
3. Txt1 = "E"
4. Txt2 = "G"
5. Set rng = Sheet1.Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)
6. For j = 2 To i
7. For Each c In rng
8. Cells(j, 9).Formula = "=" & Txt1 & j & Txt2 & j
9. Next c
10. 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!

• Find() in VBA not working as planned

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

• Find() in VBA not working as planned

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

• Would like to use HLOOKUP() instead of using many Range Names

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

• Would like to use HLOOKUP() instead of using many Range Names

Thanks AliGW,

Do you have any URL's or how to look for examples or suggestions?

• Would like to use HLOOKUP() instead of using many Range Names

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

• Find() in VBA not working as planned

Hi all,

Here is my code:

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

• Help with the Find() VBA command and variable rows in results

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:

Code
1. For i = 9 To LastRow
2. 'Find the first Ordered Work Commitment
3. Selection.Find(What:="15???", After:=ActiveCell, LookIn:=xlFormulas, _
4. LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
5. MatchCase:=False, SearchFormat:=False).Activate
6. Range("G" & i).Formula = "=(F" & i & "+1)"
7. 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