Can this be condensed
 

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 ***

Can this be condensed
 

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 ***

Can this be condensed
 

You could take out all the interim End With and With statements.

Also, are you sure you want to keep re-assigning contract =
firstAddress, upgrade = firstAddress, and prepay = firstAddress every
time through the loops? I'm guessing you meant c.Address in each of
those statements.


Regards,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

Can this be condensed
 

I don't see any point in doing multiple finds for a single value since you
only maitain an record of the first cell found, so I would suggest this.

Sub Macro2()
'
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 = .Find("Upgrade", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then upgrade = c.Address
Set c = .Find("Prepay", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then Prepay = c.Address
End With
MsgBox "Contract = " & contract & vbCr & vbCr _
& "Upgrade = " & upgrade & vbCr & vbCr _
& "Prepay = " & Prepay

End Sub

--
Regards,
Tom Ogilvy




"Neil Atkinson" wrote in message
...


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 ***

Can this be condensed
 

Many Thanks to all that replied


*** Sent via Developersdex http://www.developersdex.com ***