Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Adds values for a specific time frame Results

Hello,

I am having problems trying to figure out a formula that has a time delay based on an entry, and adds values for only 12 months after that.

If someone in our organization is hired in Jan, we have a savings for that person that comes into play starting 6 months later so in July. On the same line item we will have 1 person hired in Feb, so that savings starts in Aug. The savings only runs for 12 months for each person hired.

So if I assume a 100.00 savings per person and I hire 1 person in Jan 2012 and 1 person in Feb 2012, my outcome should show 0 savings from Jan 2012 thru June 2012, a 100.00 savings in July 2012, a 200.00 savings from Aug 2012 thru June 2013, a 100.00 savings in July 2013, and 0 savings from Aug 2013 onwards.

I hope this makes sense and someone can help me.

Hi all!

I am currently really stuck on a certain project of mine and I'm looking for a solution to make my life a lot easier. I am interested in calculating, from a pivot table, averages of certain values within a specific time frame.

For example:

I am interested in only the deals withing a month's timeframe (1st-28th/30th/31st), and for each of these deals that qualify I wish for excel to identify them and add all the costs and then divide by the quanitity (as to receive an average). Is this possible? Can excel identify monthly parameters and then proceed to add the costs of those who qualify and divide by the quantity? Any help would be greatly appreciated! Thank you very much!

Hi, All.
I have a userform with a single blank multipage. At runtime additional pages are added, the number of pages depends on input from another userform. Six frames, containing labels, textboxes and comboboxes are then added to each page.
I need to be able to use the textbox and combobox change events of these dynamically created controls to perform lookups and calculations. Although I can name the controls at the time they are created, it is not feasible to write code specifically for each control (I can have over 1,600 text boxes distributed over 9 pages, for example).
Having searched for some time on how to achieve this I believe using a class module is the way to go. However, how to use a class module is just not sinking in I'm afraid.

So far I have put the following code in a Class module called clsFormEvents:
HTML Code: 
Private WithEvents mpTextBox As MSForms.TextBox
Private WithEvents mpComboBox As MSForms.ComboBox

Private Sub Class_Initialize()
    Set mpTextBox = MSForms.TextBox
    Set mpComboBox = MSForms.ComboBox
End Sub

Private Sub mpComboBox_Change()
    MsgBox "ComboBox value has been changed."
End Sub

Private Sub mpTextBox_Change()
    MsgBox "TextBox value has been changed."
End Sub
In my UserForm module I have at the top:
HTML Code: 
Option Explicit

Private cConttbx As clsFormEvents
Private cContcbx As clsFormEvents
Where I create the controls is the following:
HTML Code: 
For r = 1 To 7
                    Set cContcbx = .Controls.Add("Forms.ComboBox.1", True)
                    CBNum = r + ((c - 1) * 7) + (MPVal * CBCount)
                    With cContcbx                  '   Comboboxes
                        .Name = "ComboBox" & CBNum
                        .Height = 0.1 * HeightFrameRow1
                        .top = 0.2 * HeightFrameRow1 + ((r - 1) * 0.1 * HeightFrameRow1)
                        .Width = (Controls("Frame" & 2 + (MPVal * FRCount)).Width - 100) * 0.48
                        .left = 5 + ((c - 1) * Controls("ComboBox" & 1 + (MPVal * CBCount)).Width)
                        .Font.Size = 10
                        .TextAlign = 1
                        .Value = ""
                    End With
                Next r
When this runs I get "Compile error: Method or data member not found", so there is something I'm missing in setting all this up.

I would really appreciate some guidance on how to use the class module to capture the change events of any instance of a specified control (specifically textboxes and comboboxes for my purpose).

Grateful thanks in advance.

I am building a excel solution that automatically analyses series of data from our support database (support ticket data) the column format is always the same but the amount of cases can vary vastly from about 50 to almost 10,000. Most of the tool is working great but I am struggling with a key feature that the remaining parts rely on.

I series of pivottables are used to analyse specific periods of time that the data represents. The start date and end date for those periods are entered by the user in a cell in the tab called [Setup]. Then a macro is run to adjust all the pivot tables dynamically to this date range. I am consistantly getting a errors when I try this.

The first attempt was to loop through the dates and turn visible off or on. THis worked most of the time but had issues with charts grouped by month. So after some reading I decieded to using pivotfilters.add would be better anyway. I looked up what the code should be plus recorded a macro doing it manual for sanity.

Now I a consistantly getting the error 1004 "Application-defined or object-defined error" when I try to add the filter. Below is the code

