Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this be condensed
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Condensed List from Imported Data | Excel Discussion (Misc queries) | |||
Create a Condensed List from Imported Data | Excel Worksheet Functions | |||
Impossible? Condensed search results | Excel Worksheet Functions |