Problem with code
 

My $C$4 is a List (Jan, Feb, ...Dec)
I have a table (Range) mydates
as follows:

Name Month# Column#Start
Jan 1 3
Feb 2 5
Mar 3 7
Apr 4 9
May 5 11
Jun 6 13
Jly 7 15
Aug 8 17
Sep 9 19
Oct 10 21
Nov 11 23
Dec 12 25


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next
Dim vcol1 As Integer
Dim vcol2 As Integer
Set mydates = Sheets("Sheet1").Range("A2:C13")
If Target.Address = "$C$4" Then
vcol1 = Application.VLookup(Target.Value, mydates, 3, False)
vcol2 = vcol1 + 1
For Each c In Range("E7:Z7")
If Val(c.Column) < vcol1 Or c.Column < vcol2 Then
c.EntireColumn.Hidden = True
End If
Next
End If

But the above is hiding EVERYTHING -- Trying to work out for 3 hrs... grrrrr
withlut success///

Problem with code
 

It looks like you are returning the Column#Start with your VLookup.

vcol1 = Application.VLookup(Target.Value, mydates, 3, False)

The 3 say use the 3rd col in mydates to search. Is that what you want?

"Jim May" wrote:

My $C$4 is a List (Jan, Feb, ...Dec)
I have a table (Range) mydates
as follows:

Name Month# Column#Start
Jan 1 3
Feb 2 5
Mar 3 7
Apr 4 9
May 5 11
Jun 6 13
Jly 7 15
Aug 8 17
Sep 9 19
Oct 10 21
Nov 11 23
Dec 12 25


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next
Dim vcol1 As Integer
Dim vcol2 As Integer
Set mydates = Sheets("Sheet1").Range("A2:C13")
If Target.Address = "$C$4" Then
vcol1 = Application.VLookup(Target.Value, mydates, 3, False)
vcol2 = vcol1 + 1
For Each c In Range("E7:Z7")
If Val(c.Column) < vcol1 Or c.Column < vcol2 Then
c.EntireColumn.Hidden = True
End If
Next
End If

But the above is hiding EVERYTHING -- Trying to work out for 3 hrs... grrrrr
withlut success///

Problem with code
 

Yes, Each month has two columns, for example there is a Jan Number column
then a Jan Text column, Feb Number, then Feb Text and so on...

"JLGWhiz" wrote:

It looks like you are returning the Column#Start with your VLookup.

vcol1 = Application.VLookup(Target.Value, mydates, 3, False)

The 3 say use the 3rd col in mydates to search. Is that what you want?

"Jim May" wrote:

My $C$4 is a List (Jan, Feb, ...Dec)
I have a table (Range) mydates
as follows:

Name Month# Column#Start
Jan 1 3
Feb 2 5
Mar 3 7
Apr 4 9
May 5 11
Jun 6 13
Jly 7 15
Aug 8 17
Sep 9 19
Oct 10 21
Nov 11 23
Dec 12 25


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next
Dim vcol1 As Integer
Dim vcol2 As Integer
Set mydates = Sheets("Sheet1").Range("A2:C13")
If Target.Address = "$C$4" Then
vcol1 = Application.VLookup(Target.Value, mydates, 3, False)
vcol2 = vcol1 + 1
For Each c In Range("E7:Z7")
If Val(c.Column) < vcol1 Or c.Column < vcol2 Then
c.EntireColumn.Hidden = True
End If
Next
End If

But the above is hiding EVERYTHING -- Trying to work out for 3 hrs... grrrrr
withlut success///

Problem with code
 

Correction: The 3 says return the data from that column in mydates.

"Jim May" wrote:

My $C$4 is a List (Jan, Feb, ...Dec)
I have a table (Range) mydates
as follows:

