Find a empty cell in next column

hi.
Dave helped me with this before, now i need more help.

I use this code to find a column called PoF and the numbers written in the 
cells below it. This code work in every row/column (i dont need to name it).

PoFRow = 0 
PoFCol = 0             
On Error Resume Next 
PoFRow = Worksheets(2).Range("PoF").Row             
PoFCol = Worksheets(2).Range("PoF").Column            

On Error GoTo 0 
 
If PoFRow = 0 Then 
MsgBox "No range named PoF on worksheet:" ' & .Name '            
Else 
Set numberPoF = Nothing 
On Error Resume Next             
Set numberPoF = Worksheets(2).Range(Worksheets(2).Cells(PoFRow + 1, PoFCol), 
Worksheets(2).Cells(Worksheets(2).Rows.Count, 
PoFCol)).Cells.Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Cells(1)
                        
On Error GoTo 0 'Ved error går den til 0
yPoF = numberPoF.Address(Row) 
End If 

Lets say that the code will find the name PoF in Column X, Row 4 and the 
first number below it in column X, Row 7.

If PoF is in Column X, Column Y has the name Risk (it is always the next 
column).

Column y (Risk) is always empty before i have tested column x,a,b etc etc in 
the same worksheet.  When the code has tested everything i want the code to 
return the value L, HM, M, N etc into column Risk, starting in the same cell 
that the number was found in PoF. Like this


Row/Column        X         Y          Z
3
4                       PoF       Risk
5
6
7                         5           N
8
9


 
Nil Satis Nisi Optimum
0
Michael5492 (655)
6/14/2005 11:26:03 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
624 Views

Similar Articles

[PageSpeed] 30

But you didn't share how to determine what to put in that adjacent column.

What makes you use:  L, HM, M, N etc?

If you just want to plop something into that cell just to the right of the
numberPoF:

numberPof.offset(0,1).value = "whatever you want here"



Michael wrote:
> 
> hi.
> Dave helped me with this before, now i need more help.
> 
> I use this code to find a column called PoF and the numbers written in the
> cells below it. This code work in every row/column (i dont need to name it).
> 
> PoFRow = 0
> PoFCol = 0
> On Error Resume Next
> PoFRow = Worksheets(2).Range("PoF").Row
> PoFCol = Worksheets(2).Range("PoF").Column
> 
> On Error GoTo 0
> 
> If PoFRow = 0 Then
> MsgBox "No range named PoF on worksheet:" ' & .Name '
> Else
> Set numberPoF = Nothing
> On Error Resume Next
> Set numberPoF = Worksheets(2).Range(Worksheets(2).Cells(PoFRow + 1, PoFCol),
> Worksheets(2).Cells(Worksheets(2).Rows.Count,
> PoFCol)).Cells.Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Cells(1)
> 
> On Error GoTo 0 'Ved error går den til 0
> yPoF = numberPoF.Address(Row)
> End If
> 
> Lets say that the code will find the name PoF in Column X, Row 4 and the
> first number below it in column X, Row 7.
> 
> If PoF is in Column X, Column Y has the name Risk (it is always the next
> column).
> 
> Column y (Risk) is always empty before i have tested column x,a,b etc etc in
> the same worksheet.  When the code has tested everything i want the code to
> return the value L, HM, M, N etc into column Risk, starting in the same cell
> that the number was found in PoF. Like this
> 
> Row/Column        X         Y          Z
> 3
> 4                       PoF       Risk
> 5
> 6
> 7                         5           N
> 8
> 9
> 
> 
> Nil Satis Nisi Optimum

-- 

Dave Peterson
0
ec357201 (5290)
6/14/2005 8:33:48 PM
Thanks again Dave.

I will try this.

I just want to put L, HM, N etc in the next column but in the same row 
number. It is a loop like this

If PoF = 5 and 2<= CoF=> 4 then

(and CoF is column x and the row is 10, the code should put L, N etc into 
column y row 10)

Else if
PoF = 4 and CoF=4 then

''''numberPof.offset(0,1).value = "whatever you want here" (your formula)

end if 
-- 
Nil Satis Nisi Optimum

I think this will work. Thanks again.

As you found out "Nothing but the best is good enough" :-)


"Dave Peterson" wrote:

> But you didn't share how to determine what to put in that adjacent column.
> 
> What makes you use:  L, HM, M, N etc?
> 
> If you just want to plop something into that cell just to the right of the
> numberPoF:
> 
> numberPof.offset(0,1).value = "whatever you want here"
> 
> 
> 
> Michael wrote:
> > 
> > hi.
> > Dave helped me with this before, now i need more help.
> > 
> > I use this code to find a column called PoF and the numbers written in the
> > cells below it. This code work in every row/column (i dont need to name it).
> > 
> > PoFRow = 0
> > PoFCol = 0
> > On Error Resume Next
> > PoFRow = Worksheets(2).Range("PoF").Row
> > PoFCol = Worksheets(2).Range("PoF").Column
> > 
> > On Error GoTo 0
> > 
> > If PoFRow = 0 Then
> > MsgBox "No range named PoF on worksheet:" ' & .Name '
> > Else
> > Set numberPoF = Nothing
> > On Error Resume Next
> > Set numberPoF = Worksheets(2).Range(Worksheets(2).Cells(PoFRow + 1, PoFCol),
> > Worksheets(2).Cells(Worksheets(2).Rows.Count,
> > PoFCol)).Cells.Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Cells(1)
> > 
> > On Error GoTo 0 'Ved error går den til 0
> > yPoF = numberPoF.Address(Row)
> > End If
> > 
> > Lets say that the code will find the name PoF in Column X, Row 4 and the
> > first number below it in column X, Row 7.
> > 
> > If PoF is in Column X, Column Y has the name Risk (it is always the next
> > column).
> > 
> > Column y (Risk) is always empty before i have tested column x,a,b etc etc in
> > the same worksheet.  When the code has tested everything i want the code to
> > return the value L, HM, M, N etc into column Risk, starting in the same cell
> > that the number was found in PoF. Like this
> > 
> > Row/Column        X         Y          Z
> > 3
> > 4                       PoF       Risk
> > 5
> > 6
> > 7                         5           N
> > 8
> > 9
> > 
> > 
> > Nil Satis Nisi Optimum
> 
> -- 
> 
> Dave Peterson
> 
0
Michael5492 (655)
6/15/2005 7:10:34 AM
If PoF = 5 and 2<= CoF=> 4 then

Did you really mean Cof >=2 and CoF >= 4?  (It looks like a typo and you want it
trapped between 2 and 4.

If yes, then maybe:

if pof = 5 and cof >=2 and cof <= 4 then

Michael wrote:
> 
> Thanks again Dave.
> 
> I will try this.
> 
> I just want to put L, HM, N etc in the next column but in the same row
> number. It is a loop like this
> 
> If PoF = 5 and 2<= CoF=> 4 then
> 
> (and CoF is column x and the row is 10, the code should put L, N etc into
> column y row 10)
> 
> Else if
> PoF = 4 and CoF=4 then
> 
> ''''numberPof.offset(0,1).value = "whatever you want here" (your formula)
> 
> end if
> --
> Nil Satis Nisi Optimum
> 
> I think this will work. Thanks again.
> 
> As you found out "Nothing but the best is good enough" :-)
> 
> "Dave Peterson" wrote:
> 
> > But you didn't share how to determine what to put in that adjacent column.
> >
> > What makes you use:  L, HM, M, N etc?
> >
> > If you just want to plop something into that cell just to the right of the
> > numberPoF:
> >
> > numberPof.offset(0,1).value = "whatever you want here"
> >
> >
> >
> > Michael wrote:
> > >
> > > hi.
> > > Dave helped me with this before, now i need more help.
> > >
> > > I use this code to find a column called PoF and the numbers written in the
> > > cells below it. This code work in every row/column (i dont need to name it).
> > >
> > > PoFRow = 0
> > > PoFCol = 0
> > > On Error Resume Next
> > > PoFRow = Worksheets(2).Range("PoF").Row
> > > PoFCol = Worksheets(2).Range("PoF").Column
> > >
> > > On Error GoTo 0
> > >
> > > If PoFRow = 0 Then
> > > MsgBox "No range named PoF on worksheet:" ' & .Name '
> > > Else
> > > Set numberPoF = Nothing
> > > On Error Resume Next
> > > Set numberPoF = Worksheets(2).Range(Worksheets(2).Cells(PoFRow + 1, PoFCol),
> > > Worksheets(2).Cells(Worksheets(2).Rows.Count,
> > > PoFCol)).Cells.Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Cells(1)
> > >
> > > On Error GoTo 0 'Ved error går den til 0
> > > yPoF = numberPoF.Address(Row)
> > > End If
> > >
> > > Lets say that the code will find the name PoF in Column X, Row 4 and the
> > > first number below it in column X, Row 7.
> > >
> > > If PoF is in Column X, Column Y has the name Risk (it is always the next
> > > column).
> > >
> > > Column y (Risk) is always empty before i have tested column x,a,b etc etc in
> > > the same worksheet.  When the code has tested everything i want the code to
> > > return the value L, HM, M, N etc into column Risk, starting in the same cell
> > > that the number was found in PoF. Like this
> > >
> > > Row/Column        X         Y          Z
> > > 3
> > > 4                       PoF       Risk
> > > 5
> > > 6
> > > 7                         5           N
> > > 8
> > > 9
> > >
> > >
> > > Nil Satis Nisi Optimum
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
ec357201 (5290)
6/15/2005 1:18:26 PM
Reply:

