Posts by Robert B

Re: Referencing an External Workbook

Thank you both for your help. Whenever I try to replace the sheet name with 'Sheet1' I get an error, either a "compile error Expected: end of statement" or Object required if I stick a ! in.

Whilst the version with the full sheet name works perfectly, I would really like to use the sheet number as people seem to have the habit of, very subtly, changing the names on the sheets of their workbooks, e.g. an extra space or the omission of a period.

Thanks again for your interest

Robert

Re: Referencing an External Workbook

Thanks Roy

In the example I posted :

ActiveCell.Offset(0, 2).Formula = "= myPath & " [RemoteFile.xls ] Weekly!$I$37"

given that "Weekly" is Sheet 1, would the format be

ActiveCell.Offset(0, 2).Formula = "= myPath & " [RemoteFile.xls ] Sheets(1)!$I$37"?

Robert

Hi All

I am having a little difficulty in correctly formatting a macro statement which I want to use to insert a formula into a specific cell. The source cell will change on a weekly basis but will be consistently filed and formatted.

This is the statement I am trying to use

ActiveCell.Offset(0, 2).Formula = "= myPath & " [RemoteFile.xls ] Weekly!$I$37"

where myPath is the full location of the RemoteFile and Weekly is the Sheet Name

I have tried all manner of combinations of "s and 's but I either come up with an error message or I get the text of the formula in the target cell.

Incidentally, is it possible to refer to the sheet by Index number, rather than by name?

Thanks

Robert

Re: Calling Named Ranges in Macro

Thanks for that, Batman, itwas extremely informative.

I know you will smile disbelievingly when I tell you that Range("Name").Select, which I tried some hours ago, and which failed, has now deceided to work.

We certainly live ininteresting times, eh?

Regards

Robert

Re: Calling Named Ranges in Macro

Hi Batman

The use of "Name" was intended to indicate every name that exists in the workbook, there are 6.

Does that mean I would have to declare each name as a string, if so, how would VB recognize the range to which it referred.

Robert

Re: Calling Named Ranges in Macro

Hi Batman,

As the code works perfectly when the range is hard coded, I supect that the error lies within the format of the statement.

I have, temporarily and unsatisfactorily, resolved the problem by using

Application.Goto ("Name")

thanks again for your interest

Robert

Re: Calling Named Ranges in Macro

Hi Batman

thanks for your input. I had tried that but received this error message

"Compile error:

Wrong number of arguments or invalid property assignment"

Does there need to be a Dim statement so that VBA can recognize the fact that named ranges exist in the target workbook?

Robert

Hi All,

I have looked at many of the past posts on this topic, but none seem to solve my difficulty, or. more probable, I have'nt understood the answers.

I have a workbook which contains 3 named cells, each of which I would like to access via a macro which updates their contents.

I assumed that

Range(Name).Select

would suffice, obviously incorrectly.

How can I select these cells?

I am sure someone can tell me where I am going wrong

Robert

Re: Updating Dates in Queries

Thanks for all your assistance.

I did attempt to adapt Bnix's function, failed to make it work, ran out of time and resorted to a nested Iif expression, which is clumsy but achieves the desired effect.

thanks again

Robert

Re: Updating Dates in Queries

Thanks for that, Turbo. I am still a little confused.

I currently have, say

StartMonth 01/05/2005
EndMonth 31/05/2005

and what I want is, say

StartMonth 01/06/2005
EndMonth 30/06/2005

I can simply update StartMonth by

StartMonth = EndMonth+1
The problem arises with the EndMonth date as it has no relationship with either the new StartMonth or the old EndMonth, e.g if the old Month is January then new EndMonth = old EndMonth + 28 (or 29 if a leap year, but lets not go there), if old Month is February then new EndMonth = old EndMonth + 31.

I could solve this by using a nested Iif staement, but hoped there was something simpler.

Thanks for the scheduled run advice.

Robert

Hi all

I have inherited a database, which has a number of queries that are run monthly. Each query was designed so that the start and end dates were prompted for. I have created a table, containing dates, and linked that to each of the queries, so that dates are entered only once. I have also written a simple macro, which runs these queries.

I would like to create a query, which updates the dates table, the start date is easy enough but how can I calculate the month end date. Also is it possible to run the macro on a predetermined date, or after a set number of days since the previous execution.

Thanks

Robert

Re: Using Cell Value as sheet selection parameter

Hi Dave

thanks for your help on this. Unfortunately the target workbooks will not necessarily be open, and the sources are manifold. The only solution I can see is to manually change the 20 or so occurences of the month each month using Find/Replace.

Thanks again

Robert

Hi

I have a spreadsheet which uses the content of a cell to look up values on another workbook. This workbook is created monthly producing a pivot table which then shows pages for each month.

I have a number of cells which contain this formual

=VLOOKUP($D$2,'TargetWorkbook.xls]May'!$A$1:$H$30,8,0)

Is it possible to replace the target sheet name with a reference so that, each month, only one cell in the spreadsheet needs to be changed to update all references.

Thanks

Robert

Re: Naming/Deleting OLEObjects

Hi Batman

Thanks for your input.

I actually solved the problem, well, got the result I wanted, which is probably not the same thing, by using the following

myCellB.Select
    ActiveSheet.OLEObjects.Add(Filename:= _
        myChartB, Link:=True, DisplayAsIcon:=False).Select
      Selection.ShapeRange.ScaleWidth 1.75, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 1.1, msoFalse, msoScaleFromTopLeft
            Selection.Name = "ObjectB"

This was achieved by a combination of trial and error, macro recording and sheer blind faith.

Thanks again for your thoughts

Robert

Re: Dim as Percentage

I find that Dim as Variant produces the expected answer

Thanks again

Robert

Re: Dim as Percentage

Thanks for that Spog, I no longer get a failure, but nor do I pick up the value I am expecting. The target cell contains a sum formatted as a percentage, I am wondering if Dim IPercent as Integer is correct, do you have any comment

Robert

Hi

I am attempting to find a value in a dynamic cell.

I have produced the following

Dim r As Integer
Dim c As Long
Dim IPercent As Integer


    Cells.Find(What:="String", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=True).Activate
        r = ActiveCell.Row
        c = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
       
        IPercent = Range(RrCc)

which produces an error on the last line. I cannot see where the error, or errors, lies.

Can someone assist?

Thanks
Robert

Hi

I have a spreadsheet which displays 3 charts depending on a selection made in a combo box. Each time the selection changes another OLEObject appears on top of the previuously selected chart. This has the effect of there being a larger number of charts to update following each successive selection.

Is it possible to give each object a name so that a new selection will delete the existing objects before displaying new objects.

I hope this makes sense.

Robert

Re: Trendlines on Charts

Thanks Andy that's perfect

Robert

Re: Trendlines on Charts

Hi Andy,

I am working in Excel97, does that make a difference? I have attached a sample of the files with which I am having problems.

Thanks

Robert