Free Excel\VBA Help Forum

Formula which can calculate how many days in a month and create a column for each day

moshjosh — Wed, 16 May 2012 22:16:21 GMT

Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name �LARSheet1 to 12� ]

I�m trying to build a �Leave, Attendance Management� system using Excel�. I am about 40% there so far.

I have a few problems to tackle which I will post in separate treads and link them back to together; so others can hopefully learn from the help I receive (and my mistakes):

The basic structure of the workbook is as follows:

12x main monthly �Leave Attendance Record� worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets as LARSheet1 to LARSheet12].
1 x worksheet which holds �data validation� list andvlook up info. [�FormulaListSheet�]
1 x worksheet acting as a database of staff details andpay numbers. [�StaffdBaseSheet�]
1 x summary page which shows staff reaming leaveallocation. [�SummaryLeaveSheet�]

What I�m having problems with at the moment is as follows:

A formula or a macro which works out how many days are present in a given month [starting date of month located in cell B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).

Hopefully I would like it to work like this:

When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.

I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.

Can anyone help? Cheers moshjosh!

Sample of sheet setup:

screenshot_LARSheet1.jpg

====== ====== ======== ====== ============= ========== ============ ============ ===== ========= =======

This is a multi-part project which is collectively known as �Leave, Attendance Management� system using Excel� . Other parts are found on other treads. I hope to link the thread solutions together so others who wish to do a similar task can hopefully learn from help I receive (and my mistakes).

The other parts of the system are:

�Leave attendance record� (this thread) worksheets (i.e months April 2012 through to March 2013). [I will refer to these sheets as "LARSheet1" to "LARSheet12" ].

�Data validation� worksheet which holds list and vlook-up info for formulas. [I will refer to these sheet �FormulaListSheet� ].

�Database� worksheet acting as a of staff details database. [ �StaffdBaseSheet� ]

�Summary reaming leave� page which shows a summary of staff used leave and calculates reaming leave allocation. [ �SummaryLeaveSheet� ]
http://www.ozgrid.com/forum/showthread.php?t=165474

Attached Images

screenshot_LARSheet1.jpg (28.9 KB)

Find cell combinations that add up to a given value

AlliD22 — Wed, 16 May 2012 22:15:50 GMT

Is there a way to take a range of values from one column and find which combination(s) of said range equals a sum I am looking for?

Anable/disable a formula back and forth based on conditions

faustogil — Wed, 16 May 2012 22:09:36 GMT

I want to find some type macro that will allow me use the formula in a cell “if and only if” certain conditions are met (ex: adjacent cell is empty),otherwise to just hold the original value that the formula gave me, but I NEED TO BE ABLE TO USE THAT FORMULA AGAIN if adjacent cell becomes empty again.

To give an idea of what I’m trying to accomplish, I have a two sheets loan program; the 1st sheet calculates and display predicted monthly payments. On next sheet, I enter the actual payments as they occur every month, which are being pulled from the 1st sheet one by one as I enter payment date and amount paid. When I enter the actual payment, the main sheet takes that payment and automatically updates the loan. Up to that point, everything is working great.

Here is my problem: let’s say that a couple of years or months later, my interest rate changes; interest rate for those payments already made will also take the change. NO GOOD. Because of that, every time interest changes, if “actual payments” have been made, I have to save the document with different name to keep a record, then delete all data from actual payments, enter the remaining balance with the new interest rate and start all over as a brand new loan.

It’d be awesome if I could find a way to make those values stay the same by disabling the formula if data is present, keeping the formula enabled only for those unpaid future months (no data), BUT at the same time enabling the formula back IF those cells become empty again. Anyone who could help on how to go around solving this will be greatly appreciated.

cell color change based on 5 date location conditions

gmdrawdy — Wed, 16 May 2012 21:32:31 GMT

