Posts by james.aldcroft

    Re: Userform To Display/modify A Stock List

    No worries. I couldn't see the spreadsheet functions you referred to being overwritten though - can you point them out.

    Also you said the result was supposed to be returned via user form, where are you returning these results?



    Re: Comma's Purpose In Recorded Macro

    Change your code flags to [/code] and it should be easier to track down.


    Also can you highlight the particular line - lots of commas in there! Cheers,

    Re: Toggle Cell Format Between Two States

    Try this:

    Hello chaps,

    I've been working on a db for one of my mates who's starting up a business, but I haven't really used the 'reports' part of Access.

    I assume it would be possible to produce a purchase order style report, but I can't work out how to do it. I have two tables relevant for creating the PO - order and order details.

    Basically, I want to select an order and generate a PO (showing the relevant order details). Is this possible using 'reports' or will it be necessary to fall back to VBA?



    Hi Chaps,

    I'm probably being incredibly dim, but I can't figure out a way to do this easily:

    Basically, I want to be able to select a range on my spreadsheet, click a button, and have everything that is not selected hide itself.

    I've tried using intersect, but I'm not sure how to (quickly) loop through all of the columns and rows to see if my range is contained within.

    Has anyone got any ideas?

    Thanks in advance.



    I've encountered a bit of an odd one. I am opening a tab delimited file, which has been sved as an xls.

    When I open from file manager, it opens correctly. When I open from Excel, I have to go through 3 screen dictating how the data is set up, and the end result is that a couple of the numbers come through as text, rather than numbers.

    Does anyone know why?

    Failing an explanation - how do you open workbooks as file system objects - currently I am opening them using

    Workbooks.Open Filename:=wb.Path & "/" & ws.Range("B1").Value

    If I open them by mimicking the FM, perhaps it will work OK.

    Thanks in advance,


    Re: Find Method, Searching Space Starting After A Few Cells

    Hi Nathalie,

    You don't have a continuation charachter on the top line of your statement. It should read:

    Dim rFoundCell As Range 
    Dim rFilledCol1 As Range 
     'I define the column excluding the cells that contain empty cells
    rFilledCol1 = Columns(1) - Range("A1:A10") 
    Set rFoundCell = rFilledCol1.Find(What:="", After:=Range("A11"), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ 
    SearchDirection:=xlNext, MatchCase:=False)




    I'm not sure whether I need to use VBA to get the desired result, or whether I can just use something in Excel.

    I've written a worksheet to countdown to a specified time - however, unless a cell value is changed or a manual calculation (F9) is forced then the current time does not update.

    Does anybody have an idea as to how I could get my clock to tick?



    Re: Inserting column and dragging down formulas

    I'm not sure this is exactly what you want - this inserts an new row where you select a cell, and then copies the original row into the row you are inserting into.

    So formulae will be copied down - Is this what you wanted?

    Hi All,

    I've been trying to replace some marker text "*LINEFEEDGOESHERE*" with an actual line feed.

    To do this I am attempting a replaceall, replacing the text with the control characher required. However, when I run it, the control charachter seems to be there, but it isn't doing what it's supposed to. ie, there is a little box there, rather than moving to the next line. Any ideas?



    Re: loop ranges and sheets

    try this

    Re: File unstable if it gets to big.

    I had a similar problem - I got handed 2 150 MB excel sheets to do some data processing, and wierd things started to happen.

    I solved it by putting all of the excel worksheet functions into VBA - this not only reduces file size, but you can actually assign the order in which cells are calculated, rather than letting excel iterate over all of your ranges however many times it takes.



    Re: Print Macro printing wrong sheet

    Sorry, didn't read all of your post about adding/removing sheets.

    If you want to pass over your sheet to the print function you can reference the codename...

    Say You have a sheet with codename Sheet1, Name MySheet.

    To get the name variable from the codename you can use

    'strSheetName now holds the string 'MyName'

    If you want to pass the sheet directly you can use

    Is this what you meant?