Can this be condensed

[Neil Atkinson]

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

 

[Rowan Drummond]

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

 

[Nick Hebb]

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

 

[Tom Ogilvy]

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

 

[Neil Atkinson]

Many Thanks to all that replied