manupulating rows and values [Archive]

Don30

09-29-03, 13:26

I am new to programming in Excel. My problem is, I am coding a macro to joincells. The problem is that the row can happen anywhere throughout the report. I was able to use the find function to find the begining of row and able to do what I need, but can't deterine where to break out of the loop.

Here is the code I'm using:

Public Function NewFind()
Dim currRow As Integer
Dim currcell As String
Dim y As String
Dim r As String

With Worksheets(1).Range("a1:a500")
Set C = .Find("DATE", LookIn:=xlValues)
currRow = C.row
currRow = currRow + 2
Do

currcell = "G" & currRow
y = "I" & currRow
r = currcell & ":" & y
JoinCells Range(r), AndCenter:=False
currRow = currRow + 1

Loop While Worksheets(1).Range("A:" & currRow) Is Nothing

End With
End Function

Any help would greatly appreciated. If there is a better way to code this whole thing, that would be helpful too.

RickKnight

09-29-03, 14:41

Well, I'm not to sure about this, but as I review your code, it appears that you are seeking a specific date. So, I'd use the date to create the bail out point.

Sub FindDate()
Dim r as Range
Dim dt as Date
Dim strDate as String
strDate = "01/25/03"
For Each r In Range ("A1:A" & Cells(Rows.Count,"A").End(xlUp).Row)
If r.value = strDate then
Exit Sub (or some other action)
ElseIf r.Value <>strDate
End If
Next
End Sub

The Date your require can be placed into the sub by referencing a cell or from a user form.
Good luck