Inner/Outer Loop

[DarkOwl]

I tried to make a macro that searches a table for a record with a certain value, and when it does find it, an inner loop then searches another table and changes a value offset from it.
The thing is, i cannot work out how to get the loops to work.

Here is the code:

Sub CleanOrders()

Dim Ordernumber As String

Application.ScreenUpdating = False

Sheets("Shipping and Payment").Select
Range("F9").Select

Do Until ActiveCell = ""
If ActiveCell = ActiveSheet.Range("y6") Then


ActiveCell.Offset(0, -4).Select
Ordernumber = ActiveCell.Value
Selection.EntireRow.Delete


Sheets("Invoice records").Select
Range("A4").Select
Do While ActiveCell <> ""
If ActiveCell.Text = Ordernumber Then
ActiveCell.Offset(26, 7).Select
ActiveCell.FormulaR1C1 = "Shipped"
GoTo 1

End If
ActiveCell.Offset(1, 0).Activate
Loop

1
Else
ActiveCell.Offset(1, 0).Select

Loop

Msgbox ("Shipped orders have been removed")

End Sub


Can anyone help?

 

[jindon]

Hi

Sheets("Shipping and Payment").Select
Range("F9").Select

Do Until ActiveCell = ""
If ActiveCell = ActiveSheet.Range("y6") Then

It will never get into the inner loop, because of above setting.

starting activecell is "F9" and it goes down.

ActiveCell.Offset(1, 0).Activate
Loop

rgds,
jindon

 

[acw]

Hi

A couple of other things.

1) There is no End If for the first IF statement. Change
Else
ActiveCell.Offset(1, 0).Select
Loop

to
Else
ActiveCell.Offset(1, 0).Select
end if
Loop

2) If there is a match, the program is based on the sheet Invoice Records. It does not transfer to the sheet Shipping and Payment before continuing.


Tony

 

[chiello]

I have prepared the following code:


Sub CleanOrders()
Dim SearchRange1 As Range, SearchRange2 As Range, iCELL As Range
Dim StrToFind As String, Ordernumber As String

'Application.ScreenUpdating = False

'Show all data
On Error Resume Next
Sheets("Shipping and Payment").ShowAllData
Sheets("Invoice records").ShowAllData
On Error GoTo 0

Sheets("Shipping and Payment").Select
'Set Data Range in Sheet "Shipping and Payment"
Set SearchRange1 = Range(Range("F9"), Range("F65536").End(xlUp))

'String to find
StrToFind = Range("Y6")

'Filter respect to "StrToFind"
Range(Range("F8"), Range("F65536").End(xlUp)).AutoFilter Field:=1, Criteria1:=StrToFind

Sheets("Invoice records").Select
'Set Data Range in Sheet "Invoice records"
Set SearchRange2 = Range(Range("A4"), Range("A65536").End(xlUp))

On Error GoTo Skip_All 'Not sure if "StrToFind" is available
For Each iCELL In SearchRange1.SpecialCells(xlCellTypeVisible)
Ordernumber = iCELL.Offset(0, -4) 'Criteria for filter
'Filter in respect to "Ordernumber"
Range(Range("A3"), Range("A65536").End(xlUp)).AutoFilter Field:=1, Criteria1:=Ordernumber
On Error Resume Next 'Not sure if "Ordernumber" is available
SearchRange2.SpecialCells(xlCellTypeVisible).Offset(26, 7) = "Shipped"
Next iCELL
'Clear Filter in sheet "Invoice records"
Range(Range("A3"), Range("A65536").End(xlUp)).AutoFilter

Sheets("Shipping and Payment").Select
SearchRange1.SpecialCells(xlCellTypeVisible).EntireRow.Delete

SearchRange1.AutoFilter
MsgBox ("Shipped orders have been removed")
Exit Sub
Skip_All:
SearchRange1.AutoFilter

End Sub

Does it work??

Post for feedback

Ciao

 

[DarkOwl]

Thank you for the code, but when i run it, my excel just feezes as if its stuck in a loop.

 

[DarkOwl]

Anyone fix my orignal code even?

 

[Norie]

Does this work at all?

Sub CleanOrders()
Dim Ordernumber As String
Dim rng1 As Range
Dim rng2 As Range

    Application.ScreenUpdating = False
    
    Set rng1 = Sheets("Shipping and Payment").Range("F9")
    
    Do Until rng1 = ""
    
        If rng1 = ActiveSheet.Range("y6") Then
    
            Ordernumber = rng1.Offset(0, -4)
    
            rng1.EntireRow.Delete
    
            Set rng2 = Sheets("Invoice records").Range("A4")
            
            Do While rng2 <> ""
                If rng2 = Ordernumber Then
                    rng2.Offset(26, 7) = "Shipped"
                End If
            
                Set rng2 = rng2.Offset(1, 0)
            Loop
    
        Else
            Set rng1 = rng1.Offset(1, 0)
        End If
    Loop
    
    Application.ScreenUpdating = True
    MsgBox ("Shipped orders have been removed")

End Sub

By the way this is totally untested and I think you might have a problem when a row is deleted as then rng1 will also be deleted.

Perhaps you could explain further in words what the code is supposed to do and also how your data is laid out.

 

[DarkOwl]

I have this table, showing the staus of orders. When i start this macro "Clean Orders", it goes through a column and finds each 'record' which has the value equal to "SHipped" (placed in cell y6).

It is supposed to then delete the row, go to "Invoice records" and search through the records there from the order number. The first saved order number is in Cell A4 and then at 28 row intervals.

I changed the offset to (28,0) in the above code, but when i run the macro in step into, it doesnt seem to work still.

I can send the file if it still isnt to clear.

Thanks in advance

 

[acw]

Darkowl

Check you PM.


Tony