Solved: Cell address : loop till 2006 data

[skaswani]

hello


1)
i have a date column which is Cell A


the Range has dates from 2005 till update

is that possible to loop only 2007 values?

& to know the cell adress where it end?


2) how to find specific value cell

i.e,


2) if i have 200 rows & i just want to get the cell where value = "ABC"

thanks

[Bob Phillips]

1) i have a date column which is Cell A, the Range has dates from 2005 till update is that possible to loop only 2007 values & to know the cell adress where it end?

[vba]
With Activesheet
For i = 1 To .Cells(.Rows.Count,"A").End(xlUp).Row
If Year(.Cells(i,"A").VAlue) = 2007 Then Msgbox .Cells(i,"A").Address
Next i
[/vba]

2) how to find specific value cell

i.e, if i have 200 rows & i just want to get the cell where value = "ABC"

[vba]

Dim cell As Range

With ActiveSheet
Set cell = .Columns(1).Find("ABC")
If Not cell Is Nothing Then
MsgBox cell.Address
End If
End With
[/vba]

[mdmackillop]

To get the range.
[VBA]Option Explicit

Sub MatchDate()
'http://support.microsoft.com/default.aspx?scid=kb;en-us;213643
Dim Yr As String
Dim Rng As Range, Source As Range, Strt As Long, Endd As Long

'Set Year and Range
Yr = Format(InputBox("Enter year yy"), "00")
Set Source = Range("A:A")

Strt = DateRow("1/1/" & Yr, Source, 0)
If Strt = 0 Then Strt = DateRow("1/1/" & Yr, Source, 1) + 1
Endd = DateRow("31/12/" & Yr, Source, 1)
Set Rng = Range(Cells(Strt, 1), Cells(Endd, 1))

Rng.Interior.ColorIndex = 6

End Sub

Function DateRow(Dte As String, Rng As Range, Mtch As Long) As Long
On Error Resume Next
DateRow = Application.Match(CLng(CDate(Dte)), Rng, Mtch)
End Function

[/VBA]

[skaswani]

Dear mdmackillop

wheen i execute ur cute it gives me this error!
[vba]
application defined or object defined error
[/vba]

Dear Xld

you code help me alot, but i have just one problem..

u have used the FOR LOOP, so it gives me message after every line, actuly what i have to do is that i have to get the last cell no#



my data looks like this


11-Jan-07 69
10-Jan-07 69.2
9-Jan-07 69.95
8-Jan-07 69
5-Jan-07 69.45
4-Jan-07 69.25
29-Dec-06 68.5
28-Dec-06 69
27-Dec-06 68.5



so when it loop in For, after every row it gives me MsgBox with cell addy

actully i dont know how to use While Unil loop

thanks

[mdmackillop]

Can you post your data in a workbook. Use Manage Attachments in the Go Advanced section

[Bob Phillips]

Dear Xld

you code help me alot, but i have just one problem..

u have used the FOR LOOP, so it gives me message after every line, actuly what i have to do is that i have to get the last cell no#

my data looks like this

11-Jan-07 69
10-Jan-07 69.2
9-Jan-07 69.95
8-Jan-07 69
5-Jan-07 69.45
4-Jan-07 69.25
29-Dec-06 68.5
28-Dec-06 69
27-Dec-06 68.5


so when it loop in For, after every row it gives me MsgBox with cell addy

actully i dont know how to use While Unil loop

[vba]


With Activesheet
For i = .Cells(.Rows.Count,"A").End(xlUp).Row To 1 Step -1
If Year(.Cells(i,"A").VAlue) = 2007 Then
Msgbox .Cells(i,"A").Address
Exit For
End If
Next i
End With
[/vba]

[mdmackillop]

"Provide sample data and layout if you want a quicker solution." - MD
Data in ascending/descending order can affect solutions, hence my request in post #5

[skaswani]

u guys really dam good

God Bless u

regards,