Similar Artilces:

Finding an asterisk
How can I search/find an asterisk in a cell? I've tried and the find dialog box locates cells with any text. I'm trying to find a cell that contains three asteriskx. Hi Gary, Try ~* HTH Regards, Howard "Gary Callahan" <garycallahan@bmwconcord.com> wrote in message news:049201c3928f$379f9610$a301280a@phx.gbl... > How can I search/find an asterisk in a cell? I've tried > and the find dialog box locates cells with any text. I'm > trying to find a cell that contains three asteriskx. Precede the * in the Find box with a tilde, eg ~* -- Regards...

autofit: cell height expands with text entered?
For a form: can a user enter mass quantities of text in a cell and have the cell depth expand so it fits? does Merging Cells limit this ability? I made a giant cell to handle the text the user might enter. I can't figure out where to set this... I have copied & pasted formatting from one worksheet to another without luck. Thanks. Sandy Merged cells don't adjust rowheight for wrapped text (like non-merged cells do). Jim Rech wrote a macro called AutoFitMergedCellRowHeight that you may like: http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05 Sandy wrote:...

Go To the next empty cell in Column A
Using Vista and Excel 2007, I will be constructing a mailing list with 10 columns. In the first empty row of column A will be added a new name for the list. With 10 columns it is not possible to view Column A from Column L on screen. With hundreds of names to add to the list, I need a fast way to go to the next empty cell in column A to add the next name.. I am familiar with tables in Access where there is an icon that will take me to the next empty cell in column A. Is there a similar one stroke command to take me to the next empty cell in column A from anywhere in an Excel ...

User defined functions aware of what cell they are placed in?
Hi, I would like to make a user defined function which needs to know in what cell and what worksheet it is placed in. I will be using this UDF in multiple cells on multiple worksheets. I originally just passed the cell row and column as parameters to the UDF however this ended up updating all worksheets and not just the one the UDF was on. Is there any way to do this? Option Explicit function myfunct(something as somethingelse) as something msgbox application.caller.address & vblf _ & application.caller.parent.name & vblf _ & application.caller.pare...

copy date in a cell if within a date range
Column M is a listing of percentages Column A is various dates, anywhere from Jan 1, 1998 to the present. I need to copy the contents of let's say M3 into cell T3 is the date in cell A3 is any date in the year 2010. If the date is in another year, leave cell T3 blank Thanks "carrerapaolo" wrote: > Column M is a listing of percentages > > Column A is various dates, anywhere from Jan 1, 1998 to the present. > > I need to copy the contents of let's say M3 into cell T3 is the date in cell > A3 is any date in the year 2010. If the ...

