Excellent!! Thank you!
Posts by Villa


Hi there,
Unfortunately I never managed to acheive it.
I'll have a look at your code and see if this can inspire me!
Cheers,
V 
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 = Jun02, M26 = Jul02 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 there,
Thanks for your response  I fixed the problem by changing the text qualifyer (in the Link Text wizard) from {none} to " , this then recognised my name field as one field.

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!

Many thanks for your responses  much appreciated!

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 MyRangeor something similar? Has anyone designed an Addin/Utility that would do this?
Many thanks in advance

Hey weasel!
Thanks for the info, I can live with it if to get rid of it is a nightmare.
Thanks for the welcome too, I'm sure I'll be back :tumble:

Hi all,
Is there a way to change the "The cell or chart you are trying to change is protected and therefore readonly etc..." so that it displays a custom message box?
Its annoying me!
Cheers