Find the cell value in excel by using vb code

[Guest]

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]

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

 

[Guest]

--
Nil Satis Nisi Optimum

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.

 

[Dave Peterson]

One translation:

"nothing but the best is good enough."

 

[Guest]

--
Nil Satis Nisi Optimum

One translation:

"nothing but the best is good enough."

He he.
You are good!

Do you also know where i found it?

 

[Dave Peterson]

I used google. It knows everything.

It seems to be a common motto for highschools and soccer clubs.