Looping thru cells on two different sheets

Hey,

This is a little similar to

http://www.excelforum.com/excel-prog...each-time.html

I have a list and i want to be able to move down the list in column c looking for a particular value (set via a userform). When the value is reached, I would like to copy some of the information from that line into the A1 on sheet2. When the next occurance of the value is reached, I would like to copy some info (same as before) into A2 on sheet 2.

So far I have:

Private Sub CommandButton1_Click()

Dim start1 As Range
Dim start2 As Range
Dim service As String
Dim title As String
Dim ref As String

Dim time As Integer
Dim when As Date

my_time = CDate(time)
my_when = CDate(when)

Dim row_count As Integer

row_count = 0

Set start1 = Range("sheet1!g2")

Set start2 = Range("sheet2!A1")

service = ComboBox1.Value

my_when = Format(start1.Offset(0, -6), "dddd, mmm d yyyy")
my_time = Format(start1.Offset(0, -6), "hh:mm")
title = start1.Offset(0, 3)
ref = start1.Offset(0, -4)

start1.Select

Do Until IsEmpty(start1) = True

If start1.Value = service Then

start2.Value = "On " & my_when & " at " & my_time & "," & title & " (" & ref & ")."

Else

End If

row_count = row_count + 1

Loop

Unload UserForm2

End Sub

But I can't get the looping through to work. I have been trying to incorporate the rowcount into the offset function.

Thanks In Advance.

MoonWeazel

Not tested but maybe:

Private Sub CommandButton1_Click()

Dim rngLoopRange As Range

Dim service As String
Dim title As String
Dim ref As String
Dim my_when As String
Dim my_time As String

Dim row_count As Integer

row_count = 0

service = ComboBox1.Value

For Each rngLoopRange In Sheets("Sheet1").Range("G2:G" & Sheets("Sheet1").Cells(Rows.Count, 7).End(xlUp).Row)

    If rngLoopRange.Value = service Then

        my_when = Format(rngLoopRange.Offset(0, -6), "dddd, mmm d yyyy")
        my_time = Format(rngLoopRange.Offset(0, -6), "hh:mm")
        title = rngLoopRange.Offset(0, 3)
        ref = rngLoopRange.Offset(0, -4)

        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
            "On " & my_when & " at " & my_time & "," & title & " (" & ref & ")."

    End If

    row_count = row_count + 1

Next rngLoopRange

Unload UserForm2

End Sub

Dom