I have a 10 column worksheet that I need the first 5 columns to change color based on a date being input into one of the next 5 columns. Example:
if a date is put in f1 then a1-e1 turns yellow, if a date is put in g1 then a1-e1 turns green and so on for h1 through j1. Then it repeats for a2-e2 based on f2-j2. I want it to base the color coding on each rows date location. This is the 1st time I have used VBA but I figured out how to use the View Code shortcut. Conditional formatting worked when I only needed 3 colors but the worksheet has expanded beyond my ability.

Search multiple worksheets for employee no.; count leave taken; summarise one workshe

moshjosh — Wed, 16 May 2012 21:21:58 GMT

Worksheet which summaries staff used leave and calculates reaming leave allocation, all values summarised on new sheet [sheet name “SummaryLeaveSheet” ]

Hi all,

I would like help creating a method, formula or VB code which can look through 12 worksheets [named “LARSheet1” to “LARSheet12” ]; find an employee’s pay number on a row (say B25) and return a count of each attendance (“a”); lateness (“L”); holiday (“am or pm”) or Sickness (“S”) record on that row. The row may change on different worksheets but the search method can always use (B25) as a starting point.

With the found values, I would like a total count to be returned on a summary worksheet [ “SummaryLeaveSheet” ]

I would also like to list the dates each leave was recorded on the various worksheets [i.e “LARSheet1” to “LARSheet12” ] in a table.

Ideally one summery sheet could be used for all employees, the information would update and change based upon a pay number located in a cell linked to a ‘data validation’ drop down list.

Tall order I know….. Any help out there???

Many thanks, moshjosh

Example of layout of ["LARSheet1" ]

screenshot_LARSheet1.jpg

====== ====== ======== ====== ============= ========== ============ ============ ===== ========= ========== =======

This is a multi-part project which is collectively known as “Leave, Attendance Management” system using Excel” . Other parts are found on other treads. I hope to link the thread solutions together so others who wish to do a similar task can hopefully learn from help I receive (and my mistakes).

The other parts of the system are:

‘Leave attendance record’ worksheets (i.e months April 2012 through to March 2013). [I will refer to these sheets as "LARSheet1" to "LARSheet12" ].
http://www.ozgrid.com/forum/showthread.php?t=165478

‘Data validation’ worksheet which holds list and vlook-up info for formulas. [I will refer to these sheet “FormulaListSheet” ].

‘Database’ worksheet acting as a of staff details database. [ “StaffdBaseSheet” ]

‘Summary reaming leave’ page (this thread) which shows a summary of staff used leave and calculates reaming leave allocation. [ “SummaryLeaveSheet” ]

Attached Images

screenshot_LARSheet1.jpg (28.9 KB)

referencing cells from other workbook

Markica85 — Wed, 16 May 2012 21:18:16 GMT

Hello,

I created uniform .xls workbook that I use to collect data every time new patient arrives (around 20 variables for every patient). I put all filled workbooks in one folder and use a master workbook that creates a list of all other workbooks in a folder.

I would like to add another sheet in a workbook that would use references from a list (something like indirect function wiithout opening every workbook) to create large overview table with 20 columns contaning those 20 variables and every patient in a new row. Could you please help me with vba code, I don't have enough experience to create it from scratch. If you could please suggest me how to do it.
Folder name is c:\proba, variables are collected in cells from b1 to b20.

Thank you in advance

Problem with VLOOKUP in VBA

smanvb — Wed, 16 May 2012 20:54:03 GMT

Hello everyone!

I am trying to do a vlookup in an excel sheet where the data are found in a second sheet (library). The code is as follows. When I run the whole application, I get the #N/A value for all the vlookups (which is not correct, most values are found in the table). However, when I break the running of the code at the point just before the beginning of the code for the vlookups and then continue to run (changing nothing!!!), it gives the correct results. Could anyone help please? This is very urgent! Thanks a lot in advance!

VB:


 
With Worksheets(dat)        '.....      .Cells(g_i, 5) = myFile.DateLastModified 'GetFileTime              ' BREAK POINT            .Cells(g_i, 7) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 2, False) .Cells(g_i, 8) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 3, False) .Cells(g_i, 9) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 4, False) .Cells(g_i, 10) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 5, False) .Cells(g_i, 11) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 6, False) .Cells(g_i, 12) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 7, False) .Cells(g_i, 13) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 8, False) .Cells(g_i, 14) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 9, False) .Cells(g_i, 15) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 10, False) .Cells(g_i, 16) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 11, False) .Cells(g_i, 17) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 12, False)                End With         g_i = g_i + 1

