Posts by markc

Hi all
I opened an Excel attachment from Outlook
I thought I saved it on my drive

I made lots of updates that took me 1 hour
I thought I saved and closed
I looked in the directory where I thought I save it - but nothing is there.

I checked my recent files in Excel - nothing.
I checked my recent files in Windows - nothing.

Is there any way I can get this file back?

Thanks
-marc

Re: Custom View Disappears From Menu Bar

Thanks.
Didn't do anything but a few more re-boots
Seems to be sticking now.

Thanks
-marc

Re: Copy/paste Formulas Only

Thanks Flee-

After the UDF is I sort aren't my frmlas invalid or must they use absolute reference?

Thanks
-marc

Hi All -

I added Custom View to right of Help on the Menu Bar.
Works great
Until I close Excel and later re-open Excel
The custom toolbar is no longer available
I have to drag it to the menu bar each time

Is there a way to make Custom View available in this position every time I open Excel?

Thanks
-marc

Re: Copy/paste Formulas Only

Thanks Roy
Attached is a sample worksheet
I added entered 2 values and a formula summing the 2 values
I copied all 3 cells
I moved over 1 column
I used pastespecial formula
The 2 numbers were pasted as well as the formula
I need the 2 values to be skipped

This is a very simple example.
The actual workbook has over 40 rows with close to 20 formulas in each column and lots of hard inputs interspersed

Thanks
-marc

Re: Copy/paste Formulas Only

Thanks Roy -

That picks up hard coded values and pastes them over the current value
I need to be able to skip the hard inputs

Thanks
-marc

Hi all

I have setup a forecast model and now find it necessary to update many formulas

I have set period 1 formulas correctly and would like to copy through periods 2-12. However each period has already been populated with data that need to remain intact in this process

For example,
I have formulas to copy at I5 and I9. I6,7,8 should be skipped

These formulas should be pasted to K5 and K9 respectively, while leaving K6,7,8 intact

This is of course a simple example, the column contains 15 formulas to be copy/paste

Thanks much
-marc

Re: Last Non Zero Value In Range

Thanks Tom

Works great!!

Thanks
-marc

Hi all -

I need to get the right most non zero value from 5 columns (D:H)
So starting at H and working backward
If H5 <> 0 Then C5 = H5
ElseIf G5 <> 0 then C5 = G5
ElseIf F5 <> 0 Then C5 = F5
ElseIf E5 <> 0 Then C5 = E5
ElseIF D5 <> 0 Then C5 = D5

But I'm not sure how to write a formula to achieve this.
Thanks
-marc

Re: Count With Sumproduct And 3 Criteria Range

Thanks guys -

Shades the formula did the job

Thanks
-marc

Hi all

I am trying to count some data I stripped from SAP General ledger
I need to count a record if
Unit
Within range of GL Codes
Within date range

Here is the sumproduct formula I came up with
=SUMPRODUCT(--(Data!$A$2:$A$209=$A11),--(AND(Data!$C$2:$C$209>=$B$1,Data!$C$2:$C$209<=$B$8)),--(AND(Data!$J$2:$J$209>=$B$10,Data!$J$2:$J$209<=$C$10)))

Where
Data! Contains the data to be analyzed
$A is the unit number(s)
$C is the GL Codes
$B1 is the lowest GL code to be incl
$B8 is the highest GL code to be incl
$J is a range of dates
$B10 is lowest date to be incl
$C10 is the highest date to be incl

Currently the formula returns a #VALUE! error
Where did I go wrong?

Thanks
-marc

Re: Indirect Function Inside Sumproduct Formula

Thanks Bob -

I will add the dynamic named ranges for the criteria and the range to sum
But I believe I still need to use indirect to allow the user to choose which table (sheet) is to be presented.

Unless there is a way around this?

Thanks
-marc

Re: Indirect Function Inside Sumproduct Formula

Thanks for the help guys.
Both solutions worked equally well
But I like the latter a little better as the end range could grow in the future

I guess I need to loop through each data sheet and get the end row nad use the maximum value in the frmla.

Thanks!
-marc

Hi all -

I am attempting to build an interactive user-app to let the user choose a table name and update P&L values based on selection.

Assumptions
Start: Row 2
End: Row 2831 (Some tables less, this is max size table)

