Posts by GlennUK

Use sum of OFFSET with arithmetic to calculate the offset ... using COLUMN in the arithmetic to adjust depending on position, etc.

Have a different series for each colour. Make the overlap 100%. Have the number format for data labels suppress zeroes, like #,##0;;; as I have done in attached example.

Use this formula in F2: =IF(B2<today,0,C2+(ROUNDDOWN((SUM(C$1:C1)-SUM(D$1:D1,F$1:F1))/COUNTA(B3:B$31),0)))

Explain the logic of what the process should be when the Variance total is larger than the number of days left in the month.

Change formula in cell F2 to be: =IF(B2<today,0,C2+(SUM(D$1:D1,F$1:F1)<SUM(C$1:C1))) and copy down.

I hate any kind of date stamp formula where iteration and circular refs are required ... and your query sounds like that kind of thing. Can I ask why you are not solving this using VBA with Worksheet_Change event?

If you are copying to other columns, then your reference to column A cells should be preceded with a $, like:
=IF($A23="Month Ending",SUBTOTAL(9,K$2:K23)

You can't do that in one step, no. I've done similar, using formulas to generate the link formula that would work, but as a text string, then converting that to formulas afterwards. Do you see what I mean?

You say : "While turning text to columns I change the type to date DMY" - that is not the way it works .... you are supposed to choose the date type of the data coming in, and NOT what you are wanting to change it to. (if I'm understanding your comment correctly, of course).

Hi, it was actually a help file, which I went through and printed every page.

I've scanned a couple of pages so you can see what it's like: (see attached)

I'm very glad to have helped

I did a full list of all jobs, and the relevant half per job, and then altered the half_indicator to fetch the correct half:

Sample 4.xlsx

Quote from dalgleishj72

aGlen this is loads of help. I've already been on the Microsoft excel board to ask if they have a copy of the pdf you mention. Your specific suggestions are illuminating to say the least and a little daunting. I learned basic in the early 70s which I found very simple (I was much younger then) and found VBA when I first looked at it had no relationship to the Basic that I had learned. That is essentially the moral here. The technology evolves. One of my macros does a series of regressions on about 50 mutual funds and interestingly it ran faster on a Mac IIfx in the early 1990s than it does today because of the increase in Excel overhead even though the computers today are way faster.

THANKS AGAIN

I can imagine it's all a little daunting ... I read about VBA on and off for about a year (1998), but was still only developing for users who only had Excel v3.0. So I knew a little VBA in theory, and I had that PDF/hlp file for Excel macro vs VBA printed out ... but then a user in the Far East updated their entire office with new software without telling us about it, and I had to rewrite a 600 row Excel v4.0 macro in VBA a.s.a.p., which was a massive learning curve (you know, VBA Forms instead of Dialog boxes, etc etc), but a great baptism into VBA.

In 1999 I learned a lot about VBA, and some of the things were worrying, like, how much slower certain commands were in VBA (especially Page Setup for printing ... about .25 sec to 10 secs in VBA ... ended up using ExecuteExcel4Macro to use Excel 4.0 PAGE.SETUP function to speed things back up). Also, VBA operates a lot better if you don't select the cells you are changing (yeah, seems obvious now, but it wasn't then), like:

Range("A12").Value="Done"

instead of:

Range("A12").Select
Selection.Value="Done

You can also turn off screen updating if you're updating a lot of cells, so that Excel doesn't have to try to keep the screen up to date with what you are doing ... that can be a big time saving.

Be aware that the hints for Excel 4.0 macros to VBA was written for the old-style interface, so there are references on the menubar manipulation, which no longer makes sense. Also, the charting engine was redesigned for either 2007 or 2010, and so the charting commands cannot be guaranteed to be the correct VBA syntax/command.

I find it very interesting that your code ran faster back in the day, but as you say, the increase in Excel overhead must be massive.

I've looked for info about the Excel 4.0 macro commands vs VBA, and it's not online anyway ... but, I printed something off back then, and I still have the print-out. If I get VERY bored I will scan it into a PDF, but it is dozens and dozens of pages, so would take a while in any case.

As for hints, there are loads of things online for learning VBA, I just can't recommend any, as I learned VBA before such things were available, and so never had any requirement for training for myself.

There are some tricks I've learned over the years, and here are a few:

• VBE Toolbars -Edit: in the macro editing environment (the VBE), switch on visibility for the Edit toolbar, as there are a couple of buttons there that are really useful, being the Indent and Outdent buttons for shifting code left or right, and the Comment Block and Uncomment Block buttons ... all of the buttons mentioned work on selected blocks of code, which is very useful when making changes to code.
• Declare your variables : you don't have to declare variables in VBA, which is great when doing something quick and disposable, but if you are writing anything even slightly more complex then declare your variables. This is the recommended thing to do anyway, and if you set Option Explicit at the top of your module, any spelling mistakes will be caught. But the real advantage is the autocomplete for declared variables ... I use Camelcase, which is prefixing variable names that indicate the variable type, so str for String, int for Integer, lng for Long, wks for Worksheet ... like :
  Dim intSectionCount as Integer, strTitle as String, wksInputData as Worksheet
  .. and then, when you are typing your code, and you start typing a variable name, after you've typed say 4 characters, press Ctrl+Space and the editor will automatically complete the name, or give you a choice of variables/functions that begin with those characters in a choosable tip. That saves me so much time, and I didn't even know about that feature for the first few years of programming in VBA.
• Set objects to save typing ... you can set an object (create an object) that behaves as whatever you've set it as. That is hard to understand, so I'll give an example ...say you are writing some code, and you are referencing a particular workbook and worksheet ... I could set that worksheet as an object, like:
  Set wksInputData = Workbooks("Annual Collation Sales").Worksheets("Monthly Analysis Summary")
  ... then I can type wksInputData any time I was to use that worksheet object ... the object created is equivalent to the fully qualified object referenced in the Set command. You can imagine how easy that makes programming.
• Alternative to Set ... With : Instead of setting up an object to reference, issue a With statement, and that allows you to perform a series of statements on a specified object without requalifying the name of the object, like:
  With Workbooks("Annual Collation Sales").Worksheets("Monthly Analysis Summary")
  .Range("A12").Value = "Hello"
  ... the Range object parent is not qualified, but because a "." is the prefix, it will take whatever is specified in the With statement .. a With block is switched off/terminated with an End With
• For Each loops :- same as FOR.EACH() in old Excel 4.0 macros, but supercharged ... the VBA For Each loops with cycle through each member of ANY collection, whether in-built or user created. So you can look at every sheet in a workbook, or every cell in a range, etc etc, like:
  For Each wksCurr In Workbooks("Input Data.xlsx").Worksheets
  wksCurr.Range("A1").Value="Audited!"

Hope that helps.

Quote from dalgleishj72

How difficult is it to translate into VBA. I tried when VBA came out but found it too complicated. Maybe there are better resources now. I'll look on Amazon

It is difficult to get into your head that you set the attributes in assignments, like:

Range("E12").Font.Bold = True

... instead of issuing commands to set attributes.

Microsoft issued a PDF in the 1990s which translated every Excel 4.0 macro command into a VBA equivalent ... I found that very useful during learning. I'll see if I can locate one via Google.

See if this is close to what you want:
Sample 2.xlsx

Your description is still not clear ... so am having to guess.

Data Validation added which works off a defined name, which does a lookup into Data, returning First Half or Second Half.

Definition: =INDEX(Data!$J$2:$J$17,MATCH(Report!E2,Data!$I$2:$I$17,0))

The INDIRECT of that result (substituting " " with "_") gives a drop-down of the first half or the second half of the list.

The first half and second half are defined name ranges.

See guess workbook: (attached)
Sample_guess.xlsx

Quote

The columns so far have been drop downs via indirect substitute formulae that have been dependent on the previous column content.

I see nothing like that in your sample book. Explain more fully or provide a better example.

I might know the old macro language, but I fully recommend a rewrite in VBA ... it's the only solution where other people can be used for maintenance and support (you are going to be hard-pressed to find anyone who knows the old ways).

Quote from OceanRoaming

I'm having trouble using the Address() function for logical testing. Below is a snapshot of a logical test using ISBLANK() with and without various ADDRESS() functions.

Essentially, when I test cells D1 (a blank cell) & D2 (a non-blank cell), these are my conflicting results.

Ultimately, I'm using VBA code to write a standard formula but this code can be written into any column, so I don't want to use an absolute column reference. I'm using the column() within Address() to apply to any column for which the code might be written. Within that function, results depend upon logical tests. However, I can't get even a basic logical test to properly work.

Display More

Your assumption about using ADDRESS is fundamentally incorrect. The address function gives the text of an address, like "D$1", a string. If you then pass that to an ISBLANK function, it is the same as doing =ISBLANK("D$1"), and NOT =ISBLANK(D$1).

To get a string to be translated into an actual address use the INDIRECT function: