2 Dimensional Array Help Please

R

radar_jynx

New Member
Joined
Mar 16, 2009
Messages
18
I Can figure out how to simplify this array.

nbox is my text input box on myform

anyideas would be much appreciated

Select Case MyForm.nbox.Value

Case 101

If Range("F12,E13").Value = 0 Then
Range("F12,E13").Value = Str(t)
ElseIf Range("F13").Value = 0 Then
Range("G12,F13").Value = Str(t)
ElseIf Range("G13").Value = 0 Then
Range("H12,G13").Value = Str(t)
ElseIf Range("H13").Value = 0 Then
Range("I12,H13").Value = Str(t)
ElseIf Range("I13").Value = 0 Then
Range("J12,I13").Value = Str(t)
ElseIf Range("J13").Value = 0 Then
Range("K12,J13").Value = Str(t)
ElseIf Range("K13").Value = 0 Then
Range("L12,K13").Value = Str(t)
ElseIf Range("L13").Value = 0 Then
Range("M12,L13").Value = Str(t)
ElseIf Range("M13").Value = 0 Then
Range("N12,M13").Value = Str(t)
ElseIf Range("N13").Value = 0 Then
Range("N13").Value = Str(t)
End If
MyForm.nbox.Value = ""
Case 102

If Range("F14,E15").Value = 0 Then
Range("F14,E15").Value = Str(t)
ElseIf Range("F15").Value = 0 Then
Range("G14,F15").Value = Str(t)
ElseIf Range("G15").Value = 0 Then
Range("H14,G15").Value = Str(t)
ElseIf Range("H15").Value = 0 Then
Range("I14,H15").Value = Str(t)
ElseIf Range("I15").Value = 0 Then
Range("J14,I15").Value = Str(t)
ElseIf Range("J15").Value = 0 Then
Range("K14,J15").Value = Str(t)
ElseIf Range("K15").Value = 0 Then
Range("L14,K15").Value = Str(t)
ElseIf Range("L15").Value = 0 Then
Range("M14,L15").Value = Str(t)
ElseIf Range("M15").Value = 0 Then
Range("N14,M15").Value = Str(t)
ElseIf Range("N15").Value = 0 Then
Range("N15").Value = Str(t)
End If
MyForm.nbox.Value = ""

Case 103

If Range("F16,E17").Value = 0 Then
Range("F16,E17").Value = Str(t)
ElseIf Range("F17").Value = 0 Then
Range("G16,F17").Value = Str(t)
ElseIf Range("G17").Value = 0 Then
Range("H16,G17").Value = Str(t)
ElseIf Range("H17").Value = 0 Then
Range("I16,H17").Value = Str(t)
ElseIf Range("I17").Value = 0 Then
Range("J16,I17").Value = Str(t)
ElseIf Range("J17").Value = 0 Then
Range("K16,J17").Value = Str(t)
ElseIf Range("K17").Value = 0 Then
Range("L16,K17").Value = Str(t)
ElseIf Range("L17").Value = 0 Then
Range("M16,L17").Value = Str(t)
ElseIf Range("M17").Value = 0 Then
Range("N16,M17").Value = Str(t)
ElseIf Range("N17").Value = 0 Then
Range("N17").Value = Str(t)
End If
MyForm.nbox.Value = ""

Case 104

If Range("F18,E19").Value = 0 Then
Range("F18,E19").Value = Str(t)
ElseIf Range("F19").Value = 0 Then
Range("G18,F19").Value = Str(t)
ElseIf Range("G19").Value = 0 Then
Range("H18,G19").Value = Str(t)
ElseIf Range("H19").Value = 0 Then
Range("I18,H19").Value = Str(t)
ElseIf Range("I19").Value = 0 Then
Range("J18,I19").Value = Str(t)
ElseIf Range("J19").Value = 0 Then
Range("K18,J19").Value = Str(t)
ElseIf Range("K19").Value = 0 Then
Range("L18,K19").Value = Str(t)
ElseIf Range("L19").Value = 0 Then
Range("M18,L19").Value = Str(t)
ElseIf Range("M19").Value = 0 Then
Range("N18,M19").Value = Str(t)
ElseIf Range("N19").Value = 0 Then
Range("N19").Value = Str(t)
End If
MyForm.nbox.Value = ""

End Select
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Duck! It's a wall of text!

hehe seriously, I actually have a similar question, and feel like I can be helped by the answer to this one. Trouble is it's very difficult to decipher text walls.

Try tabbing out dependent lines, and always wrap everything in code tags. Also, consider adding comments to lines at logical places makes it easier to grasp at a glance what the code is doing.

The reformatted code would look something like this

Code: 
Select Case MyForm.nbox.Value

Case 101
'(guess) look for ranges with data
If Range("F12,E13").Value = 0 Then
     Range("F12,E13").Value = Str(t)
ElseIf Range("F13").Value = 0 Then
     Range("G12,F13").Value = Str(t)
ElseIf Range("G13").Value = 0 Then
     Range("H12,G13").Value = Str(t)
ElseIf Range("H13").Value = 0 Then
     Range("I12,H13").Value = Str(t)

etc etc

code tags can be entered by selecting the text you want as code, then clicking the button at top that looks like a little text bubble just to the left of the pound sign

Cheers,
Mike
 
Upvote 0
In words, what is that code doing. I suspect that that large a stack of regularly varying If..ElseIf can be simplified considerably.

Also, I get a typemismatch on
Code: 
If Range("F12,E13").Value = 0
What is that supposed to test, F12=0 AND E13 = 0 or ???
 
Upvote 0
I the above interpretation of If Range("F12,E13").Value = 0 is correct (called "starting dual 0's" in a comment in the code, I think this will do what you want.
Code: 
Dim zeroFound As Range
Dim rowToSearch As Long

    Select Case Val(myForm.nbox.Value)
        Case Is = 101
            rowToSearch = 13
        Case Is = 102
            rowToSearch = 15
        Case Is = 103
            rowToSearch = 17
        Case Else
            rowToSearch = 1
    End Select

    If 1 < rowToSearch Then
        With Rows(rowToSearch)
            Rem test starting dual 0's
            If Cells(rowToSearch, "F") = 0 And Cells(rowToSearch - 1, "E") = 0 Then
                Set zeroFound = Cells(rowToSearch, "F")
            Else
                Rem find first zero on key row
                With .Range("F1:m1")
                    On Error Resume Next
                    Set zeroFound = .Find(What:="0", After:=.Columns(.Columns.Count), LookIn:=xlValues, LookAt:= _
                        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                    On Error GoTo 0
                End With
            End If

            If zeroFound Is Nothing Then
                If .Range("n1").Value = 0 Then .Range("n1") = CStr(t)
            Else
                zeroFound.Value = CStr(t)
                zeroFound.Offset(-1, 1).Value = CStr(t)
            End If
        End With
    End If
    
    myForm.nbox.Value = vbNullString
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
283
offthelip
O
F
  • Solved
Show result from other columns (without knowing which of the 4 columns has value in it)
Replies
4
Views
40
Fluff
Fluff
N
  • Question
Count - specific consecutive order
Replies
6
Views
512
Peter_SSs
Peter_SSs
Romano_odK
  • Solved
How to remove al line with VBA when the values is "OK"
Replies
9
Views
253
Peter_SSs
Peter_SSs
imback2nite
  • Solved
Help shorten loooong code?
Replies
2
Views
367
imback2nite
imback2nite

Forum statistics

Threads
1,214,983
Messages
6,122,582
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top