Findnext in open workbooks

[farhan_babu]

Hi,

I m new in this forum.I have a problem that I have a code which is find through all open workbook but it is not woring as findnext.Here is the code.


Sub Find_Next()
On Error Resume Next
Dim wb As Workbook
Dim wk As Worksheet
Dim rng As Range
Dim rng1 As Range
Dim radd As String
For Each wb In Application.Workbooks
For Each wk In wb.Worksheets

Set rng = wk.Cells.Find(50, ActiveCell, xlFormulas, xlPart, xlColumns)
If Not rng Is Nothing Then
radd = rng.Address
'MsgBox wb.Name & wk.Name & rng.Address

Do
Set rng = wb.wk.Cells.FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> radd

Application.Goto rng, True
Exit For
Exit For
End If


Next
Next
End Sub


Thanks in advance to help me.

Regards,

[Bob Phillips]

Post your workbook, it will make life a lot easier.

[johnske]

Hi farhan_babu,

Welcome to VBAX, hope you don't mind, but I've taken the liberty of putting your code in VBA tags to make it more readable.


Post your workbook, it will make life a lot easier.
:rofl: :rotlaugh: (yes, good idea)

Regards,
John

[farhan_babu]

Hi,

This code is in personal macro book.May I do attachment?

Regards,

[Bob Phillips]

Yes, there is a button below the reply box called Manage Attachment.

[farhan_babu]

Here is the attachment.

[Bob Phillips]

There is not a lot of data on that worksheet, where is the data worksheet?

[farhan_babu]

Its book is for just example.Its run properly in find method through all open workbook but not working in case of FindNext method.Its dosn,t change the workbook or worksheet in case of FindNext.

I hope u will understand that.

REgards,

[jindon]

not sure if this is what you wanted....


Sub test()
Dim wb As Workbook, ws As Worksheet, r As Range, ff As String, msg As String
For Each wb In Workbooks
For Each ws In wb.Sheets
Set r = ws.Cells.Find(50, , , xlPart)
If Not r Is Nothing Then
ff = r.Address
Do
msg = msg & wb.Name & " : " & ws.Name & " : " & r.Address(0, 0) & vbLf
Set r = ws.Cells.FindNext(r)
Loop Until r.Address = ff
End If
Next
Next
If Len(msg) Then
MsgBox "Found:" & vbLf & msg
Else
MsgBox "Not found"
End If
End Sub

[farhan_babu]

Hi,

Thanks for your response but I want the following functionality;

1 - There is three workbooks open.

2 - And all books has the desired value of 50.

3 - When I first time run the code it should be select the first occurance in first workbook.

4 - When I run the 2nd time then the next sheet or book should be searched.

5 - When I run the Next time this code then it will search it next sheet or next book.

I hope I will explain my problem in detail.


Regards,

[jindon]

try


Private a()
Sub test()
Dim wb As Workbook, ws As Worksheet, r As Range, ff As String, n
On Error Resume Next
x = UBound(a, 2)
If Err.Number <> 0 Then
On Error GoTo 0
For Each wb In Workbooks
For Each ws In wb.Sheets
Set r = ws.Cells.Find(50, , , xlPart)
If Not r Is Nothing Then
ff = r.Address
Do
n = n + 1
ReDim Preserve a(1 To 4, 1 To n)
a(1, n) = wb.Name: a(2, n) = ws.Name
a(3, n) = r.Address(0, 0)
Set r = ws.Cells.FindNext(r)
Loop Until ff = r.Address
End If
Next
Next
End If
For i = 1 To UBound(a, 2)
If IsEmpty(a(4, i)) Then
With Workbooks(a(1, i))
.Activate
With .Sheets(a(2, i))
.Activate
.Range(a(3, i)).Select
End With
End With
a(4, i) = "n/a"
flag = True
Exit For
End If
Next
If Not flag Then MsgBox "No more"
End Sub