Posts by markc

Re: Object visible on mouse hover (click)

Thanks Roy

How did you determine the name of the object?
I've looked everywhere I can think of, and cannot find it.

Thanks
-marc

Hi all -

I would like to toggle visibility of some connector arrows I put
into my spreadsheet

Can I do this with a mouse hover action?
Alternatively, click?

What is the syntax to refer to the object?
I searched and could not find anything.

Thanks
-marc

Re: update formula to last row

Thanks TJ

Perfect.

Why just column A though?
Just for my knowledge.

Thanks
-marc

Re: update formula to last row

Thanks TJ

I've attached an example of what I'm trying to do
It works with conditional sum wizard

But when I try same with the dynamic range names
I get a #REF! error.

Thanks
-marc

Re: update formula to last row

Thanks TJ -

I thought about dynamic ranges on the morning walk for coffee
I guess I sometimes post before I completely think through
what I am trying to do.

I am having a problem setting up the DR formula to suit however

[highlight]
=OFFSET($L$136,0,0,COUNT($L136:$L),1)
[/highlight]

Returns "Formula contains an error" highlight on last $L
The data under examination begins at row.136

Thanks
-marc

Hi all
Using xl 2003

How do I would I re-write my formula to reference ever expanding data
No blanks ever.

Here's my current array [ctrl+shift+enter, of course]

[highlight]
=SUM(IF($L$136:$L$600=C$27,IF($A$136:$A$600=$A28,$J$136:$J$600,0),0))
[/highlight]

Everything is good except the refernce to row600
At the moment need 633, but tomorrow will need more

Thanks
-marc

Re: Code execution is slow

Norie / Richie -

Thanks.
About 1 Billion times faster

Thanks
-marc

Hi all
Using xl 2003

System
Win xp pro
HP Pavilion zx5000
P4 (3.06GHz)
1.0GB Ram

This code is very slow to run

Sub Clear_Sheet_Data()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual


    Worksheets(1).Activate
        Call Clear_Data
    Worksheets(2).Activate
        Call Clear_Data
    Worksheets(3).Activate
        Call Clear_Data
    Worksheets(4).Activate
        Call Clear_Data
    Worksheets(5).Activate
        Call Clear_Data


    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub


Sub Clear_Data()
    Range("A2:A65536").Select
    Selection.ClearContents
    Range("A2").Select
End Sub

Any ideas?
Thanks
-marc

Hi all

Using mixed xl enironment 2000-2003

Need to plot customers and employees
both vertical column

Against times along the bottom (x - axis)

I've tried several different approaches
Can't seem to figure it out

See attached book

Thanks
-marc

Re: Macro attached to main xl menu

Thanks Viper / Stapuff -

I want the macro always avail
Even if no workbook is open (such as personal.xls)

For some reason my personal.xls no longer opens when I start xl
Not a problem as long as I can quickly call it up from the menu or standard toolbar.

Thanks
-marc

Hi all -

Can I attach a macro to the main xl menu?
I want the macro always avail
Does it have to be attached to a workbook?
Can it somehow be attached to the .xlb?

Thanks
-marc

Re: Copy a formula to a range (reworked)

Norrie / Bill -

Thanks.
As always, I am humbled by your excellence and willingness to help.

I can only hope that one day I can return the favor to others.
Thanks
-marc

Re: Copy a formula to a range (reworked)

Thanks Roy -

I really think for troubleshooting and future developments
the past practice is to copy from a Formula sheet
to the required range

This way a user or I could edit the necessary formulas on a worksheet
rather than digging thru code to edit the correct formula.

My latest code is giving me
Run-time error '1004':
Select method of Range class failed

Debug highlights here:

Selection.Copy

Don't know why

Full revised code below
Thanks
-marc

