| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/// |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/// |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/// |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/// |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/// |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/// |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code Problem | Excel Programming | |||
| Little problem with this code... | Excel Discussion (Misc queries) | |||
| If/then code problem | Excel Programming | |||
| Problem with the following code | Excel Programming | |||
| Code problem | Excel Programming | |||
Linear Mode
