Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find the cell value in excel by using vb code
Hi.
I'm trying to write a vb code that find a Row and a Column in worksheet2 that i called PoF (headline) It looks like this Row/Column: a b c d e 1 2 PoF 3 4 6 5 6 6 8 The name is PoF because i will use the same code in different sheets and the name PoF in different Columns/Rows The code is a loop. W = Worksheets(2).Range("PoF").Row ' This will give me the Row, but i also need to find the Column. 'Then a i also need the code to find the first cell that includes a number Number = (Worksheets(2).Range("d4").Value) ' this will give me the value 6 in the example 'but i cant put "d4" etc in every code in every sheet, is there a code to fix this? numberRow = Worksheets(2).Range("First number").Row 'The cell that includes the first number?? for i=numberRow (as row number) to the end of row?? (to the last cell number) step 1 If number = 6 then w10="Ok" ElsIf w10="Try again" end if Next mumber Next numberRow Please help me! |
#2
|
|||
|
|||
You have a worksheet level range name PoF on a bunch of worksheets?
And you have values (not formulas) somewhere in that column? And you want to find the first number under that PoF cell? If yes, this may give you a couple of ideas: Option Explicit Sub testme() Dim wks As Worksheet Dim PoFRow As Long Dim PoFCol As Long Dim myNumberRng As Range For Each wks In ActiveWorkbook.Worksheets With wks PoFRow = 0 PoFCol = 0 On Error Resume Next 'just in case... PoFRow = .Range("PoF").Row PoFCol = .Range("PoF").Column On Error GoTo 0 If PoFRow = 0 Then MsgBox "no range named POF on worksheet: " & .Name Else Set myNumberRng = Nothing On Error Resume Next Set myNumberRng = .Range(.Cells(PoFRow + 1, PoFCol), _ .Cells(.Rows.Count, PoFCol)).Cells _ .Cells.SpecialCells(xlCellTypeConstants, _ xlNumbers).Cells(1) On Error GoTo 0 If myNumberRng Is Nothing Then MsgBox "no number constants" Else MsgBox myNumberRng.Address(external:=True) _ & vbLf & myNumberRng.Value End If End If End With Next wks End Sub Michael wrote: Hi. I'm trying to write a vb code that find a Row and a Column in worksheet2 that i called PoF (headline) It looks like this Row/Column: a b c d e 1 2 PoF 3 4 6 5 6 6 8 The name is PoF because i will use the same code in different sheets and the name PoF in different Columns/Rows The code is a loop. W = Worksheets(2).Range("PoF").Row ' This will give me the Row, but i also need to find the Column. 'Then a i also need the code to find the first cell that includes a number Number = (Worksheets(2).Range("d4").Value) ' this will give me the value 6 in the example 'but i cant put "d4" etc in every code in every sheet, is there a code to fix this? numberRow = Worksheets(2).Range("First number").Row 'The cell that includes the first number?? for i=numberRow (as row number) to the end of row?? (to the last cell number) step 1 If number = 6 then w10="Ok" ElsIf w10="Try again" end if Next mumber Next numberRow Please help me! -- Dave Peterson |
#3
|
|||
|
|||
-- Nil Satis Nisi Optimum "Dave Peterson" wrote: You have a worksheet level range name PoF on a bunch of worksheets? And you have values (not formulas) somewhere in that column? And you want to find the first number under that PoF cell? If yes, this may give you a couple of ideas: Option Explicit Sub testme() Dim wks As Worksheet Dim PoFRow As Long Dim PoFCol As Long Dim myNumberRng As Range For Each wks In ActiveWorkbook.Worksheets With wks PoFRow = 0 PoFCol = 0 On Error Resume Next 'just in case... PoFRow = .Range("PoF").Row PoFCol = .Range("PoF").Column On Error GoTo 0 If PoFRow = 0 Then MsgBox "no range named POF on worksheet: " & .Name Else Set myNumberRng = Nothing On Error Resume Next Set myNumberRng = .Range(.Cells(PoFRow + 1, PoFCol), _ .Cells(.Rows.Count, PoFCol)).Cells _ .Cells.SpecialCells(xlCellTypeConstants, _ xlNumbers).Cells(1) On Error GoTo 0 If myNumberRng Is Nothing Then MsgBox "no number constants" Else MsgBox myNumberRng.Address(external:=True) _ & vbLf & myNumberRng.Value End If End If End With Next wks End Sub Dave Peterson Dave. Your answer was perfect! All of it was correct. Thank you for using your time to help me. |
#4
|
|||
|
|||
One translation:
"nothing but the best is good enough." Michael wrote: -- Nil Satis Nisi Optimum |
#5
|
|||
|
|||
-- Nil Satis Nisi Optimum "Dave Peterson" wrote: One translation: "nothing but the best is good enough." Michael wrote: -- Nil Satis Nisi Optimum He he. You are good! Do you also know where i found it? |
#6
|
|||
|
|||
I used google. It knows everything.
It seems to be a common motto for highschools and soccer clubs. Michael wrote: -- Nil Satis Nisi Optimum "Dave Peterson" wrote: One translation: "nothing but the best is good enough." Michael wrote: -- Nil Satis Nisi Optimum He he. You are good! Do you also know where i found it? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Using Jet to read excel file returns blank for last cell - sometim | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |