Posts by Averilp

Re: Return Next Specific Weekday From Date

You are awesome Dave!!!

Thank you so much

Hi all,

What would be the formula I need to find the following Friday from a date? I figure that it would be a combination of WEEKDAY, DATE and CHOOSE but I can't get my head around it.

I have read Dave's posts on finding the last Friday of a month etc. but I can't work out how to get the next occurring Friday from a particular date (not just the last). I.e. if cell D2 has 13/08/2008, I want E2 to have 15/08/2008 since that is the next occurring Friday.

Thanks in advance.

Averil[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]OK, I have the following now:

=D2+(WEEKDAY(D2)>6)*6-WEEKDAY(D2)+6

However, this doesn't work properly if a Saturday date is entered (it puts the next Thursday in). My brain is in overload this week so I apologise if this is an extremely simple question.

Cheers,
Averil

Re: Remove Protection Of File & Unprotect Multiple Worksheets

You need to use the sheets code name.

You can see this in the VBE Editor in the Excel Objects. If you see your sheet name in the brackets, the code name is to the left. I.e. if you see Sheet3 (Panels_SQ) the sheets code name is Sheet3.

Alternatively, use:

sheets("Panels_SQ").Unprotect Password:="D8ts!js1"

Cheers,
Averil

Re: Extract Data From Multiple Sheets Into One

Dave,

I have read through the above posts as well as others. In fact, I've been searching the site since 5am this morning, and no case seems to be like mine unless I am searching incorrectly, but I'll keep trawling through.

In the mean time, does anyone have suggestions as to why the Select Case doesn't seem to be working as expected?

Regards,
Averil

Hi there,

This was originally going to be a simple Select Case question, but upon introducing my problem I realise that there is likely a much better way of attacking my "big picture".

I know I will likely get told off for the code given that it is basically a series of loops and will be time consuming because of this. However, this is a one-time code that I need to run on this spreadsheet so I can get the data into a "correct" format for future use.

Currently, I have hundreds of sheets that are simialr to the attached TA0632TEST and TA0632TEST2 sheets, each with a differing amount of columns based on the number of work days for that particular month. The code only needs to be performed on sheets starting with the initials of an employee (shown in select case in code).

I've been thinking of better ways to have data entered/managed and had thought it best to have all raw data in a single sheet which can then be filtered/looked up/pivot tabled/custom viewed etc. and all the other fun stuff like how it should be done :-).

I am open to other ideas about how best to get this historic data into a managable format.

The problem I am running into at the moment is that where the Select Case is checking if the sheet name starts with the initials and project number, it doesn't seem to recognise if it is correct (and therefore perform the actions).

I have stepped through the code and when I use the immediate window to manually check:

? ws.Name Like "TA0632*"

I get True as an answer, but the code goes on to the next case as though it is false.

Here is the code I have so far, which I'm sure can be trimmed down loads:

Option Explicit
Dim currCell As Range
Dim c As Long
Dim r As Long
Dim rng
Dim ws As Worksheet
Dim skp As String
Dim LastColumn As Integer


Sub Breakdown()
Dim t
t = Timer


    For Each ws In ThisWorkbook.Sheets
    Debug.Print "Current sheet is " & ws.Name
        CheckSheet
        If skp = "Skip" Then GoTo SkipGetRange
        GetRange
        CheckAndTransferValues
SkipGetRange:
    Next ws
Debug.Print "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run."
End Sub


Sub CheckSheet()
Debug.Print ws.Name
    Select Case ws.Name
        Case ws.Name Like "MS0632*"
            Exit Sub
        Case ws.Name Like "NC0632*"
            Exit Sub
        Case ws.Name Like "TA0632*"
            Exit Sub
        Case ws.Name Like "ZM0632*"
            Exit Sub
        Case ws.Name Like "AM0632*"
            Exit Sub
        Case ws.Name Like "AP0632*"
            Exit Sub
        Case ws.Name Like "CF0632*"
            Exit Sub
        Case ws.Name Like "HT0632*"
            Exit Sub
        Case ws.Name Like "BO0632*"
            Exit Sub
        Case Else
            skp = "Skip"
    End Select
End Sub


Sub GetRange()
    With ws
        If WorksheetFunction.CountA(Cells) > 0 Then
            'Search for any entry, by searching backwards by Columns.
            LastColumn = .Cells.Find(What:="TOTALS", After:=[A2], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        End If
        ' Offset to exclude the TOTALS column from the range
        rng = .Range(Cells(2, LastColumn).Offset(0, -1), .Range("A65536").End(xlUp).Offset(-1, 0)).Address
        .Range(rng).SpecialCells(xlCellTypeBlanks) = "Blank"
    End With
End Sub


Sub CheckAndTransferValues()
   For Each currCell In Worksheets(ws.Name).Range(rng)
        Select Case currCell
            Case currCell.Row = 1           ' If current cell is on row 1, skip to next cell
                GoTo NextCurrCell
            Case currCell.Row = 2           ' If current cell is on row 2, skip to next cell
                GoTo NextCurrCell
            Case currCell.Column = 1        ' If current cell is in column 1, skip to next cell
                GoTo NextCurrCell
            
            Select Case currCell.Value
                Case Is = "Blank"           ' If cell contains the word "Blank"
                    GoTo NextCurrCell       ' Move to the next cell
                Case Is = 0                 ' If cell = 0
                    GoTo NextCurrCell       ' Move to the next cell
                Case Is = ""                ' If cell is empty
                    GoTo NextCurrCell       ' Move to the next cell
                Case Else
                    c = currCell.Column - 1 ' Set the c value for the column offset
                    r = currCell.Row - 2    ' Set the r value for the row offset
                    
                    ' Grab task name from current sheet and copy it to the "Output" Sheet
                    Worksheets(ws.Name).currCell.Offset(0, -c).Copy
                    Sheets("Output").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                    ' Grab date from current sheet and copy it to the "Output" Sheet
                    Worksheets(ws.Name).currCell.Offset(-r, 0).Copy
                    Sheets("Output").Range("A65536").End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues
                    ' Grab number of hours from current sheet and copy it to the "Output" Sheet
                    Worksheets(ws.Name).currCell.Copy
                    Sheets("Output").Range("A65536").End(xlUp).Offset(0, 2).PasteSpecial xlPasteValues
                    ' Get staff name and rate from the current sheet and transfer to the "Output" sheet
                    Worksheets(ws.Name).Range("A1").Copy        ' Cell containing staff name
                    Sheets("Output").Range("A65536").End(xlUp).Offset(0, 3).PasteSpecial xlPasteValues
                    'Rate
                    Worksheets(ws.Name).Range("A2").Copy        ' Cell containing staff rate
                    Sheets("Output").Range("A65536").End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues
            End Select
NextCurrCell:
    Next currCell
End Sub

I appreciate any thoughts as to the best method, or advise as to why my select case is not working as expected.

Kind regards,
Averil Pretty

Re: Percentage Calculation Based On Months & Dates

Try this in your H3 and copy across to the right:

=IF(MONTH($B$2)=MONTH(H$1),$A$2*0.5,($A$2*0.5)/11)

Re: Lookup Value In Table

No worries mate Glad you got it

There are lots of helpful pages on here including this page Dave has put together that explains common errors and how to handle them:

http://www.ozgrid.com/Excel/formula-errors.htm

Cheers,
Averil

Re: Lookup Value In Table

When are you getting the #value! error? Maybe if you could provide an example I could have a look...

Re: Lookup Value In Table

Glad I could help

Re: Lookup Value In Table

There are various ways you can do this. This might not be the best solution, but I'm having a crack at helping others in this forum where I usually get so much help

You can build it all into one formula like:

=IF(ISNA(VLOOKUP(A1,Table1,2,FALSE)),"Product not found",VLOOKUP(A1,Table1,2,FALSE))

Cheers,
Averil

Re: Count Number Of Days Occured For Certain Month In Date Column

Use dynamic named ranges that will expand/contract when you add/remove data. There is a tutorial and there are plenty of posts on using named ranges in this forum. Hover over the word Range to see them.

Cheers,
Averil

Re: Ensure Automatic Calculation

Hey there,

Not sure if this makes a difference to you, but the calculation mode is an Excel level setting NOT a workbook level setting, and it is set by the first workbook opened in an Excel session. That is, if you open a workbook whose calculation mode is set to manual, then open your other workbook even if its calculation is technically set to automatic, the calculation in the second workbook will revert back to manual.

You may also want to use Shift/Ctrl/Alt/F9 which will rebuild dependencies and do a full calculate.

Cheers,
Averil

Re: Remove Spaces From Imported Dates

Thanks for the correction Dave... I had actually typed a sentance on dates/times as numbers/decimals etc. then decided to just link to your page so cut the majority of it out and left the wrong end part in so jumbled it up - sorry!

Not sure why there is resistance to find/replace either as it can be done like so (not prettied up just whipped out of a file I've been using):

Sub ReplaceSpaceWithBlank()
Dim TopCell As Range
Dim BottomCell As Range


    Set TopCell = Range("A1")
    Set BottomCell = Cells(65536, ActiveCell.Column)
    If IsEmpty(TopCell) Then
        Set TopCell = TopCell.End(xlDown)
    End If
    If IsEmpty(BottomCell) Then
        Set BottomCell = BottomCell.End(xlUp)
    End If
    
    Range(TopCell, BottomCell).Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
            xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Cheers,
Averil

Re: Remove Spaces From Imported Dates

Hi there,

It's working for me... You may just have to add a helper column or row. See the attached for an example.

Dates are never really stored as dates but as decimal fractions. See this page here:

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Cheers,
Averil

Re: Calculate X Percentage Of Cost

All good

Re: Calculate X Percentage Of Cost

Sorry Dave, I know you know what I meant, but I should have clarified...

If your initial value/cost is in cell A2, put =A2*1.1 in the cell where you want the calculated value returned.

A better option if you have lots of rows especially if there is the likliehood of the % amount changing would be to store the percentage (in this case 10%) in say cell A1, then use the formula =A2+(A2*$A$1) where you want the result, and copy it down. This means you wouldn't need to change each formula if the % changes, just the one cell (A1).

Cheers,

Re: Calculate X Percentage Of Cost

Cost * 1.1

Re: Removing Spaces From Cell To Then Calculate On

I would use =TRIM(A1)

Cheers,
Averil

Re: List All Worksheets

Hi Ben,

See here for creating a sheet index http://www.ozgrid.com/VBA/sheet-index.htm

Re: Scoresheet That Automatically Ranks / Sorts

In my example I would enter this formula in D2 then copy down:

=CHOOSE(C2,8,7,6,5,4,3,2,1)

Cheers,
Averil