Sub CopyFormulas2()
    Dim myRange As Range
    Dim cRange As Range
    Dim lngRows As Long
    Dim i As Long           'counter
        
        Set cRange = Worksheets("Formulas").Range("O16")
        Set myRange = Worksheets("Order_Nmbrs2").Range("P17", Range("P65536").End(xlUp))
        lngRows = myRange.CurrentRegion.Rows.Count


        Worksheets("Formulas").Range("O16").Select
        Selection.Copy
        
        For i = 0 To lngRows
            Worksheets("Order_Nmbrs2").Range("P17").Offset(i, -1).Select
            Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
            i = i + 1
        Next i
        Worksheets("Order_Nmbrs2").Range("O16").Select
End Sub

Hi all
Using xl 2003.

Am reworking my approach to copy a single formula to a range of unknown # of rows, 1 Col.

Formula must update for current row (i.e. row 17 formula ref row 17, etc...)

New code below gives "PasteSpecial method of Range class failed"
I suspect because my copy is much earlier than the paste.

Could someone please nudge me in the right direction?

Thanks
-marc

Sub CopyFormulas2()
    Dim myRange As Range
    Dim lngRows As Long
    Dim i As Long           'counter
        
        Worksheets("Order_Nmbrs2").Range("O16").Copy
        Set myRange = Worksheets("Order_Nmbrs2").Range("P17", Range("P65536").End(xlUp))
        lngRows = myRange.CurrentRegion.Rows.Count
        
        Worksheets("Order_Nmbrs2").Range("P17").Select
        For i = 0 To lngRows
            Range("P17").Offset(i, -1).PasteSpecial xlPasteFormulas
            Application.CutCopyMode = False
            i = i + 1
        Next i
        Worksheets("Order_Nmbrs2").Range("O16").Select
End Sub

Re: Variable range

Thanks Will

Is there a more esoteric way to do this?
What if I or other user want to change the formula
in the future?

Wouldn't it be better to store the formula on another sheet
and then copy the formula from Sheet("Formula") to Sheet("Analyze")

Or could I alter this

c.Offset(0, -1).Formula = _

to

c.Offset(0, -1).Formula = Formula!$A$1.Formula

Thanks
-marc

Re: Variable range

Thanks Roy
Worked great - as I'm sure you knew

My next problem is thr row reference in the formula does not update to the current row.

How do I make the row reference the current row?

Thanks
-marc

Hi all
Using xl 2003

Guess I do not know how to specify a variable range
I want to start at P17 and continue until blank cell in Col P

Can someone tell me th correct syntax please?

Thanks
-marc

Sub CopyFormulas2()
    Dim lRow As Range
    Dim myRange As Range
    Dim c As Range
    
        'Range("P65536").Select
        'Set myRange = Range(Selection, Selection.End(xlUp)).Select
        'Set lRow = Range("P65536").End(xlUp)
        'Set myRange = Range("P17:lRow")
        'Set myRange = Range("P17" & lRow)
        'myRange.Select
        Set myRange = Range("P17").End(xlDown)
        
        For Each c In myRange
            If c.Value <> 0 Then
            c.Offset(0, -1).Formula = _
            "=(s17*t17)+(s17*u17)"
            Else: Exit Sub
            End If
        Next c
End Sub

Re: A different scenario manager

Thanks Pangolin
Way too awesome
As always, I am humbled by this forum

Thanks again
-marc

Hi
Using xl 2003

I would like to develop many options beginning at row 18
Then row.19 would offer different options etc

User should be ablt to enter row at B2
The sample shows 5 col's of data, but in the end
it will be 52 weeks

I was thinking of concatenation or something
but not sure because don't know how to update col reference

Sample attached
Thanks
-marc

Re: sum single occurrence from column criteria other column

Thanks Norie-

I finally got it.
sumif/countif

=SUMIF($O$12:$O$1837,$A12,$Q$12:$Q$1837)/COUNTIF($O$12:$O$1837,$A12)

=40/4
=10

Where Col O is range of order # details
Col A is single occurence of order #
Col Q is is Delivery

Thanks
-marc