Results 1 to 10 of 10

IF and Else IF , Is there a better way to code this ?

Threaded View

UltimateNeo IF and Else IF , Is there a... 02-17-2019, 09:17 PM
6StringJazzer Re: Is there a better way? 02-17-2019, 09:28 PM
banaanas Re: IF and Else IF , Is there... 02-18-2019, 02:30 AM
UltimateNeo Re: IF and Else IF , Is there... 02-18-2019, 07:49 PM
Norie Re: IF and Else IF , Is there... 02-18-2019, 08:08 PM
UltimateNeo Re: IF and Else IF , Is there... 02-18-2019, 08:24 PM
Norie Re: IF and Else IF , Is there... 02-19-2019, 12:27 AM
UltimateNeo Because i was wanting to... 02-19-2019, 12:32 AM
Norie Re: IF and Else IF , Is there... 02-19-2019, 02:50 AM
UltimateNeo Re: IF and Else IF , Is there... 02-20-2019, 07:51 PM
  1. #1
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    209

    IF and Else IF , Is there a better way to code this ?

    I have some code that works fine , but I have to copy and paste the code many times. this can then get confusing when you are changing certain parts of the code.

    I was wondering if it could be put in to functions and then you just call the function u want ?

    Private Sub CommandButton1_Click()
     Dim NumArr As Object, i As Long, j As Long, rng As Range, cell As Range
       
       Application.ScreenUpdating = False
       
         With Sheets("Games")
    
    ElseIf MultiPage10.Value = 4 And Me.cbGames = "All" Then 
    Set NumArr = CreateObject("System.Collections.ArrayList")
             
             Set rng = .Range("All") ' this range of cells changes 
             For i = 5 To 28  ' this is number of rows so this can change
                NumArr.Clear
                j = 1
                Do Until j > 3
                   Set cell = RdmCell(rng)
                   ' Consider only ODD numbers
                   If cell.Value Mod 2 = 1 Then ' this changes for odd and even 
                      If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
                         NumArr.Add cell.Value
                         j = j + 1
                      End If
                   End If
                Loop
                Cells(i, 38).Resize(, 3) = NumArr.toarray ' this changes as is destination of data
             Next i
             For i = 5 To 28
                NumArr.Clear
                j = 1
                Do Until j > 3
                   Set cell = RdmCell(rng)
                   ' Consider only ODD numbers
                   If cell.Value Mod 2 = 0 Then
                      If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
                         NumArr.Add cell.Value
                         j = j + 1
                      End If
                   End If
                Loop
                Cells(i, 41).Resize(, 3) = NumArr.toarray
             Next i
    End If
       End With
       Application.ScreenUpdating = True
    
    End Sub
    
    Function RdmCell(rng As Range) As Range
        Set RdmCell = rng(Int(Rnd * rng.Count) + 1)
    End Function
    Last edited by UltimateNeo; 02-17-2019 at 09:58 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1