Posts by Villa

    Hi all,


    I'd like to be able to open a lotus notes database from Excel VBA.


    I've looked at the code where you can open a Lotus notes mail session but how can I alter this code to open a specified lotus notes database (eg. not a lotusnotes mailbox).


    Any ideas much appreciated!

    Thats a good point, I changed the dynamic range to be:


    =OFFSET(External_Data!$A$2,1,1,COUNTA(External_Data!C:C),4)


    as I discovered that I didnt really need to reference the ExternalData1 table as A2 will do, this way I can ensure that it is absolute.


    I dunno whats going on because when I use:


    =VLOOKUP(N26,FTEs0102,4,FALSE) on a sheet called 'Graph' I get #REF!


    When I use =VLOOKUP(Graph!N26,FTEs0102,4,FALSE) on the same sheet as the lookup table array it works.


    Bloody microsoft.

    The named range is called 'FTEs0102'.


    The vlookup formula is as follows:


    =VLOOKUP(L26,FTEs0102,4,FALSE)
    =VLOOKUP(M26,FTEs0102,4,FALSE)
    =VLOOKUP(N26,FTEs0102,4,FALSE)


    where L26 = Jun-02, M26 = Jul-02 etc


    The top formula works fine. When I go into the vlookup formula and highlight the FTEs0102 and hit F9 this is what I get.


    =VLOOKUP(L26,FTEs0102,4,FALSE)


    =VLOOKUP(L26,{36923,0,373,0;36951,313,356,669;36982,299,319,618;37012,291,323,614;37043,301,312,613;37073,303,303,606;37104,292,291,583;37135,307,292,599;37165,303,237,540;37196,303,237,540;37226,300,210,510;37257,350,142,492;37288,293,182,475;37316,292,186,478;37347,291,184,475;37377,300,199,499;37408,301,208,509;37438,308,226,534;37469,313,235,548;37500,316,233,549;37530,321,244,565;37561,323,243,566;37591,328,230,558},4,FALSE) - this is correct


    =VLOOKUP(M26,FTEs0102,4,FALSE)


    =VLOOKUP(M26,#REF!,4,FALSE) - this doesnt work at all



    =VLOOKUP(N26,FTEs0102,4,FALSE)


    =VLOOKUP(N26,{36923,0,373,0;36951,313,356,669;36982,299,319,618;37012,291,323,614;37043,301,312,613;37073,303,303,606;37104,292,291,583;37135,307,292,599;37165,303,237,540;37196,303,237,540;37226,300,210,510},4,FALSE) - this only returns about half of the table.


    I've checked the formulas and all seems to be exactly the same except for the L26, M26 and N26 but they all return different things.


    Help!

    Hi all,


    Wondering if anyone has any ideas on this problem.


    I have a dynamic range that selects a range 4 columns wide and x rows down. The range is part of some external data I have imported from Access.


    Here is the formula:


    =OFFSET(External_Data!ExternalData1,1,1,COUNTA(External_Data!A:A),4)


    I have two problems with this formula.


    1. For some odd reason the part (External_Data!A:A) changes to B:B or C:C without me actually changing it!


    2. I use this formula as the table array for a vlookup formula.


    On another sheet in column L, the formula works fine. In column M the formula doesnt work, in column N the formula only seems to pick up half the table!


    Why why why?!?!


    Thanks in advance and merry christmas!

    Hi all,


    Could someone please explain the .formulaR1C1 function?


    I would like to put in a SUM formula into a cell using VBA.


    eg.
    =SUM(O7:U15)


    This column and the row might change so I have a variable for each column and row


    eg. O = Col1, 7 = Row1, U = Col2 and 15 = Row2.


    How do I structure the R1C1 formula to include these variables?


    Thanks in advance!

    Hi all,


    I have an Access database and I'd like to link (table) to a .txt file.


    The text file is comma delimited but it holds one field of peoples names.


    The problem is the names are also separated by a comma (eg. Smith, John Mr) so when I link the table Access creates a new field so all field after this are out of whack by one column.


    How can I get it to not do this?


    Thanks!

    Hi,


    I have a range on a sheet (this is currently A1:K194). This sheet gets updated each month with new figures so the number of rows each month will change.


    How can I find what the range is using code? (ie. the equivalent of doing Ctrl+Shift+End - which selects the range on the sheet).


    Thanks!

    Hi all,


    I have a large spreadsheet that is full of complicated formulas and named ranges.


    Is is possible to scan the workbook and produce a list of all cells that have a formula or named range?


    eg. the list would show


    cell formula named range
    A1 =MyRange+B2 MyRange


    or something similar? Has anyone designed an Add-in/Utility that would do this?


    Many thanks in advance