looping through columns and rows

Hello,

Can someone show me the code which would allow me to search through a
number
of rows and columns, to identify a criteria.  Each time the criteria
is
found, 1 is added to a variable.  When the variable reaches a set
number, the
search moves to a different column.

This would allow me to allocate people to jobs according to their
first,
second, third choice etc, without resourcing too many people to a job.

can you help?  I have very little experience, but am usually able to
see
what is happening in the code and expend on it to take care of more
elements.

kind regards,

Matt

0
3/20/2007 4:37:26 PM
excel 39879 articles. 2 followers. Follow

1 Replies
814 Views

Similar Articles

[PageSpeed] 49

Check one of your other threads.

Guerilla wrote:
> 
> Hello,
> 
> Can someone show me the code which would allow me to search through a
> number
> of rows and columns, to identify a criteria.  Each time the criteria
> is
> found, 1 is added to a variable.  When the variable reaches a set
> number, the
> search moves to a different column.
> 
> This would allow me to allocate people to jobs according to their
> first,
> second, third choice etc, without resourcing too many people to a job.
> 
> can you help?  I have very little experience, but am usually able to
> see
> what is happening in the code and expend on it to take care of more
> elements.
> 
> kind regards,
> 
> Matt

-- 

Dave Peterson
0
petersod (12004)
3/20/2007 5:14:18 PM
Reply:

Similar Artilces:

if cell = 0 then hide the row?
My spreadsheet is a list of names and hours and charges, but if name X, say D14, has zero hours, say F14 = 0, then I do not need the row - otherwise my spreadsheet will have so many unused rows - and so want to hide it. To complicate matters, my spreadsheet has titles, etc, and clearly such a row has no value for hours in any cell, and these rows are not to be hidden. I see something that only conisders the "data rows" and hides them if the hours cell, F14, is zero. Allan Here is one way which assumes headings in rows 1 and 2. Just chan ge the F3 to the first real data ro...

Total cells of one column based on the values in another column?
One of the sheets in my ledger workbook is for annual cash receipts. Column D is for the amount (formatted to number-accounting)and Column E is for who it's from (formatted to number-text). I've got my cells elsewhere for month, quarter, & year totals. The problem is I have to manually, with my adding machine, go thru and add up the total from each source. (How much total income for the year is/was from Co.A, Co.B, Co.C, etc..) I've figured out how to use conditional formatting so that if the text in Column E cells is say, Co.A, the text will be red, if Co.B, text will ...

Column Labels on Views
Hi, does anybody know if I can change a the Display Name of an existing column field? I checked Deployment manager and noticed the Display Name is prompted during creation of the field, is there a way to change the label? we reused a field for the accounts table and would like to change the display name on the List View.. Thanks =) Simple log in the CRM web page with administrator level and go to Start/Configuration/Custom System, choose the entity who you want custom and click in custom form. In this page you can choose the displayed fields and custom them. After custom the disired fie...

