Array Help

[RYANspaceHall]

I am trying to hide cells that are in my array. I am new to VBA so I am having a little trouble. My code is below. Thanks in advance! My array is in columns E-S and the cells contain letters E-S in it.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$3" Then

Dim i As Long
i = 0
Dim MyArray() As String

For i = 0 To 14
MyArray(i) = Range("E1:S1").Value
If 0 <= i <= 14 Then

If (Target.Value) = "yes" Then

Columns("MyArray(i)").EntireColumn.Hidden = True

ElseIf (Target.Value) = "no" Then
Columns("MyArray(i)").EntireColumn.Hidden = False

End If
End If

Next i

End If

End Sub

 

[MintJulep]

I don't see any need for the array.

 

[RYANspaceHall]

Here is the updated version. I am probably over complicating it. My goal is to have one group of cells close if it has data and have the other group of cells open if it doesn't have data. (The data is 'RUN' in this situation) This will search and see if the column (j) has data in cell(4,i). If so then j will close The user types in 'yes' or 'no' to open and close it. If there is an easier way let me know.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$3" Then

Dim i As Integer
Dim j As Integer
i = 5
j = 0
Dim MyArray() As String

For i = 5 To 19
For j = 0 To 14
MyArray(j) = Range("E1:S1").Value

If (Target.Value) = "yes" And Worksheet.Cells(4, i).Value <> "RUN" Then

'I want MyArray(j) to be the corresponding letter in my array
Columns("MyArray(j)").EntireColumn.Hidden = True

ElseIf (Target.Value) = "no" And Worksheet.Cells(4, i).Value = "RUN" Then
Columns("MyArray(j)").EntireColumn.Hidden = False

End If

Next j
Next i

End If

End Sub

 

[RYANspaceHall]

Thanks for your help I figured it out.... And I didn't use an array; just over thought it.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$3" Then

Dim i As Integer
i = 5

For i = 5 To 19

If (Target.Value) = "close RUN" And Cells(4, i).Value = "RUN" Then
Cells(1, i).EntireColumn.Hidden = True

ElseIf (Target.Value) = "close RUN" And Cells(4, i).Value <> "RUN" Then
Cells(1, i).EntireColumn.Hidden = False

ElseIf (Target.Value) = "view RUN" And Cells(4, i).Value = "RUN" Then
Cells(1, i).EntireColumn.Hidden = False

ElseIf (Target.Value) = "view RUN" And Cells(4, i).Value <> "RUN" Then
Cells(1, i).EntireColumn.Hidden = True

End If

Next i

End If

End Sub

 

[MintJulep]

It would seem much simpler to place a few buttons for the user to click instead of relying on them typing some "case SENSITIVE" text.

 

[RYANspaceHall]

Thanks, I have a drop down list for them to choose from.

 

[zelgar]

Your macro can be simplified to the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$3" Then

     Dim i As Integer

     For i = 5 To 19

          If ((Target.Value) = "close RUN" And Cells(4, i).Value = "RUN") or _
             ((Target.Value) = "view RUN" And Cells(4, i).Value <> "RUN") Then
               Cells(1, i).EntireColumn.Hidden = True
          else
               Cells(1, i).EntireColumn.Hidden = False
          End If

     Next i

End If

End Sub

p.s. It's easier to see the code and structure if you indent

 

[RYANspaceHall]

Thanks I will implement your code in with what I have now. I had it indented but when I copied it it left justified everything.

 

[RYANspaceHall]

I am having trouble when I run this. It ran Perfect when I Set myRng = ActiveSheet.Range(Cells(1, j), Cells(1, 19)). When I changed is to shB (which is Sheet2) then I get an error, Method Range of Objects. I believe I have a problem defining the range. Just need some help figuring it out. I also understand that my code isn't efficient yet. I am still in the learning process. Thanks again.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$3" Then

​

Dim i As Integer

​

Dim j As Integer

​

Dim myRng As Range

​

Dim shB As Worksheet

​

i = 5

​

j = 5

​

Set shB = Sheets("Sheet2")

For i = 5 To 19

For j = 5 To 19

If (Target.Value) = "close RUN" And shB.Cells(4, i) <> "Run" Then

​

j = i + 1

​

Set myRng = shB.Range(Cells(1, j), Cells(1, 19))

​

​

j = 19
End If

Next j


'This part runs perfect

If (Target.Value) = "close RUN" And shB.Cells(4, i).Value = "RUN" Then
shB.Cells(1, i).EntireColumn.Hidden = True
myRng.EntireColumn.Hidden = False

ElseIf (Target.Value) = "close RUN" And shB.Cells(4, i).Value <> "RUN" Then
myRng.EntireColumn.Hidden = True

ElseIf (Target.Value) = "view RUN" And shB.Cells(4, i).Value = "RUN" Then
shB.Cells(1, i).EntireColumn.Hidden = False

ElseIf (Target.Value) = "view RUN" And shB.Cells(4, i).Value <> "RUN" Then
shB.Cells(1, i).EntireColumn.Hidden = True


End If

Next i

End If
End Sub​

 

[RYANspaceHall]

And once again I figured it out.

Set myRng = shB.Range(Cells(1, j), Cells(1, 19))

needed to be

Set myRng = shB.Range(shB.Cells(1, j), shB.Cells(1, 19))