I am willing to pay $50 to get a working solution to this problem using pivotfilters.add or another 100% reliable method that fits into the soltution.


	VB:
	
 RebuildReports_Click() 
     
    Dim txtPivotName As String 
    Dim dteStartDate, dteEndDate As Date 
    Dim intProgStep As Integer 
     
     'Update Progress Bar
    Sheets("Setup").Select 
    Application.ScreenUpdating = False 
    intProgStep = (300 / 37) 
    UserForm1.Show 
     
    If ActiveWorkbook.Sheets("Setup").Range("L9").Value = "YES" Then 
        UserForm1.Caption = "Mapping Data" 
        txtFinalmsg = txtFinalmsg & " and used to scrub the data" 
         ' Standardize pServer Names (B-Y)
        Call ApplyMapData("Z", "B") 
        UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + intProgStep 
        DoEvents 
         
         ' Standardize pServer Releases (E-S)
        Call ApplyMapData("T", "E") 
        UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + intProgStep 
        DoEvents 
         
         
         ' Standardize Locations (H-L)
        Call ApplyMapData("M", "H") 
        UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + intProgStep 
        DoEvents 
         
         
         ' Standardize Frame Names (K-M)
        Call ApplyMapData("N", "K") 
        UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + intProgStep 
        DoEvents 
         
         
         ' Standardize Root Causes (N-AC)
        Call ApplyMapData("AL", "N") 
        UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + intProgStep 
        DoEvents 
         
         
         ' Standardize PAN Versions (Q-R)
        Call ApplyMapData("S", "Q") 
        UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + intProgStep 
        DoEvents 
         
         
         ' Standardize Failure Analysis (T-AA)
        Call ApplyMapData("AB", "T") 
        UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + intProgStep 
        DoEvents 
         
    Else 
        UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + (7 * intProgStep) 
        DoEvents 
    End If 
     
    dteStartDate = Range("C8").Value 
    dteEndDate = Range("C9").Value 
     
    Sheets("Pivots").Select 
    ActiveWorkbook.Sheets("Pivots").Range("JB9").Select 
    Selection.Ungroup 
     
    Call ClearPivotItems 
     
     'Update Progress Bar
    UserForm1.Caption = "Tabulating Data" 
    UserForm1.ProgressBar.Width = UserForm1.ProgressBar.Width + intProgStep 
    DoEvents 
     
    Sheets("Pivots").Select 
    ActiveWorkbook.Sheets("Pivots").Range("A1").Select 
     
    For ii = 1 To 28 
        txtPivotName = "PivotTable" & ii 
         'ActiveSheet.PivotTables(txtPivotName).ManualUpdate = True
         
         'Range("JB11").Select
         'ActiveSheet.PivotTables("PivotTable15").PivotFields("Date Submitted"). _
         'PivotFilters.Add Type:=xlDateBetween, Value1:="1/1/2008", Value2:= _
         '"1/1/2009"
         
        With ActiveSheet.PivotTables(txtPivotName).PivotFields("Date Submitted") 
            .PivotFilters.Add Type:=xlDateBetween, Value1:="1/1/2008", Value2:="1/1/2009" 
             
             '.AutoSort xlManual, "Pivot Class"
             'For i = 1 To (.PivotItems.Count - 1)
             '        If .PivotItems(i).Value >= dteStartDate And .PivotItems(i).Value

Nowadays, people can enjoy videos in different ways, such as youtube.com and other online video websites, iPod/Zune and other portable players, and also play video on computer, and you can also make you own videos with windows movie make or iMovie for Mac users. So how to convert videos between different video formats is a big problem for both windows and Mac users. Today I will share you guys two powerful video converter for Mac and Windows users.

For Mac users, this powerful video converter for mac is really nice.
Let's use it to convert a video for Example

http://www.aiseesoft.com/images/guid...rter/steps.jpg

Step 1: Add File
Click “Add File” button to load the video you want to converter

Step 2: Choose profile and adjust settings
You can find your output video format from a drop-list called “Profile”
You can also adjust the specific settings of the output video, such as “Resolution”, “Bitrate”, “Encoder” and so on.

Step 3: Edit Video
1. Effect:
You can adjust the “Brightness”, “Contrast”, “Saturation” and “Volume”
http://www.aiseesoft.com/images/guid...ter/effect.jpg
2. Trim:
You are allowed to pick up any part of the video to convert.
http://www.aiseesoft.com/images/guid...erter/trim.jpg
3. Crop:
To remove the black edges and choose a play area to convert.
http://www.aiseesoft.com/images/guid...erter/crop.jpg
Step 4: Start Conversion
Click “Start” button to begin your conversion and you will get your video soon.

For Windows users, you need Total Video Converter.

http://www.aiseesoft.com/images/guid...screen-new.jpg

Step 1: Load Video
You can load your videos into the converter by clicking “Add File”

Step 2: Choose output info
This converter provides you many output profiles for you to choose, you can choose your output profiles according to your need. And also you can just choose a certain video format.

You can also adjust the settings of your output profiles. You can even save a certain settings as your own output settings.

http://www.aiseesoft.com/images/guid...ttings-new.jpg

Step 3: Conversion
Click “Start” button to begin your conversion and few mins later you can get your video.

Tips: How to effect, trim and crop the video to improve your output video quality.
After your Load your video you can click “Effect” button to access the effect panel

1: Effect
Adjust the video effect as you want: Brightness, Contrast, Saturation and Deinterlacing.

http://www.aiseesoft.com/images/guid...effect-new.jpg

2: Trim
Clip your video, pick up any part of your video to convert.
You have 3 ways to do trim:

1.Dragging the slider

2.Controlling the “Trim From” and “Trim To” button

3.Setting the Start and End time

http://www.aiseesoft.com/images/guid...r/trim-new.jpg

3: Crop
Adjust video size, Cut off the black edge of your video to let you enjoy your movie in full screen by click the 'Crop' button
You have 2 ways to do crop:

1.Move your mouse cursor onto one of the eight small adjustment boxes on the cropping frame and drag the crop frame to crop the video such as cutting off top and bottom black bar. The cropped result will be shown on the Output Preview at the same time

2.Type the value directly or set the value in "Left", "Right" and "Cropped area size" area to set the position of the cropping frame.

http://www.aiseesoft.com/images/guid...r/crop-new.jpg

You can also select the aspect ratio from the Zoom Mode drop-down list. There are 4 choices for you: Full Screen, 16:9, 4:3 and Keep Original.

MTS Converter | iPhone Ringtone Maker | Hulu Downloader


No luck finding an answer? You could always try Google.