PDA

View Full Version : SELECT A EXCEL ROW USING MACRO VBA



pbdsp79
02-08-2008, 08:52 AM
Hi All

Having an excel file with three column name curreny, region and holiday (date).

In currency different currencies are there like AED, INR, USD, GBP ETC.

In region there is one region, LDN (LONDON)

In holday, dates are there.

What i require is whenever i open that excel sheet, it should show me that today there is holiday for following currencies for the same day.

for eg. if i open that excel sheet on 8/02/08 it should show me that on 08/02/08 this much currencie have holiday for 08/02/08 with dates for the same day.

if i open that excel sheet on 9/02/08 it should show me that on 09/02/08 this much currencie have holiday for 09/02/08 with dates for the same day.

I am attaching a excel file for reference.

I have tried hard but not received any result.

Regards
Prashant

Bob Phillips
02-08-2008, 09:27 AM
Option Explicit

Private Sub Workbook_Open()
Dim LastRow As Long
Dim rng As Range
Dim msg As String
Dim cell As Range

With ThisWorkbook.Worksheets("Sheet1")

LastRow = Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("A:C").AutoFilter Field:=2, Criteria1:=Format(Date, "D-MMM-YYYY")
On Error Resume Next
Set rng = .Range("B2").Resize(LastRow - 1).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then

For Each cell In rng

msg = msg & cell.Offset(0, -1).Value
Next cell

MsgBox "The following currencies have a holiday" & vbNewLine & msg, vbOKOnly, "Currency Holidays"
End If
.Columns(2).AutoFilter
End With

End Sub

Simon Lloyd
02-08-2008, 09:46 AM
You could try this in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Range("$A$1:$C$21").AutoFilter Field:=2
End Sub
Private Sub Workbook_Open()
Dim Dt As String
Dt = Format(Date, "dd-mmm-yy")
ActiveSheet.Range("$A$1:$C$21").AutoFilter Field:=2, Criteria1:=Dt

End Sub
which will filter your sheet to show the currencies and then put this formula in a cell on the worksheet to count the number of currencies matching todays date!



=SUMPRODUCT(($B$2:$B$21=TODAY())*(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$21)-MIN(ROW($B$2:$B$21)),,))))
really all you need is this formula!

Simon Lloyd
02-08-2008, 09:55 AM
If you are not going to use filters at all then this formula will do the trick!


=COUNTIF($B$2:$B$21,TODAY())