Now to unhide columns ... and test for upper/lower case

I've got two more questions ...

Having the routine to open the rows, I need to apply that to opening the
required columns. I assume that the columns are referred to numerically,
rather than alphabetically? When I run the routine, the first part runs fine
(opening rows), but the second part generates the error message :

            Run-time error '1004':
            Application-defined or object-defined error
(This occurred with the two routines below combined as one)

Seperating them ( as pasted here ) and running it on its' own gives the
error message:

            400

Any help for this ?

Second question ... Can the line "If RowRange.Cells(r, 1).Value = "X" Then"
test for multiple situations, such as upper & lower case, or must it be a
seperate "If" statement?

Once again, I thank you all.

Sub RowUnhide()

'This part opens 2 rows for each employee, in 1st & 2nd weeks

Dim RowRange As Range
Dim r1 As Integer, r2 As Integer, r3 As Integer, r4 As Integer
Set RowRange = Worksheets("Employees").Range("D3:D22")
For r = 3 To 22
If RowRange.Cells(r, 1).Value = "X" Then
r1 = 44 + (2 * r)
r2 = 44 + (2 * r) + 1
r3 = 294 + (2 * r)
r4 = 294 + (2 * r) + 1
Worksheets("PayPeriod_01").Rows(r1 & ":" & r2).Hidden = False
Worksheets("PayPeriod_01").Rows(r3 & ":" & r4).Hidden = False

End If
Next

'second routine placed here, without the "Sub ColUnhide()" part

End Sub
-------------------------------------------------------------------
Sub ColUnhide()
'This part opens 2 columns for each job in progress

Dim ColRange As Range
Dim c1 As Integer, c2 As Integer
Set ColRange = Worksheets("Jobs").Range("F4:F100")
For c = 4 To 100
If ColRange.Cells(c, 1).Value = "X" Then
c1 = -3 + (2 * c)
c2 = -3 + (2 * c) + 1
Worksheets("PayPeriod_01").Columns(c1 & ":" & c2).Hidden = False

End If
Next

End Sub



0
12/17/2003 4:23:10 AM
excel 39879 articles. 2 followers. Follow

4 Replies
513 Views

Similar Articles

[PageSpeed] 13

A very cursory look suggests that you don't increase your r after the
procedure.
r=r+1

As to the testing for case you could use
>If RowRange.Cells(r, 1).Value = "X" Then
If ucase(RowRange.Cells(r, 1)) = "X" Then

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Brian Belliveau" <brian-belliveauNO@SPAMshaw.ca> wrote in message
news:OSQDb.728035$6C4.226927@pd7tw1no...
> I've got two more questions ...
>
> Having the routine to open the rows, I need to apply that to opening the
> required columns. I assume that the columns are referred to numerically,
> rather than alphabetically? When I run the routine, the first part runs
fine
> (opening rows), but the second part generates the error message :
>
>             Run-time error '1004':
>             Application-defined or object-defined error
> (This occurred with the two routines below combined as one)
>
> Seperating them ( as pasted here ) and running it on its' own gives the
> error message:
>
>             400
>
> Any help for this ?
>
> Second question ... Can the line "If RowRange.Cells(r, 1).Value = "X"
Then"
> test for multiple situations, such as upper & lower case, or must it be a
> seperate "If" statement?
>
> Once again, I thank you all.
>
> Sub RowUnhide()
>
> 'This part opens 2 rows for each employee, in 1st & 2nd weeks
>
> Dim RowRange As Range
> Dim r1 As Integer, r2 As Integer, r3 As Integer, r4 As Integer
> Set RowRange = Worksheets("Employees").Range("D3:D22")
> For r = 3 To 22
> If RowRange.Cells(r, 1).Value = "X" Then
> r1 = 44 + (2 * r)
> r2 = 44 + (2 * r) + 1
> r3 = 294 + (2 * r)
> r4 = 294 + (2 * r) + 1
> Worksheets("PayPeriod_01").Rows(r1 & ":" & r2).Hidden = False
> Worksheets("PayPeriod_01").Rows(r3 & ":" & r4).Hidden = False
>
> End If
> Next
>
> 'second routine placed here, without the "Sub ColUnhide()" part
>
> End Sub
> -------------------------------------------------------------------
> Sub ColUnhide()
> 'This part opens 2 columns for each job in progress
>
> Dim ColRange As Range
> Dim c1 As Integer, c2 As Integer
> Set ColRange = Worksheets("Jobs").Range("F4:F100")
> For c = 4 To 100
> If ColRange.Cells(c, 1).Value = "X" Then
> c1 = -3 + (2 * c)
> c2 = -3 + (2 * c) + 1
> Worksheets("PayPeriod_01").Columns(c1 & ":" & c2).Hidden = False
>
> End If
> Next
>
> End Sub
>
>
>


