Posts Tagged ‘VBA’

h1

Excel: Last Entry

28/08/2009

I was challenged to work out a little problem someone was having with Excel today.  The expectation was that I could write some VBA to resolve it, but once I found out what the task was I quickly managed to put together a purely Excel function based solution.

The task was to pick the last entry from a vertical list of cells.  The list is sequential so there are no blank lines which makes the choice of algorithm to find the last entry quite open.   The algorithm I chose was based on a list length and vector approach.

Example

Example

So to start with I needed to know how many entries were in the list.  The COUNTA function will provide this.  This in effect tells us how many rows we have to step down to get to the last entry.  The OFFSET function allows us to carry out these steps based on the first entry in the list.  Because we’re starting at the first entry then we don’t need to move if there is only one entry, so we need to subtract one from the count to give us the number of “steps” to take from the first cell.

The screen shot illustrates how this is built up for a list in column A starting at cell A1.  The function we would use is simply =OFFSET(A1,COUNTA(A:A)-1,0,1,1)

However this does give us a #REF! if the list has no entries – which looks like an error if you give someone a blank list (essentially because it is).  So if you need to account for this, just include an IF.

=IF(COUNT(A:A)=0,””,OFFSET(A1,COUNTA(A:A)-1,0,1,1))

Advertisements
h1

Excel – Multiple Worksheet Macros

01/02/2009

A little while ago I was asked by one of our finance team to see if I could help reduce the workload required in maintaining a reporting Excel spreadsheet. Along with some very specific automation amendments to the spreadsheet I also created something quite generic to allow a macro to be run against multiple worksheets in an Excel workbook.

Originally finance were spending hours making amendments to 96 worksheets! This little helper allowed them to run the other amendments I made and process the whole workbook over their lunch hour (with time to spare).

Behind the scenes there’s a dialog box with a bit of VBA code that you call with the name of the macro you wish to run.   The spreadsheet includes a test function to show how to call the dialog box:

Select the macro to run

Select the macro to run

Public Sub MyTestMacro()
    LoopThroughWorkbooks ("MyMacro")
End Sub

Private Sub MyMacro()
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "xx"
End Sub

When the macro is called it will then display a dialog box with a list of the worksheets in the active workbook. Use CTRL and SHIFT to select the worksheets you want to run a macro against and then let it go.

Select worksheets

Select worksheets

Once you’ve selected the worksheets, just click the Run button and sit back.  Excel will then simply activate each of the selected worksheets in turn and run the macro you’ve specified.

The whole thing is available in a form (frmRunMacroOnManySheets) and a code module (modLoop) for you to download in the spreadsheet below.  Simply copy these into a common workbook or the one in which you specifically need to run a macro against several worksheets.

If you do find this useful please include a link back to this blog.

h1

Excel – Get Worksheet Name

06/01/2009

I was working on an Excel spreadsheet a couple of days ago where I wanted to automatically pick up the name of the current worksheet to use in a VLOOKUP function.  I came up with the following combination of functions to get it for me…

RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))