Problem with VLOOKUP in VBA

smanvb — Wed, 16 May 2012 20:51:56 GMT

VB:

With Worksheets(dat)        '.....      .Cells(g_i, 5) = myFile.DateLastModified 'GetFileTime              ' BREAK POINT            .Cells(g_i, 7) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 2, False) .Cells(g_i, 8) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 3, False) .Cells(g_i, 9) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 4, False) .Cells(g_i, 10) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 5, False) .Cells(g_i, 11) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 6, False) .Cells(g_i, 12) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 7, False) .Cells(g_i, 13) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 8, False) .Cells(g_i, 14) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 9, False) .Cells(g_i, 15) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 10, False) .Cells(g_i, 16) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 11, False) .Cells(g_i, 17) = Application.VLookup(Cells(g_i, 2), Sheets("library").Columns("A:L"), 12, False)                End With         g_i = g_i + 1

Excel VBA - how to capture the first 5 chars from a text cell and use in vlookup

garryl — Wed, 16 May 2012 20:43:56 GMT

Folks, I have started using VBA and although have found many answers I am still only taking small steps.
I want to use vlookup. I have managed to get this to work when my cell reference is simple, but when the text string greater than my needs I need to shorten to the first 5 characters only.
I have the feeling that my issue is incorrect use of LEFT..

what I'd like to know, other than the answer :o) is for a none coder should I give up or can you point me to any tutorials?
btw I am fine using excel functions and sql.. just not VBA!

Any help would be greatly appreciated.

Garry

VB:

Sub vbvlookup()
 
    Range("G1").Formula = "=vlookup((newString = left(A18, 5)),'C:\path\name.xls]Summary'!$B$2:$N$126,9,FALSE)"
 
    Range("G1").Value = Range("G1").Value
 
End Sub

ADVANCED CHALLENGE: Formulas to Determine Accrued Commissions by Month

ibanker — Wed, 16 May 2012 20:39:01 GMT

Howdy,

I have an interesting challenge for you... can you deduce a formula for the following scenario?

Suppose you have a table with calendar months as columns (4 years worth = 48 columns) and individual salespeople as rows.

Each time a salesperson makes a sale, he receives the following annual commission (divide by 12 to spread among each month):
Year 1 (Months 1-12): 80%
Year 2 (Months 13-24): 78%
Year 3 (Months 25-36): 76%
Year 4 (Months 37-48): 74%
Year 5+ (Months 49+): 0%

So for every sale, he receives a staggered commission over the next 4 years.

If each salesperson sells multiple widgets of the four year period, that calendar month's commission must account for the timing of each sale. For example, let's suppose we want to calculate commissions for Bob in April 2012. In the past 4 years, Bob sold a widget in May 2008, July 2009, September 2010, and November 2011. The widget costs $100.

He would receive a sum of the following:

May 2008: Aged 47 months (0.74/12)*$100= $6.17
July 2009: Aged 33 months (0.76/12)*$100= $6.33
Sept 2010: Aged 19 months (0.78/12)*$100= $6.50
Nov 2011: Aged 5 months(0.80/12)*$100= $6.67

Total for April 2012: = 6.17+6.33+6.5+6.67 = 25.67

What series of formulas would deliver the accrued commission for each salesperson in each calendar month?

(Forgive me for not attaching a spreadsheet illustrating the example; our proxy server prohibits me from uploading files)

This challenge was also posted on http://www.excelforum.com/excel-gene...-schedule.html

Macro Create folder and save file from cell contents into existing directory

Webbers — Wed, 16 May 2012 20:21:29 GMT

I have a spreadsheet and I need to be able to create a macro that will do the following:

