Posts by Imbuzi

Thanks Will for the quick reponse. She is a complete novice so she needs something almost along the lines of an idiots guide to SQL. Ill have a look at the SAMS book

Thanks once again.

Graeme

Hi All,

I have just been asked to recommend an SQL book to a collegue so that she can learn how to use SQL. She needs a book that will explain in simplified terms Unions and other more basic aspects of SQL.

TIA

Graeme

Hi Guys,

Brain has re-engaged I worked out the solution

Dim r As Integer


Sub RangeColSetup()


c = 1


r = 0


    SheetName = ActiveSheet.Name


    Worksheets("CL Reserves").Activate


    Cells(2, 2).Activate


Do Until IsEmpty(ActiveCell)


    ActiveCell.Offset(0, 1).Activate


    c = c + 1


Loop


'    r = r - 1


   Cells(3, 1).Activate


Do Until IsEmpty(ActiveCell)


    ActiveCell.Offset(1, 0).Activate


    r = r + 1


Loop


ActiveWorkbook.Names.Add Name:="test1", RefersToR1C1:= _


        Worksheets("CL Reserves").Range(Cells(3, 1), Cells(r, c))


 


        


End Sub

TIA

Graeme

Hi All,

My brain has gone into neutral....

I am trying to define a named range within a macro where I have identified the start and end of the area. what I cant seem to remember is how to code the last part of the add name range.

Sub RangeColSetup()


c = 1


r = 0


    SheetName = ActiveSheet.Name


    Worksheets("CL Reserves").Activate


    Cells(2, 2).Activate


Do Until IsEmpty(ActiveCell)


    ActiveCell.Offset(0, 1).Activate


    c = c + 1


Loop


'    r = r - 1


   Cells(3, 1).Activate


Do Until IsEmpty(ActiveCell)


    ActiveCell.Offset(1, 0).Activate


    r = r + 1


Loop


area1 = ActiveSheet.Cells(3, 1)cells(r,c)


ActiveWorkbook.Names.Add Name:="test1", RefersToR1C1:= _


        "='CL Reserves'!R3C1:"&"&" c , r"


        


 ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:= _


        "='CL Reserves'!R3C1:R634C55"


        


End Sub

TIA

Graeme

Hi Bearcub,

If you want to create a pivot table goto
Data|Pivot table and Pivot table chart
and follow the wizard.

HTH

Graeme

Hi MoretoLearn,

Am I correct in assuming that you are wanting to use Excel VBA to manipulate the data that you have extracted from the Access Db?

If so you might want to try and do a search in the Excel/VBA forum for posts pertaining to Arrays, there are a number of posts on them.

Once the code has finished where would the results be reviewed in Access or Excel?

Graeme

Hi Wkalo,

Firstly welcome to the board...

What version of Excel are you using? if you are using XP goto Tools|Macro|Security and select the Trusted Sources tab on the form. Make sure that both checkboxes are checked. This should do the trick

HTH
Graeme

Hi Schnett,

Firstly welcome to the board...

Could you provide a little more information such as:
1.)What external program are you getting the data from?
2.)Is the data in the same format each time you copy it (i.e. are the column widths of the data the same length)

This has been done before but there are a number of people that should be able to help with a bit more info.

GraemeH

Dreamboat

It's 5.2Mb the reason for this is the amount of data that it's using to produce the results I need. The spreadsheet is a work around as the Essbase database that I am getting the info from does not have the data in the right structure.

Does anyone know of a way of stopping this message from appearing.

I have large spreadsheet with 4 tabs that each have exactly the same comboboxes 6 in total. each time I change one of the criteria in one of the comboboxes I get the following error: "not enough systems resources to
display completely"

I have checked the Knowledge base and it suggests setting all of the spreadsheets to the same zoom level but this does not seem to have worked as I am still getting the same error.

TIA

Graeme

Hi All,

Just a quick question...

I have a memory hog of a spreadsheet that currently uses sumif to collate data from a datasheet and I was wondering if I use Dsum would the workbook become more efficient?

Dave / Andy

Thanks for the advice but it looks like I am going to have to build an access database to get around my data consolidation problem.

Thanks once again.

Graeme

Dave,

it is possible but my pc starts to strain abit and then eventually dies on me I thought that this was a memory issue so I have upgraded my memory to 1024K and still the pc locks up. There are +/- 10 sheets with 250 cells that have links to 22 workbooks (55000 links).

Personally I would never link this many spreadsheets because of this very issue but I have been landed with this.

Andy,

I have tried to update the links and it appears that I can... I have also noticed that if I do not have all of the files open that I cannot see all of the links it appears that I have exceeded the 256 limit.

Graeme

Hi All,

I have just been landed with a problem that I need to come up with a solution rather swiftly....

The problem is:
I have several files (22 intotal) all linked to a central spreadsheet. In the Central spreadsheet the layout is exactly the same as the other 22 spreadsheets. The only problem is that in every data cell there is a link to the 22 spreadsheets.

What I need to do is change the links so that they look at files in another directory. there are only two changes in the link that needs to be made 1 is the subdirectory (change the year)has to be changed and 2 is the filename contains a year as part of the name.

I have tried to use update links but I keep on getting the following error "Formula too Long".

I have also tried to update the links by selecting an area and preforming a find and replace but I keep on getting the same error.

Anyone have any ideas...

PS. the files all are sitting on a shared server.

TIA

Graeme

Stan,

Firstly welcome to the board.

You can allow several users to have access write access to a file by using Tools >Share workbook, check the allow changes. This will allow the users access to the same workbook the only draw back is that only one user can edit a particular sheet at once. In addition there are some functions that are disabled when a workbook is shared.

HTH

Graeme

Ranger,
Try using
Sheet1.UsedRange("O1").Calculate

Graeme

Ranger,

Check that on the Security|Trusted Sources that the Trust Access to Visual Basic Project is checked. This may be what is causing your problems.

Graeme

Ranger
Instead of calculating just the range("o1") try using calculate on a separate line. to force the entire workbook to calculate.

HTH

Graeme

Hi Bo-knows,

Instead of concatenating the text that is being broken up by excel have you tried using paste special there are a number of options that might be of use.

Or you could try edit the cell before pasting the data.

HTH

Graeme