It looks like you are trying to find the address of certain headings. If
you are only going to have each heading once then there is no need to
loop using findnext - you either find it or you don't. So maybe like this:
Sub Macro2()
Dim c As Range
Dim contract As String
Dim upgrade As String
Dim prepay As String
With Worksheets(2).Range("B4:U4")
Set c = .Find("Contract", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
contract = c.Address
Set c = Nothing
End If
Set c = .Find("Upgrade", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
upgrade = c.Address
Set c = Nothing
End If
Set c = .Find("Prepay", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
prepay = c.Address
Set c = Nothing
End If
End With
MsgBox "Contract = " & contract & vbCr & vbCr _
& "Upgrade = " & upgrade & vbCr & vbCr _
& "Prepay = " & prepay
End Sub
Hope this helps
Rowan
Neil Atkinson wrote:
Sub Macro2()
'
With Worksheets(2).Range("B4:U4")
Set c = .Find("Contract", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
contract = firstAddress
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets(2).Range("B4:U4")
Set c = .Find("Upgrade", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
upgrade = firstAddress
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
With Worksheets(2).Range("B4:U4")
Set c = .Find("Prepay", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Prepay = firstAddress
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
MsgBox "Contract = " & contract & vbCr & vbCr _
& "Upgrade = " & upgrade & vbCr & vbCr _
& "Prepay = " & Prepay
End Sub
Regards
Neil
*** Sent via Developersdex http://www.developersdex.com ***