create a new subfolder from the value in cell I25 ("info" tab)
The folder needs to be created in an existing directory from the value in cell O8 ("info" tab)
The file needs to be saved in the subfolder just created with the file name that is listed in cell J32 ('info" tab)

The file name (listed in cell J32) already includes the file extension of ".xlsm", however if it makes things easier, I can remove the extension from the cell. These files will always be saved as ".xlsm" files. below is a table listing the location of the cells where the data will be accessed for this macro, each of these 3 cells resides on a worksheet named "info". The file used will be a template and then when used each week the macro will save the template as a name name with all the proper formatting (this is critical as we need to link back to previous files)

cell I25 - Subfolder name	05.25.2012
cell O8 - existing directory	X:\Compensation\Hours and Earnings\LV Payroll\BWJ\2012 Payroll\VC Payroll Worksheets 2012\
cell J32 - file name (with extension)	05.25.2012 VC Las Vegas.xlsm

Pivot Tables

sroe — Wed, 16 May 2012 19:30:35 GMT

Hi!

My question is: I am trying to refresh a pivot table and I get an error message "A pivot table cannot overlap another pivot table report". This is a spreadsheet that was passed onto me from another co-worker. To be honest with you I know how to bulid a pivot table but I do not know how to manipulate one. I read online about macros but I am not sure how to set one up to find the problem and then how to go on and fix it.

Please advise.
Thank you!

Log File

jharwood11 — Wed, 16 May 2012 19:09:08 GMT

Hello, I was desperately needing some help with this. I want to log changes from a range of cells in sheet 1 lets say A1:A255. I want those cells to be recorded in sheet 2 to cells A1:A10. I only want the last 10 changes from sheet 1 to be displayed in sheet 2. When cell A10 contains data I want cell A1 to be cleared and to start over with the next change. I have been searching for days for answers.

Copy USEDRANGE from closed Workbook to a separate Workbook using VBA

jeff5019 — Wed, 16 May 2012 19:00:02 GMT

Hi,

I feel like I'm almost there. I have successfully copied the data I need from a closed workbook to a new workbook but I had to specify the exact amount of columns and rows the data is in. The amount of columns will always be the same (5) but the rows will vary. I need this to just copy the used range. Any assistance would be greatly appreciated.

I am using a function along with the sub... I will include them both here. C=1 to 5 is alright but what can I do instead of r=1 to 11?

VB:

Private Function GetValue(path, file, sheet, ref)
 
    
 
     'Retrieves a value from a closed workbook

        Dim arg As String
 
        
 
     'Make sure the file exists

        If Right(path, 1) <> "\" Then path = path & "\"
 
          If Dir(path & file) = "" Then
 
            GetValue = "File Not Found"
 
            Exit Function
 
        End If
 
        
 
     'Create the argument

        arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
 
          Range(ref).Range("A1").Address(, , xlR1C1)
 
          
 
     'Execute an XLM macro

        GetValue = ExecuteExcel4Macro(arg)
 
        
 
End Function
 

 
Sub TrainingSummary()
 
    
 
     'Turn off screen updating

    
 
      Application.ScreenUpdating = False
 
      
 
     'clear all contents on summary page

            
 
        Sheet1.Cells.ClearContents
 
    
 
     'Pull data from closed workbook

    
 
        p = "C:\Documents and Settings\jperry000\Desktop\Megan Training Project"
 
        f = "East Local Training.xlsm"
 
        s = "Summary"
 
          
 
           For r = 1 To 11
 
               For c = 1 To 5
 
               a = Cells(r, c).Address
 
              Cells(r, c) = GetValue(p, f, s, a)
 
              
 
            Next c
 
           Next r
 
        
 
     'Change width, height, text alignment for entire sheet

    
 
        Sheet1.Select
 
        Cells.Select
 
        With Selection
 
            .HorizontalAlignment = xlCenter
 
            .VerticalAlignment = xlCenter
 
            .WrapText = False
 
            .Orientation = 0
 
            .AddIndent = False
 
            .IndentLevel = 0
 
            .ShrinkToFit = False
 
            .ReadingOrder = xlContext
 
            .MergeCells = False
 
            .RowHeight = 20
 
            .ColumnWidth = 25
 
        End With
 
        
 
     'Change width of column if 'Training' is the header

                        
 
            For i = 5 To 1 Step -1
 
    
 
                If Application.WorksheetFunction.CountIf(Columns(i), "Training") > 0 Then
 
                    Columns(i).ColumnWidth = 40
 
                End If
 
                 
 
            Next i
 
            
 
     'width, height, and text alignment for row1 only

                        
 
            Rows("1:1").Select
 
                With Selection
 
                    .RowHeight = 40
 
                    .WrapText = True
 
                    .Font.Bold = True
 
                End With
 
                
 
      'Freeze top row

                                                      
 
             ActiveWindow.FreezePanes = False
 
                                                        
 
             With ActiveWindow
 
                .SplitColumn = 0
 
                .SplitRow = 1
 
            End With
 
                                       
 
              ActiveWindow.FreezePanes = True
 
    
 
     'scroll to the top and select first cell

    
 
            ActiveWindow.ScrollRow = 1
 
            Cells(1, 1).Select
 
            
 
     'check for autofilter

         
 
                If Not ActiveSheet.AutoFilterMode Then
 
                    ActiveSheet.Range("A1").AutoFilter
 
                End If
 
              
 
        
 
     'Turn on screen updating

        Application.ScreenUpdating = True
 
        
 
End Sub

How do I run a Private Sub Worksheet_Change(ByVal Target As Range) Macro??

brooke — Wed, 16 May 2012 18:23:39 GMT

I am using a Macro (written in Module 1) to take what is entered in Cell A1 and name the report with the data in that cell. However, I can not find the Macro when I open the lists of Macros, presumably because it is Private. What do I need to do to run this function or get it to work. I've seen posts that said a code needs to be created to call this Macro. I have tried to replicate this with no success. Below is the data I have in Module 1. It is all I have added into VBA. Obviously, I am new to VBA so any help would be greatly appreciated.

VB:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 
    Dim Path As String ' path of current worksheet

    Dim ThisFileNew As String ' new file name including path

    Dim Resp As Integer ' user response to overwrite query

    Dim i As Integer
 
    Dim fname As String
 
    fname = ActiveWorkbook.FullName
 
    If Target = Range("AI1") Then
 
    For i = 1 To Worksheets.Count
 
        Worksheets(i).Name = Target.Value + i - 1
 
        Next
 
    End If
 
    If Not Intersect(Target(1), Range("A1")) Is Nothing Then
 
    With Application
 
        .EnableEvents = False
 
        .DisplayAlerts = False
 
    End With
 
    On Error Resume Next
 
     ' Set cell contents (file name) to upper case

    Target.Value = UCase(Target.Text)
 
     ' Get current path (empty if workbook has never been saved)

    Path = ThisWorkbook.Path
 
    If Not Path = "" Then Path = Path & ""
 
    ThisFileNew = Path & Target.Text & ".xls"
 
    Resp = vbOK
 
     ' Check for existing file of same name and, if present, ask whether to overwrite

    With Application.FileSearch
 
        .NewSearch
 
        .LookIn = ThisWorkbook.Path
 
        .SearchSubFolders = False
 
        .Filename = Target.Text & ".xls"
 
        .MatchTextExactly = False
 
        .FileType = msoFileTypeAllFiles
 
        If .Execute() > 0 Then
 
        Resp = MsgBox("This file already exists. Overwrite? ", vbExclamation + vbOKCancel)
 
    End If
 
End With
 
 ' Save the workbook if file does not exist, or if user wants to overwrite it

If Resp = vbOK Then
 
ActiveWorkbook.SaveAs Filename:=ThisFileNew
 
Kill fname
 
Else
 
    Resp = MsgBox("You will need to rename this file manually", vbInformation)
 
End If
 
On Error Goto 0
 
With Application
 
    .DisplayAlerts = True
 
    .EnableEvents = True
 
End With
 
End If
 
End Sub