Array Help
Array Help
(OP)
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
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





RE: Array Help
RE: Array Help
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
RE: Array Help
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
RE: Array Help
RE: Array Help
RE: Array Help
CODE -->
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 Subp.s. It's easier to see the code and structure if you indent
RE: Array Help
RE: Array Help
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
[indent]
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
RE: Array Help
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))