VBA to process until all criteria is matched

May 12, 2014 at 14:09:47
Specs: Windows 7
Sub notwosame()

' check to see if random pick is required
    If Sheets("COMP1").Range("G2") = "N" Then
' random pick is NOT required
        Exit Sub
     End If
     
' random pick IS required
    
    Range("D5:D68").Select
    Selection.Copy
    Range("E5").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

' change BBOWL!J2 to N
    Sheets("COMP1").Range("G2") = "N"
    Range("G2").Select
    
    If Sheets("COMP1").Range("S7") = Range("S9") Then
    If Sheets("COMP1").Range("S11") = Range("S13") Then
    If Sheets("COMP1").Range("S15") = Range("S17") Then
    If Sheets("COMP1").Range("S19") = Range("S21") Then
    If Sheets("COMP1").Range("S23") = Range("S25") Then
    If Sheets("COMP1").Range("S27") = Range("S29") Then
    If Sheets("COMP1").Range("S31") = Range("S33") Then
    If Sheets("COMP1").Range("S35") = Range("S37") Then
    If Sheets("COMP1").Range("S39") = Range("S41") Then
    If Sheets("COMP1").Range("S43") = Range("S45") Then
    If Sheets("COMP1").Range("S47") = Range("S49") Then
    If Sheets("COMP1").Range("S51") = Range("S53") Then
    If Sheets("COMP1").Range("S55") = Range("S57") Then
    If Sheets("COMP1").Range("S59") = Range("S61") Then
    If Sheets("COMP1").Range("S63") = Range("S65") Then
    If Sheets("COMP1").Range("S67") = Range("S69") Then
    If Sheets("COMP1").Range("S71") = Range("S73") Then
    If Sheets("COMP1").Range("S75") = Range("S77") Then
    If Sheets("COMP1").Range("S79") = Range("S81") Then
    If Sheets("COMP1").Range("S83") = Range("S85") Then
    If Sheets("COMP1").Range("S87") = Range("S89") Then
    If Sheets("COMP1").Range("S91") = Range("S93") Then
    If Sheets("COMP1").Range("S95") = Range("S97") Then
    If Sheets("COMP1").Range("S99") = Range("S101") Then
    If Sheets("COMP1").Range("S103") = Range("S105") Then
    If Sheets("COMP1").Range("S107") = Range("S109") Then
    If Sheets("COMP1").Range("S111") = Range("S113") Then
    If Sheets("COMP1").Range("S115") = Range("S117") Then
    If Sheets("COMP1").Range("S119") = Range("S121") Then
    If Sheets("COMP1").Range("S123") = Range("S125") Then
    If Sheets("COMP1").Range("S127") = Range("S129") Then
    If Sheets("COMP1").Range("S131") = Range("S133") Then
    
' change BBOWL!J2 to Y
    Sheets("COMP1").Range("G2") = "Y"
    
' random pick IS required
    
    Range("D5:D68").Select
    Selection.Copy
    Range("E5").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

' change BBOWL!J2 to N
    Sheets("COMP1").Range("G2") = "N"
    Range("G2").Select
    
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
    End Sub

Hi Guys, trying to create a VBA to assist with a small problem.
I have a list of words/text that are paired up, ie Range("S7") and Range("S9"), Range("S11") and Range("S13") , etc
There is a possibility that the same text will appear twice in a pairing.
I am trying to avoid this so am then trying to random generate again to alleviate this scenario

D5:D68 generates random numbers, these are copied to E5:E68 as values, F5:F68 is the RANK of E5:E68

Now I know my coding above is very long winded and most likely incorrect, but some assistance on how to go about what I require, and more importantly the coding required would be greatly appreciated


See More: VBA to process until all criteria is matched

Report •


#1
May 13, 2014 at 07:00:26
Based on what you have described in your post, your code doesn't make sense to me.

As far as I can tell, if S7 <> S9, we're done. Exit Sub, run no more.

The same holds true for any paired cells that don't match. Basically you have written a huge Nested If which will End as soon as a non-matching pair is found.

Is that what you want it to do?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 13, 2014 at 08:57:59
Hi DerbyDad03,
As I mentioned, I was really not to sure what I wanted to do and how to go about it.
After a little rethink and some thoughtful processing, I have managed to reach what I required without the code that I previously have shown.
Basically been messing around with the =IF function and reached what I needed.

Sorry to have wasted your time on this, and anyone elses.
Hope you are well

Thanks
Tony


Report •
Related Solutions


Ask Question