0
Don
12/17/2003 2:22:48 PM
One way is to just use the first column and resize it through the last column:

Option Explicit
Sub ColUnhide()
'This part opens 2 columns for each job in progress

    Dim ColRange As Range
    Dim c As Long
    Dim c1 As Integer  
    'don't need this line   c2 As Integer
    Set ColRange = Worksheets("Jobs").Range("F4:F100")
    For c = 4 To 100
        If ColRange.Cells(c, 1).Value = "" Then
            c1 = -3 + (2 * c)
            'don't need this line anymore  c2 = -3 + (2 * c) + 1
            Worksheets("PayPeriod_01").Columns(c1).Resize(, 2).Hidden = False
        End If
    Next c
End Sub


====
But I think you may have a bug in your first sub, too.

These lines:

Set RowRange = Worksheets("Employees").Range("D3:D22")
For r = 3 To 22
   If RowRange.Cells(r, 1).Value = "X" Then

May not be doing what you want.

Put a msgbox line in it like:

Sub test()

Dim RowRange As Range
Dim r As Long
Set RowRange = Worksheets("Employees").Range("D3:D22")
For r = 3 To 22
   MsgBox RowRange.Cells(r, 1).Address
Next r
End Sub

and you'll see you're looking at D5 when r = 1.  Is that what you meant?

That .cells(r,1) takes its position relative to the top left cell of RowRange.

If you wanted to loop through the rows in the range:

for r = 1 to rowrange.rows.count
  msgbox rowrange.cells(r,1).address
next r

Will loop through the first row of the range to the last row in that range.





Brian Belliveau wrote:
> 
> I've got two more questions ...
> 
> Having the routine to open the rows, I need to apply that to opening the
> required columns. I assume that the columns are referred to numerically,
> rather than alphabetically? When I run the routine, the first part runs fine
> (opening rows), but the second part generates the error message :
> 
>             Run-time error '1004':
>             Application-defined or object-defined error
> (This occurred with the two routines below combined as one)
> 
> Seperating them ( as pasted here ) and running it on its' own gives the
> error message:
> 
>             400
> 
> Any help for this ?
> 
> Second question ... Can the line "If RowRange.Cells(r, 1).Value = "X" Then"
> test for multiple situations, such as upper & lower case, or must it be a
> seperate "If" statement?
> 
> Once again, I thank you all.
> 
> Sub RowUnhide()
> 
> 'This part opens 2 rows for each employee, in 1st & 2nd weeks
> 
> Dim RowRange As Range
> Dim r1 As Integer, r2 As Integer, r3 As Integer, r4 As Integer
> Set RowRange = Worksheets("Employees").Range("D3:D22")
> For r = 3 To 22
> If RowRange.Cells(r, 1).Value = "X" Then
> r1 = 44 + (2 * r)
> r2 = 44 + (2 * r) + 1
> r3 = 294 + (2 * r)
> r4 = 294 + (2 * r) + 1
> Worksheets("PayPeriod_01").Rows(r1 & ":" & r2).Hidden = False
> Worksheets("PayPeriod_01").Rows(r3 & ":" & r4).Hidden = False
> 
> End If
> Next
> 
> 'second routine placed here, without the "Sub ColUnhide()" part
> 
> End Sub
> -------------------------------------------------------------------
> Sub ColUnhide()
> 'This part opens 2 columns for each job in progress
> 
> Dim ColRange As Range
> Dim c1 As Integer, c2 As Integer
> Set ColRange = Worksheets("Jobs").Range("F4:F100")
> For c = 4 To 100
> If ColRange.Cells(c, 1).Value = "X" Then
> c1 = -3 + (2 * c)
> c2 = -3 + (2 * c) + 1
> Worksheets("PayPeriod_01").Columns(c1 & ":" & c2).Hidden = False
> 
> End If
> Next
> 
> End Sub

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/17/2003 3:20:16 PM
Thank You Don

