Referring to a cell(Newbie)

[San]

Hi this is my fiirst time programming vba with excel.

My problem is that i can't seem to use the cell i have refered to in a
formula.

When i write my initials in say cell A10 then excel puts in the Date =
format(now, .....) in cell B10.

I want to refer to the cell next to B10 which is C10 and sum C10 with
say C1. I use offset to refer to the cell next to B10 but i don't want
to refer to the value in C10 just the cell, so I can do a sum command
and save the result in another cell

[E4].Value = Application.Sum([C10:C1]) But i don't know C10 other than
it is next to B10...and i can't use that in the formula...

sorry its a bit hard to explain hope you understand me anyways.

 

[Die_Another_Day]

"other than
it is next to B10...and i can't use that in the formula..."
Sure you can...
Range("B10").Offset(0,1)

HTH

Charles

 

[San]

"other than
it is next to B10...and i can't use that in the formula..."
Sure you can...
Range("B10").Offset(0,1)

No that doesn't work I have tried that. I want the formula to do this:

[F5].Value = Apllication.Sum([C10:C1])

But If is write C10 as "Range("B10").Offset(0, 1)" then it would look
like this


[F5].Value = Apllication.Sum([Range("B10").Offset(0, 1):C1])

Well that doesn't work

 

[Gary Keramidas]

i'm not sure what you're looking for.
do you want a sum formula in E4?

Sub test()
Range("e4").Formula = "= sum(c10:c1)"
End Sub
--


Gary

"other than
it is next to B10...and i can't use that in the formula..."
Sure you can...
Range("B10").Offset(0,1)

No that doesn't work I have tried that. I want the formula to do this:

[F5].Value = Apllication.Sum([C10:C1])

But If is write C10 as "Range("B10").Offset(0, 1)" then it would look
like this


[F5].Value = Apllication.Sum([Range("B10").Offset(0, 1):C1])

Well that doesn't work

 

[San]

i'm not sure what you're looking for.
do you want a sum formula in E4?

Sub test()
Range("e4").Formula = "= sum(c10:c1)"
End Sub

Not exactly...I have 2 collums B and C. I have the date on B and the
amont of items made in collum C. So everyday i type my initials in
collum A and the date automaticly pops up in collum B. And then I type
in collum C how many items i have made that given day. But at the end
of the month i don't what cell in collum B i'll end up in. I want to
know how i can find out which cell it is, cause I need to sum the cell
next to which is C?? Basicly I want to sum the amount of items i have
made that month...which vary.

I am so bad at explaining this! Partly because I don't Excel or VBA
very well



Public Sub Worksheet_Change(ByVal Target As excel.Range)

On Error GoTo ErrorHandler

Dim dato As Date
Dim DatoAug As Date, DatoSept As Date, DatoOkt As Date, DatoNov As
Date, DatoDec As Date



dato = Format(Now, "mm-dd-yy")
DatoAug = Format("08-31-2006")
DatoSept = Format("09-30-2006")
DatoOkt = Format("10-31-2006")
DatoNov = Format("11-30-2006")
DatoDec = Format("12-31-2006")




With Target
If .Count > 1 Then Exit Sub
If .Column <> 1 Then Exit Sub

Application.EnableEvents = False
.Offset(0, 1).Value = Format(Now, "mm-dd-yy")
Application.EnableEvents = True

End With


If dato = DatoAug Then


[E4].Value = Application.Sum([???:C1])

At the end of the month I would like to sum collum C so I get the
amount of items for that given month. In clolum B I have the date.

 

[Die_Another_Day]

[E4].Value = "=Sum(C1:" & Range("C" &
Rows.Count).End(xlUp).Address(False,False) & ")"

Charles

i'm not sure what you're looking for.
do you want a sum formula in E4?

Sub test()
Range("e4").Formula = "= sum(c10:c1)"
End Sub

Not exactly...I have 2 collums B and C. I have the date on B and the
amont of items made in collum C. So everyday i type my initials in
collum A and the date automaticly pops up in collum B. And then I type
in collum C how many items i have made that given day. But at the end
of the month i don't what cell in collum B i'll end up in. I want to
know how i can find out which cell it is, cause I need to sum the cell
next to which is C?? Basicly I want to sum the amount of items i have
made that month...which vary.

I am so bad at explaining this! Partly because I don't Excel or VBA
very well



Public Sub Worksheet_Change(ByVal Target As excel.Range)

On Error GoTo ErrorHandler

Dim dato As Date
Dim DatoAug As Date, DatoSept As Date, DatoOkt As Date, DatoNov As
Date, DatoDec As Date



dato = Format(Now, "mm-dd-yy")
DatoAug = Format("08-31-2006")
DatoSept = Format("09-30-2006")
DatoOkt = Format("10-31-2006")
DatoNov = Format("11-30-2006")
DatoDec = Format("12-31-2006")




With Target
If .Count > 1 Then Exit Sub
If .Column <> 1 Then Exit Sub

Application.EnableEvents = False
.Offset(0, 1).Value = Format(Now, "mm-dd-yy")
Application.EnableEvents = True

End With


If dato = DatoAug Then


[E4].Value = Application.Sum([???:C1])

At the end of the month I would like to sum collum C so I get the
amount of items for that given month. In clolum B I have the date.

 

[San]

Many thx guys think I got it now again thx for your time