INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

RE: Array Help

I don't see any need for the array.

RE: Array Help

(OP)

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

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

RE: Array Help

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.

RE: Array Help

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

RE: Array Help

Your macro can be simplified to the following:

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 Sub 

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

RE: Array Help

(OP)
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.

RE: Array Help

(OP)
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
[indent]
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

RE: Array Help

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close