Posts by Robert B

Robert B · Jun 30th 2005

Hi

is there a way of adding a trendline to a series plotted on a secondary axis?

The standard method displays the trend on the primary axis and is invisible due to the disparity of the 2 scales.

Robert

Robert B · Jun 29th 2005

Re: Selection of a linked Chart

Thank you both for your suggestions. I wanted to avoid both asking the user to may any decision beyond selecting his area of interest, and allowing him access to the base data.

I am attempting to solve this by using a combo box to select the category and then running a macro which selects the relevant chart by using Select Case on the Combo Box input which then open the file as an embedded object.

So far it seems to fit the bill

Regards

Robert

Robert B · Jun 27th 2005

Hi

I am creating a report which summarises activity over 15 or so different areas, 1 page per category. In each page I have 4 charts which are naturally fairly small so I have created link each chart to another file in which a larger and more detailed chart resides.

What I would like to do is to create a single page with a list box so that the user can select a particular area thus displaying the data relevant to his selection, so far so simple, however I cannot see a way making the displayed graphs dependant on the selection of the category.

Can anybody help?

Regards

Robert

Robert B · Jun 20th 2005

Re: Unrecognisable Folder

Hi Barry,

Yes, the folder is on a network, my temporary solution has been to create a new folder, copy everything in the existing folder except the offending folder, rename the old folder "Rubbish" and name the new folder as the original. Its still there, but out of sight and, so, out of mind.

Someday I'll get the IT guys to blat it.

Thanks for your suggestions.

Regards

Robert

Robert B · Jun 20th 2005

Hi all

I have a very annoying folder that was created during an aborted macro run. This folder, I am told by Windows, Cannot be deleted, moved or renamed: cannot read from source file or disk.

The macro that created this folder has run several times since with no unusual occurences.

Can somebody suggest a means of purging this file from my system?

Many thanks

Robert

Robert B · Jun 17th 2005

Re: List Sheets in Workbook

Many thanks Roy

Robert

Robert B · Jun 17th 2005

Hi

I remember seeing a piece of code which produced an index of the sheets in a workbook, with hyperlinks to each sheet, can anybody direct me to the source for this code? Similarly, is there some code which can list workbooks in a folder also with hyperlinks?

Thanks

Robert

Robert B · Jun 15th 2005

Re: Nested IF Formula

Thanks, Alastair, the = sign was a typo but the additional brackets solve my problem

Regards

Robert

Robert B · Jun 14th 2005

Hi

I have this formula, which works fine

=IF($D7="OPFUP",VLOOKUP($B7,April!$A$5:$F$37,6,0),IF($D7="OPFA",VLOOKUP($B7,April!$A$5:$F$37,5,0),IF($D7="NEL",VLOOKUP($B7,April!$A$5:$F$37,4,0),IF($D7="EL",VLOOKUP($B7,April!$A$5:$F$37,3,0),VLOOKUP($B7,April!$A$5:$F$37,2,0)))))

what I would like to do is return 0 on error, so I tried

=IF(ISERROR(IF($D7="OPFUP",VLOOKUP($B7,April!$A$5:$F$37,6,0),IF($D7="OPFA",VLOOKUP($B7,April!$A$5:$F$37,5,0),IF($D7="NEL",VLOOKUP($B7,April!$A$5:$F$37,4,0),IF($D7="EL",VLOOKUP($B7,April!$A$5:$F$37,3,0),VLOOKUP($B7,April!$A$5:$F$37,2,0))))),"0",=IF($D7="OPFUP",VLOOKUP($B7,April!$A$5:$F$37,6,0),IF($D7="OPFA",VLOOKUP($B7,April!$A$5:$F$37,5,0),IF($D7="NEL",VLOOKUP($B7,April!$A$5:$F$37,4,0),IF($D7="EL",VLOOKUP($B7,April!$A$5:$F$37,3,0),VLOOKUP($B7,April!$A$5:$F$37,2,0)))))

but this fails. I am obviously making a very basic mistake, but can't spot my error.

Any help appreciated

Robert

Robert B · Jun 8th 2005

Re: Sheet Number

Thanks v much, Filo thats a great help

Robert

Robert B · Jun 8th 2005

Hi All

I would like to be able to set an active sheet as an integer so that e.g.

n = ActiveSheet.Number

this obviously fails but I am sure there is a way, can somebody help?

Regards

Robert

Robert B · Jun 7th 2005

Re: VBA code to produce a list of the duplicates in a column

Hi Marcus

You could create a Pivot Table using Col A as data, paste the resultant Row headings into Col C and delete the Pivot.

Hope this is useful
Rob

Robert B · Jun 6th 2005

Re: Complete Border Round Cell

Thats great thanks v. much, just what I needed

Regards

Robert

Robert B · Jun 6th 2005

Hi All

Is there a piece of code that will put a border all round a cell, other than using four bits of code, one for each edge?

Thanks

RB

Robert B · May 26th 2005

Re: Creating File Reference in VBA

Thanks Norie,

I tried it with the &s and with the "s but not with both.

thanks again

Robert

Robert B · May 26th 2005

Re: Creating File Reference in VBA

What I am trying to do is set myFolder as a variable which is allocated the name established in the myFolder = ActiveSheet.Name statement.

The Locals window seem to indicate that is happening.

It obviously isn't, so where am i going wrong? Should the Filepath be constructed differently?

Thanks again
Robert

Robert B · May 26th 2005

I am populating a summary spreadsheet from a number of other spreadsheets which are located in a location such as
Z:\Folder\Foldera\Folderb\TargetFolder\filename.xls.

I would like to add new folders for monthly data so that the path becomes

Z:\Folder\Foldera\Folderb\TargetFolder\CurrentMonth\filename.xls.

However when I use

Code

dim myFolder as string
myFolder = activesheet.name ' This is the current month on the summary


Range("A1") "=Z:\Folder\Foldera\Folderb\TargetFolder\myFolder\[filename.xls] _!Sheet1'Range("A1")"

the macro looks for a folder called myFolder, which doesn't exist.

I am probably doing something really dumb, but can someone help

Thanks

Robert B · May 12th 2005

Hi all

I have 2 questions regarding Pivot Tables.
1)Given the following code

Code

ActiveSheet.PivotTableWizard _ 
	SourceType :=xlDatabase, _
	SourceData :=myRange, _
	TableDestination :="", _
	TableName :="PivotTable2"

How do I define the destination to be on a specific worksheet at, say, cell (“B5”).

2) I would like to add an additional column called “Variance” which would show the percentage difference between the successive cells in the preceding column

e.g
RowField Data
Total Variance
January 100 -
February 110 +10%
March 99 -10%

Hope this makes sense
Thanks

RB

Robert B · Feb 7th 2005

Re: Dynamic date as criterium in Auto Filter

Thanks Andy &Will

I amended the code as suggested but still failed to extract the data I am expecting; nothing is returned. The odd thing is that when I pause the macro and custom filter the column, myDate in dd/mm/yyyy format defaults in the selection box and the expected records are returned.

Will, I would love to know the option indicated by your aside.

Regards
Rob

Robert B · Feb 7th 2005

Hi

I want to select a range of data based upon a variable date which is stored on a worksheet.

This is an extract of the code I have created.

Code

Dim myDate as String
Application.Goto Range("A2") 'this is where the date is stored
myDate = ActiveCell
Sheets("2).Select
Columns("H:H").Select
Selection.AutoFilter Field:=1, Criteria1:= ">=myDate", Operator:=xlAnd

This does not produce the expected result, can somebody see what I am doing wrong? I should say that the Locals window indicates that the date has been stored as required.
Thanks

RB