Posts by moshjosh

Re: Input box to gather date range and sort list using VBA

Quote from Luke M;725356

Hello moshjohs.

This sounds like a perfect place to use an Advanced Filter. Take a look at the attached, or see link for more info.
http://www.contextures.com/xladvfilter01.html

Hi Luke, Thank you for your input but this does not solve my problem. My requirements included the filter via vba code and input box for user input of date ranges. Have you got any other solution which covers this?

Hi fellow VBA lovers,

I would love a solution to help me do the follow:

'Sort' (i.e. push all applicable rows of data which match my criteria to the top of a list, as opposed to just filtering out the irrelevant dates) a list of scheduled courses to the top of my data set based on a date range ('start' and 'end' dates) in Excel 2010. The dates will be provided by the user via an 'Input box' which will pop up at the start when the code is run.

It would be great if the sorted data could be copied and pasted into a new worksheet called 'sorted data'. Tall order but I would be massively grateful of a solution. Cheers

Sample In-Put box:
[TABLE="class: grid, width: 191"]

[TD="colspan: 2"]Please enter the data range your wish to sort by?:
[/TD]

Date range 'Start'

Date range 'End'

10/08/2014 09:00

14/08/2014 09:30

[/TABLE]

• My data starts in Cell A5
• The table headers are in row 4
• My data table has 32 colums
• and 'X' number of rows (usually about 3,000)

An example of my data is below 'dates' to sort by will be present in column B.

[TABLE="class: grid, width: 719, align: left"]

UID ID

Start Date

Last Date

Course UID

Course Title

Status

100909

10/08/2014 09:00

19/08/2014 17:00

JPCVBA

VBA for complete novice

Cancelled

100908

12/08/2014 09:30

11/08/2014 12:30

JPCMIR

Baking in the Microwave

Active

100907

14/08/2014 09:30

12/08/2014 17:30

JPCPLU

Plumbing 101

Cancelled

100906

02/08/2014 09:30

14/08/2014 17:30

JPCDIY

DIY for the accident prone

Active

100905

25/08/2014 09:30

14/08/2014 17:30

JPCCHA

How to dance the cha-cha-cha

Cancelled

100902

18/08/2014 09:30

14/08/2014 17:30

JPCDIY

DIY for the accident prone

Active

[/TABLE]

Re: Can Countif be case sensitive

Hi A9192Shark,
I'm not that familiar with VB; where within the VBA editor does the code go? Also what formula do I type into the formula bar once the code is in the right place? Does it work across all worksheets or just the one I apply the code to?

I have attached a screen-shot of my VBA editor if this helps. Many thanks in advance.

[ATTACH=CONFIG]45683[/ATTACH]

Quote from A9192Shark;140530

JP2004,

or this function which is a simplified version of countif for your situation:

Code

Public Function CountifCaseSensitive(rngEvaluate As Range, _
strCriteria As String) As Long
    Dim rng         As Range
    Dim lngCount    As Long
    lngCount = 0
    For Each rng In rngEvaluate
        If rng = strCriteria Then lngCount = lngCount + 1
    Next rng
    CountifCaseSensitive = lngCount
End Function

A.

Display a message box which displays cell address of the last populated cell in a row and jumps to 'it' when user clicks “ yes “ button

I am a complete novice to VBA. Is there a cleaner way to write some VBA code performing the following function?

When macro is run:

• Displays popup message box with text.
• Runs string of VBA code which returns cell address of last populated cell in column "A" and displays it as follows: "Last Populated Cell Is: [Cell Address] Jump To The Last Cell?"
• Gives user chose of "Yes" or "No"

• If user choose YES = Jumps to last populated cell.
• If user choose NO = macro ends and pop box disappears.

Here is my code at present:

Sub MsgBox_LastPopulatedCell()


Dim result As VbMsgBoxResult


result = MsgBox("Last Populated Cell Is:" & Chr(32) & Range("A65536").End(xlUp).Address & vbCrLf & "Jump To The Last Cell?", vbYesNo + vbQuestion, "Joshua Excel Tools" & Chr(32) & Chr(169))
  
Select Case result


    Case 6
        Range("A65536").End(xlUp).Select
    Case 7
        ActiveCell.Range("A1").Select
End Select


End Sub