"Don Guillett" <donaldb@281.com> wrote in message >
> As to the testing for case you could use
> >If RowRange.Cells(r, 1).Value = "X" Then
> If ucase(RowRange.Cells(r, 1)) = "X" Then



0
12/23/2003 1:58:36 PM
Thank You Dave

I find it odd that columns & rows are treated differently ( or at least
that's how I've ended up)

And, yes, the row opener needed to count from 1 - 19 rather than from 3 -
22.

( And now I try to get the one code to work for a number of sheets ! )

Brian

"Dave Peterson" <ec35720@msn.com> wrote in message
news:3FE07430.6883CA8D@msn.com...
> One way is to just use the first column and resize it through the last
column:
>
> Option Explicit
> Sub ColUnhide()
> 'This part opens 2 columns for each job in progress
>
>     Dim ColRange As Range
>     Dim c As Long
>     Dim c1 As Integer
>     'don't need this line   c2 As Integer
>     Set ColRange = Worksheets("Jobs").Range("F4:F100")
>     For c = 4 To 100
>         If ColRange.Cells(c, 1).Value = "" Then
>             c1 = -3 + (2 * c)
>             'don't need this line anymore  c2 = -3 + (2 * c) + 1
>             Worksheets("PayPeriod_01").Columns(c1).Resize(, 2).Hidden =
False
>         End If
>     Next c
> End Sub
>
>
> ====
> But I think you may have a bug in your first sub, too.
>
> These lines:
>
> Set RowRange = Worksheets("Employees").Range("D3:D22")
> For r = 3 To 22
>    If RowRange.Cells(r, 1).Value = "X" Then
>
> May not be doing what you want.
>
> Put a msgbox line in it like:
>
> Sub test()
>
> Dim RowRange As Range
> Dim r As Long
> Set RowRange = Worksheets("Employees").Range("D3:D22")
> For r = 3 To 22
>    MsgBox RowRange.Cells(r, 1).Address
> Next r
> End Sub
>
> and you'll see you're looking at D5 when r = 1.  Is that what you meant?
>
> That .cells(r,1) takes its position relative to the top left cell of
RowRange.
>
> If you wanted to loop through the rows in the range:
>
> for r = 1 to rowrange.rows.count
>   msgbox rowrange.cells(r,1).address
> next r
>
> Will loop through the first row of the range to the last row in that
range.



0
12/23/2003 2:02:54 PM
Reply:

Similar Artilces:

convert row to column
I have information in a row on a spreadsheet. Is there a way to copy/cut and paste that information into a column instead? TIA Ivor Have you tried the Transpose yet? You copy the row, select the column you want to put the data in, paste special and check the transpose box Highlight and Copy the Row; then go to the column cell you wish the data to goto and click/select it, then do an Edit Paste-Special (check) Transpose<<Consider if you want to further paste the de-formularized Value or not, also before ending>> HTH "Ivor Williams" <ivor@just-a-second.com> w...

how to revert columns to rows
example: i need to revert table A 1 2 3 4 5 6 B C to this format A B C 1 2 3 4 ?????? Thank you. Copy the cells. Go menu edit > paste special and choose "transpose". HTH. Best wishes Harald "agenda9533" <agenda9533@discussions.microsoft.com> skrev i melding news:E1CFDF77-7ABF-4734-9329-DB5F9D95579C@microsoft.com... > example: i need to revert table > A 1 2 3 4 5 6 > B > C > to this format > > A B C > 1 > 2 > 3 > 4 > ?????? > Thank you. > > > > see topic below "revert table" "agend...

