retrieving cell reference data
The a a few approaches in doing this
1) Put a formula on the worksheet
=Sum(A1:Y1)
Then copy done the worksheet. The only problem if you keep on adding new
rows you need to copy done the entire column (to 65536) and you will have
zeroes in rows without data
2) Similar to 1 above except put an if statement. The only problem with the
IF is cell in not consider empty when no data is in the row.
=if(countA(A1:Y1)0,Sum(A1:Y1),"")
3) Use a worksheet change function to add the formula when data is entered
in the cell
Sub worksheet_change(ByVal target As Range)
For Each cell In target
If Application.Intersect(target, Columns("Z")) Is Nothing Then
Range("Z" & target.Row).Formula = _
"=Sum(A" & target.Row & ":Y" & target.Row & ")"
End If
Next
End Sub
4) If you don't want a formula in the cell instead a real number
Sub worksheet_change(ByVal target As Range)
For Each cell In target
If Application.Intersect(target, Columns("Z")) Is Nothing Then
Range("Z" & target.Row).Formula = _
Evaluate("Sum(A" & target.Row & ":Y" & target.Row & ")")
End If
Next
End Sub
"thomas donino" wrote:
I am trying to total Z30 for example once data starts getting entered into
row30. For example once the user starts enter data into the cells in row 30 ,
Z30 begins to update the running total of all data entered in row 30
"Joel" wrote:
If you are writing a SUB then
ActiveCell
If you want to create a UDF (User Define function) like you would use on a
worksheet then You will pas the cell(s) that you are using in the function
=MySum(A1:b10_
function MySum(target as range)
MySum = 0
for each cell in target
MySum = MySum + cell.value
next cell
end function
"thomas donino" wrote:
I am new to this so pardon my ignorance. I am trying to write a short macro
but first I need to obtain the row and column info of the cell that is
currently selected. How do I do so?
|