PDA

View Full Version : Find cell, make active cell and repeat



werner123
04-02-2017, 09:36 PM
Good Morning

I have this code , it works fine except I need it to make the current founded cell with the "true" statement the Activecell(or Row) and run the call Macro on the active cell , and then move to the next "true" find and make that the Active and repeat the process.
The macro needs to do its business on the active row of every "true" statement
Problem is it finds the first "true" value , and stops


Sub TRUEVALUE()
Dim c As Range
With Sheet1.Range("C1:C10")
Set c = .Find("TRUE", LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Select
Call TOTALVALUE

Set c = .Find(c)
Loop While Not c Is Nothing And c.Address <> firstAddress

End If


End With
End Sub

mikerickson
04-02-2017, 10:15 PM
I think that changing this line will do what you want.

Set c = .FindNext(After:=c)

At the same time, I don't think that you need to actually select the found cells. TOTALVALUE could be altered to accept a range argument, so that no selecting is needed.

werner123
04-03-2017, 12:26 AM
Thank you , works perfectly, on the same sheet , having problem running macro that runs between sheets.


Loop While Not c Is Nothing And c.Address <> firstAddress

error 94 , Object variable or with block variable not set

werner123
04-03-2017, 02:44 AM
Thank you so much for your effort , works perfect in my Macro I had another search that search for a keyword and add pagebreak , that somehow interfered with this one

snb
04-03-2017, 02:57 AM
Keep it simple


sub M_snb()
sn=Sheet1.Range("C1:C10")
for j=1 to ubound(sn)
if sn(j,1) then ...
next
End sub

That's all you need. (and even less if you tell us what 'totalvalue' does).
Allways avoid 'select' and 'activate' in VBA.

Paul_Hossler
04-03-2017, 05:33 AM
Can you change TOTALVALUE to accept a Range as an input?

I don't like to rely on Selection or ActiveCell being what I think / hope they are







Sub TRUEVALUE()
Dim c As Range
With Sheet1.Range("C1:C10")
Set c = .Find("TRUE", LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
Do
Call TOTALVALUE (c)

Set c = .FindNext(After:=c)
Loop While Not c Is Nothing And c.Address <> firstAddress

End If


End With
End Sub