Changed Titled bar and now sort doesn't default to "Header Row"
I feel like an idiot. I deleted the first row in a data field that I used to be able to sort by. After inserting a new row and re-titling each column, when I go to sort now the default comes up "No Header Row" and I have to switch it back to "Header Row". Is there something I can do to get it to default back to "Header Row" like it was before I messed it up? Excel likes to see something different that distinguishes the header row. One easy fix is to bold that header row. Steven wrote: > > I feel like an idiot. I deleted the first row in a data f...

PivotTable Column Field Values
Hi there The column field list of my PivotTable (when one clicks on the drop-down arrow) is populated with a list of items which no longer exist in the data table from the data is drawn. Is it possible to reset these values so that only valid data can be selected. Please let me know if I have not explained the problem adequqtely. Regards Michael There are instructions here for clearing old items from a PivotTable dropdown list: http://www.contextures.com/xlPivot04.html Michael Rekas wrote: > The column field list of my PivotTable (when one clicks on the > drop-down arrow) ...

test for events as they occur on a different form
Is there any way to have VBA code in one form test for events that are occurring on another? Something like: public otheractiveform.form_keyup() do stuff end sub The idea is to have a form that's always open run a test generically on whatever other form happens to have the focus at that time, and to do it without having to write any supporting code on the other form. Here's the quick rundown on why I ask: I already use a hidden form that tracks how long it's been since the focus has been changed to a new active control and closes the db if it's been idle f...

Column Info
Hi - Please forgive my ignorance but I just don't know anything about Excel. I have an expense report that I have to file monthly that has to show daily mileage for both company and personal mileage. I want to automate the functions as much as I can as an aid to ensuring accuracy. Each day has its own line and data with totals for the data at the bottom. Here are my questions: 1) How do I get the current date to automatically load into the appropriate cell? In other words, when I open this file daily how do I get the current date to appear in the cell but still retain the option o...

Compare two columns with different codes
Dear all I have two columns with codes. The first column include the main coding (ie: ab1, ab101, ab10101,...) The second column include the same codes, but the first leter is always C. So the coding is (ie: cab1, cab101, cab10101,...) I have notice that some of the codes in the second column are missing. There is any way that i can make a check regarding the second column based the first one. I tried to use the vlookup but the problem is that the first letter is c and basically the two codes looks different. Can anyone direct me of how i can make the comparison between the two col...

How to freeze NOW()
Hi all, I am using the NOW() to avoid typing in the date/time of some data i am collecting. the problem is that all the cells with now get recalculated every time i enter new data in the spreadsheet. How do I "freeze" the older data so that NOW() does not get recalculated in those cells or is there a way to transform the output of now into some text so that it does not get recalculated?. thanks Rather than =NOW(), type CTRL+; (semi-colon) followed by space and then CTRL+: (colon) to get date and time as if you had typed them best wishes -- Bernard Liengme MVP Excel htt...

Extracting data from two columns
I have data in B1-B1000+ and C1-C1000+ that I need in column A. The problem is that column B & C are filled with data. Column A has specific cells that the data needs to go into. In the example below B1 and C1 represent the data from columns B & C that need to be in A2 and A3. And b2 and c2 need to be in A7 and A8. This pattern continues for 1000+ rows. Thank you in advance! A B C 1 $$un $$tdp $$dac 2 B1 $$tdp $$dac 3 C1 $$tdp $$dac 4 pause $$tdp $$dac 5 $$tdp $$dac 6 $$un $$tdp $$dac...

right justify number column within listbox
Hello everybody, Is there a way to right justify a column within a listbox containing number type data? thanks in advance, George. "George" <George@discussions.microsoft.com> wrote in message news:F0ADD978-D72C-4AF6-A8C0-5D228FE21214@microsoft.com... > Hello everybody, > > Is there a way to right justify a column within a listbox containing > number > type data? > > thanks in advance, George. There's no built-in way, but I have a (rather kludgey) method which calculates the number of "left padding" spaces to insert. Let me know if you...

