vba- excel- "for each" what row/col (cell) is it looking at?
 

Can you please tell me how to get the row and col of the cell that the
'for each cell in userrange' command in vba in excel is Processing.

ex:

a1=5
a2=4

function print("a1:a2")

for each cell in userrange
debug.print cell.Value
' I hope to be able to print a1 = 5 and a2=4
next
end function

will print
5
4

But how can I tell that it is processing a1 or a2 and put THAT valu
in a variable?

What IS cell.Value? is cell a class? and does this class have cell.re
or cell.row or ANYthing?

thank yo

--
Message posted from http://www.ExcelForum.com

vba- excel- "for each" what row/col (cell) is it looking at?
 

Hi
cell.row
cell.column

--
Regards
Frank Kabel
Frankfurt, Germany


Can you please tell me how to get the row and col of the cell that
the
'for each cell in userrange' command in vba in excel is
Processing.

ex:

a1=5
a2=4

function print("a1:a2")

for each cell in userrange
debug.print cell.Value
' I hope to be able to print a1 = 5 and a2=4
next
end function

will print
5
4

But how can I tell that it is processing a1 or a2 and put THAT value
in a variable?

What IS cell.Value? is cell a class? and does this class have
cell.ref
or cell.row or ANYthing?

thank you


---
Message posted from http://www.ExcelForum.com/

vba- excel- "for each" what row/col (cell) is it looking at?
 

In a
For Each cell In rng
type of statement, the code is iterating through each cell within the range
one at a time, so cell refers to a single cell range object. Hence

cell.row will return the row of the cell being process
cell.column returns the column
cell.value returns the value in that cell
cell.address(false,false) returns the cell address (e.g. A1)

The code you want seems to be

For Each cell In userrange
Debug.Print cell.Value
'or maybe MsgBox cell.value
Next cell

If you want to return these values as a worksheet function, you need more
complex processing to handle the array.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jimx " wrote in message
...
Can you please tell me how to get the row and col of the cell that the
'for each cell in userrange' command in vba in excel is Processing.

ex:

a1=5
a2=4

function print("a1:a2")

for each cell in userrange
debug.print cell.Value
' I hope to be able to print a1 = 5 and a2=4
next
end function

will print
5
4

But how can I tell that it is processing a1 or a2 and put THAT value
in a variable?

What IS cell.Value? is cell a class? and does this class have cell.ref
or cell.row or ANYthing?

thank you


---
Message posted from http://www.ExcelForum.com/

vba- excel- "for each" what row/col (cell) is it looking at?
 

Sub Main()
Dim rng as Range
set rng = range("A1:A2")
Range("A1").Value = 5
Range("A2").Value = 4
myPrint rng
End Sub

function myPrint(userrange as Range)
dim cell as Range
for each cell in userrange
debug.print cell.Address(0,0) & " = " & cell.Value
next
end function

--
Regards,
Tom Ogilvy


"jimx " wrote in message
...
Can you please tell me how to get the row and col of the cell that the
'for each cell in userrange' command in vba in excel is Processing.

ex:

a1=5
a2=4

function print("a1:a2")

for each cell in userrange
debug.print cell.Value
' I hope to be able to print a1 = 5 and a2=4
next
end function

will print
5
4

But how can I tell that it is processing a1 or a2 and put THAT value
in a variable?

What IS cell.Value? is cell a class? and does this class have cell.ref
or cell.row or ANYthing?

thank you


---
Message posted from http://www.ExcelForum.com/