Excel-Multiple Cells Being Hi-lited
Sometimes when I'm setting up a worksheet and I left-click in a cell, multiple cells in the same column are hi-lited. After it happens the first time, it continues as I move through the worksheet, reducing my ability to get work done considerably. After some trial and error, it seems to occur when I've been adding and/or deleting columns and/or rows, after a header has been installed. I can move throughout the worksheet using the arrow keys, but it is a time consuming and cumbersome technique. I think the version I'm using is Office Professional 2007 (file extensi...

How can I line up 2 columns with similar information?
I have 2 columns with names in each and I'd like to line them up next to each other. I think we'll need more information, Kathy. I don't understand what you're trying to do. Can you give exactly what's up? ************ Anne Troy www.OfficeArticles.com "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:A5C0BC96-7E27-4C55-89A3-03513FC0D409@microsoft.com... >I have 2 columns with names in each and I'd like to line them up next to >each > other. Column A has employee number, Column B has the same employee numbers but has addi...

Go To an address specfied in a cell
Hello Folks, Does anyone know how I can move the cursor to a cell, the address of which is specified in another cell? Here is the scenario. I enter a list of hours worked in a specfic week on a data entry sheet. I hit a button and the values are copied to a data summary sheet, the position depends on the Week No., the first cell is specfied as the address "Data!29" for Week 5. I reckon I can handle a recorded macro to copy and paste the data but how do I locate the correct start cell? I have tried copying and pasting into the GoTo box but that doesn't work. Data!J29 Wee...

How do I make a particular column required in Excel? #2
Our NPO is creating a reimbursement chart in Excel. We're trying to make it so that if employees do not fill in one particular column entitled "Projects," they will get an error or will be unable to have a final sum filled in. Is there any way I can make this particular column required so that if it is not filled in, the chart will not complete properly? Thank you in advance for any advice or help--I'm a bit of a novice to this! ...

Help!! Way to find users sending email to large amount of receipients.
Could some one tell me if there is a way in Exchange 2003 to find out what users are sending email to a large amount of recipients? I'm having some serious performance issues and I'm sure it's some one sending email to a large amount of recipients. Thanks, Will ...

Help with displaying the contents of the last populate cell.
I have numerous sheets within a book where all cells in column C in all sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you reference both columns P and R hold a monetary value and are formatted as Currency. Is there a way that cell D1 can automatically be populated with the contents of the last cell in column C that has a value in it. E.G. Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200. Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250. Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900. Any h...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Excel 97 Worksheet Protection and cell colour
Hi there, One of our users has setup a worksheet will a small range of cells that are locked (they have formulas in them), he then protects the sheet. He then wants to change the colour of some of the other cells, these cells are not locked, but he cannot change the colour of the cells. Is there an obvious solution? Cheers, Andy Hi AFAIK you can't do this in Excel 97 without first removing the protection -- Regards Frank Kabel Frankfurt, Germany andy wrote: > Hi there, > > One of our users has setup a worksheet will a small range of cells > that are locked (they h...

How do I create a running subtraction column?
In other words, how do I create a column which subtracts from the preceeding cell? Hi you may provide some more details what you're trying to do exactly. Maybe post some example rows -- Regards Frank Kabel Frankfurt, Germany aschneider wrote: > In other words, how do I create a column which subtracts from the > preceeding cell? If you want the balance in column B, and you are making entries in column A which are to be subtracted from the balance, put the starting balance in B1. In B2 put the formula =B1-A2 On Mon, 4 Oct 2004 08:35:08 -0700, "aschneider" <aschne...

sp_columns does not return NVARCHAR data type columns
Hi, our SQL Server Version is 2000 SP4. For several days the SP "sp_columns" does not return NVARCHAR data type columns any more. I reviewd the SP "sp_columns" and it seems, that the used SP "spt_datatype_info" does not return that type any more. A second production server returns that column type. The SP "sp_columns" is used by the ODBC-Driver to determine the table columns. Any suggestions? Thanks a lot, Nils Ok, my fault, it seems that "spt_datatype_info" is a table and there is no NVARCHAR row. After insert...

Publisher calendar, how can I show the previous/next month?
I need to show the past and previous months in blank days at the top of a per month calendar, but can't see how to do that. Thanks. Create a yearly calendar in a new publication. Ungroup, copy the separate months, paste to your main calendar. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jennie" <Jennie@discussions.microsoft.com> wrote in message news:AF4CF698-ABB8-4F01-A667-CC6AA87A9732@microsoft.com... >I need to show the past and previous months in blank days at the top of a per > month calendar, but...