Last Day of a Week based on NOW()
Good evening all, Just a quick question. How would I work out what all the days in a wee are, based upon the current date. So, have 7 cells, which would work out what their date is, based o today. Example. Today is Thursday, 24th June. Monday- 21/06/2004 Tuesday - 22/06/2004 Wednesday - 23/06/2004 Thursday - 24/06/2004 Friday - 25/06/2004 Saturday - 26/06/2004 Sunday - 27/06/2004 Is this actually possible? With vague thinking, I beleive it might be but I'm not exactly sure how I'd go about working it into a formula Maybe a helper column that would place the the current date into th...

Now
Hi I'm using excel and it is asking me to use NOW formula Where? When? -- HTH Bob Phillips "Jill graeve" <jillgraeve@training.net.nz> wrote in message news:uoGNMj0WFHA.1148@tk2msftngp13.phx.gbl... > Hi > I'm using excel and it is asking me to use NOW formula > > > "Jill graeve" wrote > > I'm using excel and it is asking me to use NOW formula "Bob Phillips" wrote > Where? When? I'd guess... in school? Jordon "Bob Phillips" <phillips@tiscali.co.uk> skrev i melding news:e5kMjF4WFHA.2796@T...

now function
I got a cell A1 with a now function =now() which gives the current time. Cell A2 has a time that is entered, and cell A3 has an if function that compared if(A1>A2,"","Do Something"). However, the if statement is true all the time even if the time is cell A2 is a later time than A1. I changed the format in cell A1 from a time format to a general and it turn out to be a big number. Otherwise, I changed the time format in cell A2 into general and it is a very small decimal number. How do I get around this, may with a mod function or something? NOW gives both ...

can't send and receive, test message settings reports no errors
New vista laptop, installed our volume licence Office 2003, copied mail settings from older XP machine, test mail settings reports no errors, test messages go in and out with no problem, but send and receive within the program gives error - !Task 'pop.googlemail.com - Sending' reported error (0x8004010F): 'The operation failed. An object could not be found.' !Task 'pop.googlemail.com - Receiving' reported error (0x8004010F): 'The operation failed. An object could not be found.' Possible cause - when I booted the laptop last week it had a trial for Office 2007 ...

How to copy the url for a list of cell with hyperlinks in another column?
How to copy the url for a list of cell with hyperlinks in another column? With the hyperlinks in Col A run this to copy the address in Col B Sub Test() Dim hlnk As Hyperlink For Each hlnk In Columns("A").Hyperlinks hlnk.Parent.Offset(0, 1).Value = hlnk.Address Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "jaya" <jayap@wanadoo.fr> wrote in message news:4347b4a1$0$27414$8fcfb975@news.wanadoo.fr... > How to copy the url for a list of cell with hyperlinks in another column? > ...

Now what?
Hi I have installed the CRM server 1.2 on a win2k server. When I try to install the CRM for outlook app, it says it is already installed. How do I now run the CRM app? Thanks Regards Sales for Outlook can't be installed on the same machine of CRM. To use CRM you need to connect to the address http://<servername>. "John" <john@nospam.infovis.co.uk> wrote in message news:ONP4giRcEHA.3016@tk2msftngp13.phx.gbl... > Hi > > I have installed the CRM server 1.2 on a win2k server. When I try to install > the CRM for outlook app, it says it is already instal...

Is there a way I can enter a letter to proceed text a column of ce
I need to enter a source code (letter W) before all the text that I have listed in a column. Example: All of column C has text... MPV001, MPV002, MPV003, ETC. I need to put a "W" in front of all the text. i.e. WMPV001, WMPV002, WMPV003, ETC. Is there a way to do this without manually typing W in all the cells? The spreadsheet has 1500 rows... MelanieWW wrote: > I need to enter a source code (letter W) before all the text that I > have listed in a column. Example: All of column C has text... > MPV001, MPV002, MPV003, ETC. I need to put a "W" in front of...

Microsoft... come on now...
Ok, so many of your mac support pages lead to "page not founds". Great, thanks. On your mactopia support page: http://www.microsoft.com/mac/support.aspx click on "Office 2004 for Mac". Nice huh? Now, open say, Excel. Go to "Excel" in the top menu bar, click "About Excel". Now, in the resulting window click on "Support". Now, click on the first link, the one that says, "http://support.microsoft.com", which by the way isn't even the mac support page for this product, but no matter, you get a "page not found" anyway. It&...