Name Month# Column#Start
Jan 1 3
Feb 2 5
Mar 3 7
Apr 4 9
May 5 11
Jun 6 13
Jly 7 15
Aug 8 17
Sep 9 19
Oct 10 21
Nov 11 23
Dec 12 25


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next
Dim vcol1 As Integer
Dim vcol2 As Integer
Set mydates = Sheets("Sheet1").Range("A2:C13")
If Target.Address = "$C$4" Then
vcol1 = Application.VLookup(Target.Value, mydates, 3, False)
vcol2 = vcol1 + 1
For Each c In Range("E7:Z7")
If Val(c.Column) < vcol1 Or c.Column < vcol2 Then
c.EntireColumn.Hidden = True
End If
Next
End If

But the above is hiding EVERYTHING -- Trying to work out for 3 hrs... grrrrr
withlut success///

Problem with code
 

Jim, your Range $C$4 looks like it is within the scope of your mydates range
A2:C13, unless these ranges are on separate sheets. If they are on separate
sheets then the code needs to be modified to reflect that. Otherwise, the
code, as written is saying, if C4 is the target then return the value that is
selected. Which
really don't make much sense.

Maybe if you could explain what you are trying to accomplish I could offer a
solution.

"Jim May" wrote:

Yes, Each month has two columns, for example there is a Jan Number column
then a Jan Text column, Feb Number, then Feb Text and so on...

"JLGWhiz" wrote:

It looks like you are returning the Column#Start with your VLookup.

vcol1 = Application.VLookup(Target.Value, mydates, 3, False)

The 3 say use the 3rd col in mydates to search. Is that what you want?

"Jim May" wrote:

My $C$4 is a List (Jan, Feb, ...Dec)
I have a table (Range) mydates
as follows:

Name Month# Column#Start
Jan 1 3
Feb 2 5
Mar 3 7
Apr 4 9
May 5 11
Jun 6 13
Jly 7 15
Aug 8 17
Sep 9 19
Oct 10 21
Nov 11 23
Dec 12 25


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next
Dim vcol1 As Integer
Dim vcol2 As Integer
Set mydates = Sheets("Sheet1").Range("A2:C13")
If Target.Address = "$C$4" Then
vcol1 = Application.VLookup(Target.Value, mydates, 3, False)
vcol2 = vcol1 + 1
For Each c In Range("E7:Z7")
If Val(c.Column) < vcol1 Or c.Column < vcol2 Then
c.EntireColumn.Hidden = True
End If
Next
End If

But the above is hiding EVERYTHING -- Trying to work out for 3 hrs... grrrrr
withlut success///

Problem with code
 

Thanks JLGWhiz;
I found a different way to acheive my solution.
Jim

"JLGWhiz" wrote:

Jim, your Range $C$4 looks like it is within the scope of your mydates range
A2:C13, unless these ranges are on separate sheets. If they are on separate
sheets then the code needs to be modified to reflect that. Otherwise, the
code, as written is saying, if C4 is the target then return the value that is
selected. Which
really don't make much sense.

Maybe if you could explain what you are trying to accomplish I could offer a
solution.

"Jim May" wrote:

Yes, Each month has two columns, for example there is a Jan Number column
then a Jan Text column, Feb Number, then Feb Text and so on...

"JLGWhiz" wrote:

It looks like you are returning the Column#Start with your VLookup.

vcol1 = Application.VLookup(Target.Value, mydates, 3, False)

The 3 say use the 3rd col in mydates to search. Is that what you want?

"Jim May" wrote:

My $C$4 is a List (Jan, Feb, ...Dec)
I have a table (Range) mydates
as follows:

Name Month# Column#Start
Jan 1 3
Feb 2 5
Mar 3 7
Apr 4 9
May 5 11
Jun 6 13
Jly 7 15
Aug 8 17
Sep 9 19
Oct 10 21
Nov 11 23
Dec 12 25


Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next
Dim vcol1 As Integer
Dim vcol2 As Integer
Set mydates = Sheets("Sheet1").Range("A2:C13")
If Target.Address = "$C$4" Then
vcol1 = Application.VLookup(Target.Value, mydates, 3, False)
vcol2 = vcol1 + 1
For Each c In Range("E7:Z7")
If Val(c.Column) < vcol1 Or c.Column < vcol2 Then
c.EntireColumn.Hidden = True
End If
Next
End If

But the above is hiding EVERYTHING -- Trying to work out for 3 hrs... grrrrr
withlut success///