For i = Loop

[elgringo56]

Ok, I will try this way. I am in a For i loop. I want to test the value of Cell AP10 and if it is blank, I want skip over a bunch of code and go to the next i instruction to restart the loop. I cant figgure out how to do it. I tried a Select Case but that gives me a compile error at the next i instruction. HELP

[RET79]

a really rough quick guess but you need something alone the lines of

For i = 1 to n

If Isempty("AP10") = false then
'do code

End if
Next i

[ This Message was edited by: RET79 on 2002-05-01 18:16 ]

[elgringo56]

When I try this, I get a compile error on the Next i statment saying I have a next without a for

[elgringo56]

This is my code, and it doesn't work
For i = 1 To LoopRange
Range("AA6").Select
Selection.Copy
Range("AB6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("AA9").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("AB5:AB6"), CopyToRange:=Range("AP9:BB9"), Unique:=False
If IsEmpty("AP10") = False Then
Range("BE10:BE61").Select
Selection.Copy
Range("BB10:BB61").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("AP9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("AP9").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("AP10").Select
ActiveCell.CurrentRegion.Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("AP10:BB509").Select
Selection.ClearContents
Next i

[RET79]

Try putting an End IF just before Next i.

Sorry i have to go to bed now I'm sure others will help you if that doesn't work

RET79

[RET79]

Or Try this:

For i = 1 To LoopRange

' code

If [AP10] <> "" Then

'code
End if
Next i


Not certain if that will work as u want but worth a try, get yourself a VBA book and learn how to edit your code so it has less select selection stuff as its difficult to debug and understand. The macro recorder is great for learning how to do stuff but it doesnt leave you with the best code to work with and understand. Good luck

RET79

[ This Message was edited by: RET79 on 2002-05-01 18:15 ]

[RET79]

For i = 1 To LoopRange

Range("AA6").Copy
Range("AB6").PasteSpecial Paste:=xlValues
Range([AA9], [AA9].End(xlDown).End(xltoRight).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("AB5:AB6"), CopyToRange:=Range("AP9:BB9"), Unique:=False

If [AP10] <> "" Then

Range("BE10:BE61").Copy
Range("BB10:BB61").PasteSpecial Paste:=xlValues
Range("AP9").End(xlDown).Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).ClearContents
Range([Ap9], [AP9].End(xlToRight)).ClearContents
Range("AP10").CurrentRegion.Copy
Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
Range("AP10:BB509").ClearContents

End if
Next i



I tidied up your code a bit, should probably run quicker even.

RET79

[ This Message was edited by: RET79 on 2002-05-01 18:31 ]

[Jay Petrulis]

Hi,

I cannot follow what you are doing, but here is some of your code rewritten to remove as many .select statements that I can without knowing your data layout.

Sub test()
Dim LoopRange, i
LoopRange = 4

For i = 1 To LoopRange
    Range("AA6").Copy
    Range("AB6").PasteSpecial (xlValues)
    
    Range("AA9").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Range(Selection, Selection.End(xlToRight)).AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:= _
    Range("AB5:AB6"), CopyToRange:=Range("AP9:BB9"), Unique:=False
    
    If IsEmpty("AP10") = False Then
        Range("BE10:BE61").Copy
        Range("BB10:BB61").PasteSpecial (xlValues)
        
        Range("AP9").End(xlDown).Offset(1, 0).Select
        Range(Selection, Selection.End(xlToRight)).ClearContents
        
        Range("AP9").Select
        Range(Selection, Selection.End(xlToRight)).ClearContents
        
        Range("AP10").CurrentRegion.Copy
        Range("A1").End(xlDown).Offset(1, 0).PasteSpecial (xlValues)
        Range("AP10:BB509").ClearContents
    End If

Next i

End Sub

Please report back what you wish to do and where the problems remain.

Jay

EDIT: The following code removes all the select statements. Don't know if it would work, though.

Sub tester()
Dim LoopRange, i
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range

LoopRange = 4

For i = 1 To LoopRange
    Range("AB6") = Range("AA6")
    
    Set Rng1 = Range("AA9", Range("AA9").End(xlDown))
    Set Rng2 = Range(Rng1, Rng1.End(xlToRight))
    
    Rng2.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Range("AB5:AB6"), CopyToRange:=Range("AP9:BB9"), Unique:=False
    
    If IsEmpty("AP10") = False Then
        Range("BE10:BE61").Copy
        Range("BB10:BB61").PasteSpecial (xlValues)
        
        Set Rng3 = Range("AP9", Range("AP9").End(xlDown).Offset(1, 0))
        Range(Rng3, Rng3.End(xlToRight)).ClearContents
               
        Range("AP10").CurrentRegion.Copy
        Range("A1").End(xlDown).Offset(1, 0).PasteSpecial (xlValues)
        Range("AP10:BB509").ClearContents
    End If

Next i

End Sub

[ This Message was edited by: Jay Petrulis on 2002-05-01 18:54 ]

[elgringo56]

That got it, thanks millions

[elgringo56]

Jay. Thanks heaps, I will study your code. some of the selects and clears I was doing were to totally clear some cells that were produced by other formulas and therefore not blank even though they appeared blank. the paste value only does not leave a cell blank if the blank that is in the cell was developed by a formula, this has been a real headache for me for a long time.