Posts by abundant_lyfe

    Re: Use Checkbox to Show / Hide Subform

    okay, so this is how I did it. I had to re-label my tick box and sub form so that they were different from the control names. Then I put the code both in the tickbox 'after update' and the form's 'current' / 'after update' event areas. It works!!!! :smile:

    Re: Average Range Meeting Multiple Conditions

    Hi Dave

    Apologies for replying to an old thread, but I was wondering if you could explain why an array is advised against and which database function would solve the 'average with two criteria' issue.

    I ask because I am genuinely interested in what you have to say.

    Thank you


    Re: Chart to Show Relational Position

    After another hour of googling I have found the following, which pretty much does what I want.

    Positional Graph

    The only difference is that instead of selecting a range of cells for my series, I selected just one cell, then I was able to give the series a name to represent the person I wanted to show at that position.

    Thank you for your assistance.


    Hi there.

    I am looking to create and unusual chart and have stumped myself. It is something that I can really easily draw on paper, and logically do so, but I am struggling when attempting to reproduce this in Excel.

    My data is this:

    Name, Position A, Position B
    Joe Bloggs, 9, 2
    Dina Lady, 2, 5
    Ann Teak, 0, 9

    I would like my chart to show Joe Bloggs at position 9 across and 2 up, Dina Lady at 2 across and 5 up, Ann Teak at 0 across and 9 up.

    Is there a way to do this? Have I missed a simple trick or will I need to do my plotting manually?

    Thank you in advance for your assistance.


    Re: Find and Replace Formula Error with static value

    Hi there

    You could replace your forumla with the following:


    replacing B5 and C5 with the cells you are using in your calculation.

    This basically says, if the calculation returns an error {IF(ISERROR(B5/C5),} then return this value {"100",} otherwise do the calculation {B5/C5)}

    Hope that helps.

    Re: Select and delete a column that includes merged cells

    Hi SME

    Thank you for your reply.

    In this case, the cells need to be merged in the final report, so that wouldn't work.

    What I did in the end was to redefine the merged cells so that they no longer included an extra cell at the end of the dataset.

    I was just getting frustrated that Excel would permit me to delete an entire column, but VBA won't.

    Here is the code I used. It loops through the dataset and finds the merged cells, then unmerges them, removes the extra cell, then re-merges them. It uses 'Select', which I hate, but it was the only way I could think of doing what I needed to do.
    Good morning all.

    I am in the process of writing a macro to make a database export into Excel presentable for the printed medium. I know that this is not making best of of Excel, but it is how we have been instructed to use it by the top bods.

    I have included an example spreadsheet for you to see.

    My spreadsheet contains merged cells which are passed from the database and need to stay merged as they visually separate out the data. The issues is that the merged cells include one more column than the rest of the data. In order to rectify this I wanted the delete the first unused column in my dataset. This column will change depending on how much data has been exported.

    When I do this manually I simply select the entire column and press delete. This works. However, when I try to do it programatically the Entire dataset is selected thanks to the merged cells.

    My question is, how do I, having found the first unused column, select the entire column and delete it without selecting the entire dataset.

    This is my code so far. Please note - the 'Select' part is simply so that I can test which data will be affected and will be removed once the code works.

    Dim nr4 As Range
    Set nr4 = Range("IV1").End(xlToLeft).Offset(0,1) 'find first unused column
    nr4.Select 'test to see which cell it has found
    nr4.EntireColumn.Select 'test to ensure that just the column will be affected.
    nr4.EntireCoulmn.Delete 'delete entire column

    Thank you for your time.


    I am running Excel 2003 and I wish to apply conditional formatting to only cells with format Number. Does anyone knows an easy way of doing this or a VBA code for it?

    I have tried on my own to write a code , but as I am new to VBA , I can't figure out how to make it work . The code supposed to compare all the numeric values from an uknown number of columns with numeric values from column B row by row and Bold the ones greater. The number of columns and rows is uknown because the data will be exported from another software to Excel , so bassically I wish to format a woorkbook to automatically do that.
    The code is below:

    Thank you very much for any help received.

    Hello there.

    I am attempting to write a macro which formats my data correctly before it is uploaded into a database.

    My data is held on two worksheets, one named 'July' and one named 'Weather.

    In July I have a list of dates and times a certain area has been visited, then spaces for the associated weather data.

    In Weather, I have a list of dates, time in half hourly intervals and the associated wind speed and direction.

    What I am attempting to do is to match the date in July with the date in Weather, then match the times as best as possible. The times in July are rarely on the hour or the half hour, so they have to be matched to the nearest time. For example, 7.45 in the July sheet would pick up the data from 7.30 in the weather sheet because it is between 7.30 and 8.00.

    Having matched the appropriate date and time, the associated weather data is populated in the July sheet.

    I seem to have got most of the macro to work, but it doesn't seem to be copying across the appropriate weather data for the time! I've annotated it as much as I can, but any help you can offer would be greatly appreciated.

    I've also attached a sample of my data. The actual data runs to over 10000 rows!

    Thank you in advance.


    Hi there.

    I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format.

    e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. Does that make sense?

    This is the formlua I am using currently:


    However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold)

    I have attached a small spreadsheet to aid understanding.

    Thank you in advance for your assistance.


    Re: Assign Menu Action To Button On Form

    Oh I see. Thank you.

    Mind you, I now get an error saying that I can't run compact and repair whilst a macro is running. Never mind.

    Thank you for your help. I think that I'll just use the custom toolbar and remove the rest so that it's pretty obvious for the users.

    Thanks again.


    Hello there.

    I just wondered whether it was possible to assign a menu action to a button on a form.

    For example, I would like to assign the menu action "Compact and Repair" to a button on my form so that users of my database don't have to worry about using the menus.

    The other actions I would like are:

    Backup database
    Syncronise Database.

    Thank in for your help in advance.