Excel Rows and Columns Issue
I am an excel 2002 user, and I am having problem putting in th formulas. The formulas are simple, average, add, subtract, an multiplying cells, but the real problem is in "viewing" the rows an columns. Normally, in excel, rows are numbered (1,2,3,4...) and columns ar alphabatized (A,B,C,D...) In my case, both columns and rows ar "numbered" , so putting in a formula is a pain in my neck. How d resolve or change the view of the excel sheet so it displays th "Letters" on the top columns part of the sheet going from left t right, and the "numbers" rows (wh...

Column Headers/headings
Hi, Is there any way that you can get the top row to stay visible no matter where you are on the spread sheet - ie: to be able to see what columns you are typing in (eg cost) when further down the spread sheet. Thanks for any help, Matt. Click on A2 Click Window > Freeze Panes -- Rgds Max xl 97 ---------------------------------------------------- Use xdemechanik <at>yahoo<dot>com for email ----------------------------------------------------- "The Stoat" <thefantstoat@lycos.co.uk> wrote in message news:5iSNb.3831$pD3.3270@news-binary.blueyonder.co.uk... &...

My "Do While...Loop" wont loop and delete the spaces between my digits.
I am trying to eliminate the spaces between digits in Australian Business Numbers which have the format "NN NNN NNN NNN" to produce an eleven digit number with no spaces. I have written a function to do this that appears to work. I now want to call this function from a subroutine to change about 3000 numbers. The subroutine is causing me headaches. Here is my space-eliminating function: Function RemoveSpaces(CellContents As String) As String Do While InStr(1, CellContents, " ") > 0 CellContents = Replace(CellContents, " ", "") Loop Re...

Holding the view of column one
Good morning, I want to hold the view of column one while working on other columns. What is the best approach to my need? Thank you, Mark Windows>Freeze panes -- Kind Regards, Niek Otten Microsoft MVP - Excel "Mark S." <Mark S.@discussions.microsoft.com> wrote in message news:0DDD2DBD-1BF5-4D11-AA96-86368E7BA8E6@microsoft.com... > Good morning, > > I want to hold the view of column one while working on other columns. What > is the best approach to my need? > > Thank you, Mark ...

auto comlete a row of data
in exel say for instance g1 is a lisence plate number (variable) how can i get A1-f1 to be filled with spesific data dedicated to each licence number You could use a lookup table and VLookup formulas or a macro. More info OR If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Soft...

Repost: Loop through files
I posted this earlier, but it never made it to the screen so I'm reposting - hope it doesn't duplicate the earlier one. I need to loop through all currently open workbooks and display a message box that tells me the file name and whether or not the workbook is visible. Your example code is what I need. Thanks in advance for your assistance. Hi, Sub ShowThem() Dim oBook As Workbook For Each oBook In Workbooks MsgBox "Book " & vbNewLine & oBook.FullName _ & vbNewLine & "is " & _ IIf(oBook.Windows(1).Visible...

Ordering Columns in a Cross Tab Query & Dividing a Value
Hello, First question, is there a way to specify what order the columns appear in a cross tab query? Second, is there a way to distribute a value in one column of a cross tab query across new columns? For example, if I have a total value of a contract, but I needed to recognize revenue equally in each of the three months of a quarter, is there a way to distribute that value across columns in a cross tab? Thanks! >> First question, is there a way to specify what order the columns appear in a cross tab query? In the SQL view edit the PIVOT line to something like this (Make sure...

Thick column lines
Hello I want to create thick column lines but don't know how. Thick lines would enable me to separate the data from one column to next more clearly. Please help. Thank you. Regards hash Format / Cells / Borders / Select thick border and click on diagram to tell Excel where to apply it on the cell(s). -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- There's no 'I' in 'Team' ---------...

Loop?
I really am having a bad week... I have a workbook with two sheets; 'main' & 'not complete'. On the main sheet I have 10 columns of data, row 2 to 20 (More rows can be added so may be more). What I need to do is: for each row, if column I = 1 and J = "" then I need to copy some of the data from that row (column A,B,F & G) to the sheet called 'not complete'. the cell in column J will then be changed to 1. Repeat for each row making sure the sheet called 'not complete' does not have any blank rows. Hope that makes sense a...

Money 2003
What is the column in the register that has the heading of a red exclamation mark? Some of my entries have an image of an envelope and a question mark in that column. What does this image mean? This is Money 2003 Deluxe running under Windows 2K. Thanks in advance. Mark See http://umpmfaq.info/faqdb.php?q=6 and http://umpmfaq.info/faqdb.php?cat=6. From online help: There are two icons that indicate different conditions for downloaded transactions: When you see ? in your account register, no match has been found for this downloaded transaction. It is marked as a possible new transaction. A ...

How do I set end-of-range to #rows from beginning
Specifically, I am creating a chart that will have a breadth (# data points) based on a variable (value of a cell). Start point is always the same. For instance, a table is created based on a formula. The chart should stop when values in the table reach a certain value, but this could be 20 or 200 datapoints (20-200 rows). I know how many datapoints should be included, but not how to terminate the graph based on this number. I would prefer to do this without VBA, as I am not particularly proficient, but will if the only way. Hi, It would be nice to see some sample data, with a...

Unhide a column among columns.
Hi, There are 10 columns which are hidden between two columns(A and X). I want to unhide only 1 or 2 columns without enabling(unhiding) remaing How can i do it. Thanks and regards mowrya.K.B.P. With the help of a Macro. The command look like this: Columns("E:G").EntireColumn.Hidden = False *** Check the VBA Help *** Micky "Mowrya" wrote: > Hi, > There are 10 columns which are hidden between two columns(A and X). I want > to unhide only 1 or 2 columns without enabling(unhiding) remaing How can i do > it. > Thanks and regards >...

Columns and rows are numbered
For some really strange reason lately, when I'm working in Excel both my rows and columns are numbered 1,2,3,4,5....This makes it really difficult to deal with . How do I get excel to stop doing this I want my columns to go back to being lettered A,b,c,d, etc....Anyone else ever have this happen or know how to fix it. It's annoying. Thanks Tools - options - general- R1C1 style- UNTICK. ;) >-----Original Message----- >For some really strange reason lately, when I'm working in Excel both my rows >and columns are numbered 1,2,3,4,5....This makes it really difficu...

Totals column thinks it's 24 hr clock & shows 01h31m instead of 25h31m. Fix?
A spreadsheeI that allows me to enter hours is not adding up correctly in the totals column. It adds up correct up to but not including 24 hours but after that the total deletes that 24 hoursl It seems Excel is mistaking this for the 24 hours clock system and doesn't realize that it must show the total. i.e., after deleting 3 rows above the totals column I get a total of 23h16m but then after the next row, it shows less than 1 hour with the final total of all the rows showing 01h31m. That should actually show 25h31m. Is there a way to get Excel to actually add up the hours instead of ...

flip columns to rows
Is there any way to 'flip' a worksheet with one command, so that columns become rows (or rows become columns)? Have you tried the Transpose function? "captain fantastic" wrote: > Is there any way to 'flip' a worksheet with one command, so that columns become > rows (or rows become columns)? > captain fantastic wrote: >Is there any way to 'flip' a worksheet with one command, so that columns become >rows (or rows become columns)? Not one command, but you could write a macro. Select all Copy Paste Special tick "Transpose" Chris ...

Loop/Repeat Code
A2007 I need to run code while the record is displayed on a form, and must run this for all records. I have on the Click event of a button on the form: DoCmd.GoToRecord , "", acFirst Call MyFunction It is MyFunction I need help with: DoCmd 'Run some code and queries' DoCmd.GoToRecord , "", acNext Need help here to repeat/loop until the last record. Thanks in advance. ...

Mail Loops
Is there anyway on exch 5.5 to limit the number of times a mail is resent ie when the user sets up a forwarding rule to a home account and screws up the address a mail loop is formed. I would like to set the number of retries to say 6. Help please tia No. Rules are set to fire when the e-mail is received. Only OOF is set to fire only once per recipient. As far as the rule is concerned, each message is separate and must be forwarded on to the home account. Do yourself a favor and disable forwarding to the internet. Give them another option to get e-mails while they are home. If you need ...

How cancel $ in column?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I mistakenly selected a column and hit the dollar sign ($), changing the numeric codes in the column to dollar and cents values. How can I cancel this? Thanks. If you haven't done anything significant since & haven't closed the file, how about Undo? Otherwise, select the column again then go to Format> Cells to select either General (default formatting for new sheets), Number, or whatever is preferred. -- HTH |:>) Bob Jones [MVP] Office:Mac <William_Richards@officeform...

Inserting row takes 1
I'm working with an Excel 2003 workbook with six worksheets (that I've opened in Excel 2007), one per year. There are only 14 columns with a very simple SUM formula in column 14. This is a spreadsheet that keeps track of auction sales by week with a grand total at the bottom of each yearly worksheet. Pretty simple. Since I've opened this workbook in Excel 2007, it is taking from 1 to 3 minutes to insert a blank row in the middle of other blank rows. This would be instantaneous with the same workbook in Excel 2003. I'm not a programmer, just a user and this is ver...

reference cell value from fixed column with variable row
template wizard wont let me select a form control (combo box) value to associate a field with but provides a reference cell value which gives me the row number in the other workbook of the value. how do i make a valid cell reference from this reference? i.e. col of referenced value is "A", row is variable depending on what value i pick in combo box. how do i make a reference out of "A" plus the value to equal "A5" ? (so that i can associate a field with it using the template wizard) -- bob z ...

Column names??
Hi My Column names are not showing-up as A, B, C, D... They are showing-up as 1, 2, 3, 4, 5... I do not understand why this is happening?? I have tried running: Excel.exe /regserver Any suggestions?? Thanks Clive In xl2003 and below: Tools|Options|General|Uncheck R1C1 reference style Clive_S wrote: > > Hi > > My Column names are not showing-up as A, B, C, D... > They are showing-up as 1, 2, 3, 4, 5... > > I do not understand why this is happening?? > > I have tried running: Excel.exe /regserver > > ...

Loop a
Guys I am programming up a macro in Excel, I was just wondering how to loop the range function. Whereby I would like to somehow put a variable in the function Range, for a set column, eg. dim i as integer dim c(0) as string i=0 for i =117 to 200 c(0) = Range("C" & i) ' This is the line that doesn't work msgbox c(0) 'for simplicity next i Thanks No need for a loop Dim c c = Application.Transpose(Range("C117:C200")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ben" <duivesteyn@...