add alpha letter to column of numbers
I have a column of part numbers that I want to re-identify with a "W" in front of the number. There has to be a quick way of doing this but everything I have tried fails. Any advice?? One way is to CONCATENATE.........using a helper column, say column B, put this in B1 and copy down ="W"&A1 Then do copy > PasteSpecial > Values on Column B to remove the formulas........then delete column A if you wish...... It can also be done with VBA code, without using a helper column, if that method is of interest....... Vaya con Dios, Chuck, CABGx3 "-John W.&...

how to sort rows with only two columns?
Using excel I have set up a file that has all my dvd's. This file has four columns: genre/title/year/minutes. I have no problem adding newly purchased dvd's to the list and sorting by title. I always add the new ones to the end of the list. The problem I have is with my book collection which only has two columns: genre and title. For some reason, it will not sort by title. The name of this file is MY BOOK COLLECTION, which is row 1. It is merged and centered with columns 1 & 2. After I click on SORT the window has MY BOOK COLLECTION in it and I cannot get it to sort by TITLE...

Search a column
Hi there. I would like to search down a column for a result in another, however cant work out how to do it. It isnt as simple as matching up A1 with B1 all the way down, there ma be something the same as A1 anywhere from B1 to B3000! Any ideas? I would greatly appreciate some help! Thanks -- Message posted from http://www.ExcelForum.com Hi try =MATCH(A1,B1:B3000,0) to return the row number -- Regards Frank Kabel Frankfurt, Germany > Hi there. > > I would like to search down a column for a result in another, however > i cant work out how to do it. > > It isnt as simp...

Problem in Dynamic Column Sorting
Hi everyone, I changed one of my CRM reports as far as article in "http://msdn2.microsoft.com/en-us/library/Aa681483.aspx" address, after deploying this report, clicking to column header caused the report Post-Back but there isn't any effect of Sorting action in report. Would you please help me about this problem? ------------- Mohsen Ahmadi, msnahm@hotmail.com IR-0912 *** **** Another questions about this problem: - I think the Value of CRM_SortField parameter can not be any Alias field as the folowing: DATEDIFF(minute, CRMAF_Incident.createdonutc, CRMAF_Incident.modif...

Free Jewel Box Liner Template in Many Formats - Disc Case Cover
Hello, At the following address you can find a free template for making paper jewel box inserts (liner notes for CD cases): http://noshadow.cnc.net/cd_cover/ David searchsubmit wrote ... > At the following address you can find a free template for making > paper jewel box inserts (liner notes for CD cases): > > http://noshadow.cnc.net/cd_cover/ And that template and several others in PowerPoint (PPT) format here: http://www.rcrowley.com/templates.htm I do all these things in PPT now. Quick, flexible, easy. If you already use MS Office, no new application to learn. ...

Everything works now....
I'm scared to breathe! All my bank, brokerage, and credit accounts sync and are updating with the right balances (even that pesky Ameritrade one). No duplicate accounts....no freaky download issues. I'm sure next time I log on, Money will have turned my personal finances into a jigsaw puzzle. When you were having problems updating, what did you do to fix it? >-----Original Message----- >I'm scared to breathe! All my bank, brokerage, and >credit accounts sync and are updating with the right >balances (even that pesky Ameritrade one). No duplicate >ac...

Values in multiple columns into one column
Hi everyone, I have a spreadsheet with values in multiple columns where for instance col A is blank colb or c or d won't be...there will be something in one where the others are empty. I need to create a column with all the values together. Tried using the following formula but it doesn't seem to work... the values are all dates.. =IF(O2=" ",N2,IF(N2=" ",K2,IF(K2=" ",J2,O2))) Can anybody help!! Cheers Dan Hi try =IF(O2="",N2,IF(N2="",K2,IF(K2="",J2,O2))) >-----Original Message----- >Hi everyone, > >I hav...