Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 14th 05, 12:26 PM
Michael
 
Posts: n/a
Default 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(xlCellTypeConsta nts, 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

  #2   Report Post  
Old June 14th 05, 09:33 PM
Dave Peterson
 
Posts: n/a
Default

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(xlCellTypeConsta nts, 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
  #3   Report Post  
Old June 15th 05, 08:10 AM
Michael
 
Posts: n/a
Default

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(xlCellTypeConsta nts, 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

  #4   Report Post  
Old June 15th 05, 02:18 PM
Dave Peterson
 
Posts: n/a
Default

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(xlCellTypeConsta nts, 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I find the cell address of the 2nd largest of a set? Mr. Snrub Excel Discussion (Misc queries) 4 May 30th 05 12:53 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
Finding last cell with data in a column Nigel Bennett Setting up and Configuration of Excel 2 April 29th 05 08:03 PM
How do I reference every "n" cell in a column in Excel? Alma Excel Worksheet Functions 2 March 22nd 05 06:19 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM


All times are GMT +1. The time now is 09:05 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017