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

J

jimx

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
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

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
 
Top