Coloring the Desired cells
Hello, I have a work sheet in which i have to look for word "Test" and color the rows below it. There are different words like "Test 1" "Test 2" and each set needs a different color. Can I get some help with the macro for it? eg: Test1 row 1 row 2 Test 2 row 1 row 2 the number of rows in each group is not constanr. Thank you, Harsh Excel will need to know the logic of the rows and colors to be able to determine how many rows to color. You say the number of rows is not constant, but obviously you know how many rows to color. How do you know t...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

Do a calculation in cells with text data format
I have a few columns of cells having a mixed data format of number and text. Is it possible to convert the first row of numbers in text data format for further calculation? Your guidance to accomplish it is appreciated. Thanks, Ray Example? -- Regards, Peo Sjoblom "Ray" <NoSpam-ZQLi@GMail.com> wrote in message news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl... > I have a few columns of cells having a mixed data format of number and text. > Is it possible to convert the first row of numbers in text data format for > further calculation? Your guidance to accomplis...

Changing of Cell protections after saving Excel File (2002)
This problem occurs when I protect a document using a macro 4.0 function: =PROTECT.DOCUMENT(TRUE,,,TRUE,TRUE). When I use the function within a macro4.0 macro, on an original file, everything works fine. The sheet has unlocked cells, and when the sheet is protected, it allows me to access those cells. But if I save the file, or save.as another name, then the fun begins. The enable selection of the sheet( view codes) has gone from 0-xlNoRestrictions to -4142- xlNoSelection. This locks me out of doing anything in the sheet. When I unprotect and then re-protect the sheet using the T...

how do I change cell references automatically in formulas
In Excel 2000, I have data in 80 rows and 10 columns. Each week I add a new row. I have a separate chart for each column with the data range from the first row to the last.. Each week I have to change the data range to reflect the new last row for each chart. Is there someway I can do this automatically? http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "jnw3" <jnw3@discussions.microsoft.com> wrote in message news:8A3551F9-FBC6-4841-95B4-618AB1893190@micros...

Cell can left indent; anything for right side of cell?
Is there a way to leave more space between the end of a word and the right side of the column? I indented the left side of the column by using the indent option set to "1" in the alignment tab of the formatting box. Besides adding an extra column to the end of the row and making it very small in width and extending the border to that extra "spacer" column, is there a way to make this space? My spreadsheet is done and if I have to do this, I'll have tons of work re-writing code. I was going to use Word for the final presentation of the data, but it didn't work ou...

Defining same name for cells in different sheets
Does anyone know the answer to this one? I want to give the same range name to the same cell reference in a series of worksheets. I find I can do this by pre-defining all my range names on a "master" worksheet and making several copies of the sheet (try it, it works!) But let's say I have done this, and entered all my data on the sheets I have created, and suddenly realize I need another range name. I haven't found any way to define a new range name and apply it to the same range on several sheets. This is not the same as a 3-D reference, which I tried. (3- d referenci...

Alternating cell shading colors for every other merged cell
I am trying to get every other merged row to be a certain cell shading color. There is some discussion in the newsgroups about alternating colors for every row, but in my case I had merged cells and the formulas given didn't turn out right on my tables since they were based on the row numbers. Does anyone know how to have alternating cell colors for merged rows? (the merged rows are random sizes) Thanks. Not an answer to your question I'm afraid, but just for info, most people in here steer clear of merged cells like the plague - they tend to cause far more problems than they ever se...

Finding data in grouped sheets
When using the 'find' option whilst workbook pages are grouped together I am getting data from the last page first, then the last but one etc. Can anyone tell me whether it is possible to change this so that it finds the data in the first possible page, ie it looks at page 3 before page 4 etc? What I am trying to do is set up a spreadsheet which finds the first 'vacancy' in a childrens nursery. As I have set it up at the moment it is finding the last vacancy first! All help greatly received! Thanks in advance. Susie Vaughan This may not quite fit, but you may find it very us...