Here is my current frmla
=SUMPRODUCT((INDIRECT($B$1&"!"&$A$2:$A$2831)=B$8)*(INDIRECT($B$1&"!"&$C$2:$C$2831)=$A9)*(INDIRECT($B$1&"!"&$D$2:$D$2831)))

Where:
B$8 is comparing the Unit number
$A9 is comparing descriptive label
$D$2:$D$2831 is range of values to be summed if 2 conditions are met

This frmla is currently returning a #REF! error

So then I tried re-writing the Indirect arg's by placing the COl/Row Ref's in cells

=SUMPRODUCT((INDIRECT($B$1&"!"&$B$3&$B$4)=B$9)*(INDIRECT($B$1&"!"&$C$3&$C$4)=$A10)*(INDIRECT($B$1&"!"&$D$3&$D$4)))

This is also returning a #REF! error

I attached a small sample file
I had to trim all data sheets out but 1
I had to trim out all data but 2 units
Should be enough to test with though

Thanks much
-marc

Re: Select Single Cell After Paste Large Range

Hi all -

Determined I need to activate the sheet before I can select a range (cell) on the sheet.

wsDestPage.Activate
                    .Range("A6").Select

This works and removes the blue shading from the pasted area.
But on every sheets a cell in row 10 is selected not row 6
Usually in ColA but I have one sheet with Col's A & B hidden so should select in Col C, but E10 is selected instead.

It's not a big problem, just an oddity that doesn't seem to make sense.

Thanks much
-marc

Re: Select Single Cell After Paste Large Range

Bump -

Hi all -

With the current code, the range remains selected and "greyed"
What can I do to remove this?
See my code snip in prior post

Thanks
-marc

Re: Select Single Cell After Paste Large Range

Thanks
I added application.cutcopymode = false
No help. Most likely I used incorrectly

code below
Thanks
-marc

Option Explicit


Sub comp_CreateVPReport()
    Dim wbBook As Workbook
    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim wsData As Worksheet
    Dim wsSrcPage As Worksheet
    Dim wsDestPage As Worksheet
    Dim sht As Worksheet
    Dim ws As Worksheet
    Dim rngSource As Range
    Dim rngData As Range
    Dim rngCrit As Range
    Dim rngDest As Range
    Dim lngRows As Long
    Dim lngSourceRows As Long
    Dim Msg As Long
    Dim lngResponse As Long
    Dim strSource As String
    Dim strDest As String
    Dim strPath As String
    Dim strDM As String
    Dim i As Integer
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .CutCopyMode = False
    End With


    strSource = "C:\Data\Monthly Report\Monthly_Report_Tmplt.xls"
    strDest = "C:\Data\Monthly Report\Monthly Report-VP.xls"
    strPath = "C:\Data\Monthly Report\"
    
    Set wbBook = ThisWorkbook
    Set wbSource = Application.Workbooks.Open(strSource)
    Set wbDest = Application.Workbooks.Open(strDest)
    Windows("Monthly Report-Process.xls").Activate
    
    'Part E - Uses Col E to determine range to copy


        'Sheet : Revenue Plan
            'Source Range
                Set wsSrcPage = wbSource.Worksheets("Revenue Plan")
                lngSourceRows = wsSrcPage.Range("E65536").End(xlUp).Row
                Set rngSource = wsSrcPage.Range("A5:G" & lngSourceRows)
                rngSource.Copy
            
            'Destination Range
                Set wsDestPage = wbDest.Worksheets("Revenue Plan")
                Set rngDest = wsDestPage.Range("A5")
                With rngDest
                    .PasteSpecial (xlPasteValuesAndNumberFormats)
                    .PasteSpecial (xlPasteFormats)
                    Application.CutCopyMode = False
                End With

Hi all -

I would like to select cell A6 after pasting a large range so that when send to user the entire range is not selected

I tried

wsDestPage.Range("A6").Select

And

wsDestPage.[a6].Select

I receive

Quote

Select method of range class failed

How can I select a single cell after pasting the large range?

Thanks
-marc

Re: Autofilter Mode On

Thanks guys
That did the job
Appreciate the help!
-marc

Hi all -

I am trying to turn autofilter on for every sheet in the workbook except "Homepage"

I thought this code would do it

For Each ws In wbDest.Sheets
            If ws.Name <> "Homepage" Then
                ws.AutoFilterMode = True And ws.FilterMode = True
            End If
        Next

No error messages are received.

But when I open the file the filter arrows are not visible
Can you please tell me what I'm doing wrong?
Thanks
-marc