Solved: Find Value in Column

[compariniaa]

my question is similar to saban's post, "Find value in column (http://www.vbaexpress.com/forum/showthread.php?t=8610&highlight=find)." and djblois' "Find the last item and select it (http://www.vbaexpress.com/forum/showthread.php?t=8415&highlight=find)"(but I couldn't get any codes to work properly from djblois' post)
I have a spreadsheet detailing forecasted sales, and 2 of the details provided include quarter and year. I need to write a macro that will create pivot tables for every year. to do that, i need to be able to distinguish between quarters (Q1, Q2, Q3, Q4) of every year (so I don't wan't Q1 of 2006 and 2007 together). how would i do that? so far all i have is to sort the entries by year first then by quarter, but i have no idea how to return the range for every different quarter. could anybody help?

[OBP]

Do you want to transfer the Quarterly data to another sheet where your pivot table will pick it up?

[compariniaa]

no, I'd like to get the row number so the pivot table will only pull data from the applicable range

[OBP]

Doesn't the quarterly data cover more than one row?
Or do you want the start and finish rows?
Any chance of posting example of how the data looks?

[compariniaa]

certainly. i should have done it in the first place to avoid confusion. this is a sheet from the actual workbook. I changed all the values except the year and quarter because i don't know how much information is public, so i decided to play it safe. i need a macro that will find where the last row for 2006 Q1 is, where the last row for 2006 Q2 is, etc
thanks for taking a look at this. i found a code online that will search for the last 2006 entry, and i'm trying to modify it to find 2006 then within the 2006 data range find Q1. i'd like to loop it to store each row number in a separate worksheet within the same book
Public Sub FindLast()
Dim rngFound As Range
Dim rngToSearch As Range
Dim YearRow As Integer
Dim Year As Integer

Year = Sheets("Cell References").Range("B2").Value

Do While Year <= Range("K65536").End(xlUp).Value
Set rngToSearch = Sheets("Combined").Range("K2", "K" & Range("K65536").End(xlUp).Row)
Set rngFound = rngToSearch.Find(What:=Year, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchDirection:=xlPrevious)
If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
YearRow = rngFound.Row
MsgBox YearRow
'for the moment i have the msgbox so i can check if it's working
'later i will replace the msgbox command with a range value to change
Year = Year + 1
End If
Loop
End Sub

[compariniaa]

i forgot to post the file...oops.
but i just had a thought...what if i concatenated the year and quarter columns in a third column, then just did a search based off that new column?
i dont want to mark this thread solved yet because i don't know if i'll be able to figure out how to do that, but to save your time i think it's best everyone forget about this for now. if i can figure it out i'll mark it solved, if not, i'll come back to this thread asking for help

[OBP]

I would use a for next loop but I would not use the search feature because you are actually looking foran intersection of 2006 q1/2006 q2. so you are looking for a change of status i.e you are looking for = 2006 but not equals q1 and then not equals q2 etc.
I would use Activecell.offset until I reached the intersection and then reset the start at that point and start the activecell.offset again.
You sure you don't want me to try it?

The only problem is your speadsheet only has Q1, but I can change that

[compariniaa]

i don't think i'd be able to do what you suggested because i've never used intersect. sorry about my file....i had to trim it down so i could attach it, but i also forgot you needed something more than just Q1. so what would the code be for the intersect method you explained?

[OBP]

Sorry, intersect is not a basic word.
I have it finding the rows for you, I will post it in a few minutes, the only problem that I have is stopping the search lol.

[compariniaa]

Here's the file with quarters and years

[OBP]

Here it is, I am sure you will get the idea, it only has message boxes telling you the row number which is stored in a variable.
It hasn't got application.screenupdatign set to false so that you can see it in action.

[compariniaa]

Thank you so much for looking at this for me, I really appreciate it.
You're code is brilliant, and exactly what I needed. Thanks again!!

Oh, and I guess I should apologize in advance about the World Cup ;)

[OBP]

World Cup, what is that?
It is Wimbledon Fortnight.
Can you mark this thread as solved?
If you need any more help on it just pm me.

[compariniaa]

I saw the UK's flag under your name and figured you'd be following the World Cup - world soccer (football) championship. Most likely Brazil (where I'm from) and England will play on 5 July, and loser goes home.
I was just playing though, it should be a good match if Brazil makes it that far

again, thank you very much for your help, you're great

[OBP]

I was just playing too, being a man of course I follow the football.

[compariniaa]

ah good...I was a little surprised at your other post, it's a good thing you cleared that up because I was beginning to worry about the UK. are you an England fan? if not, then what team?

[OBP]

Not exactly a fan, I do follow them, but they are so inconsistent that they are very frustrating to watch.
By the way your English is excellent.

[compariniaa]

yeah, Brazil has also been lacking consistency.

about my english--thank you, but I cheated a little and lived in the US for 15 years growing up.