Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find the cell address of the 2nd largest of a set? | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Finding last cell with data in a column | Setting up and Configuration of Excel | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |