dim problem when using cell() reference

[aimee209]

I am trying to write codes that will add or subtract a number from a previous
number, depending on whether or not certain cells are blank. It's an excel
version of a checkbook: if there's nothing listed in credit, subtract the
debit and if the debit is blank, add the credit to the previous balance.
I'm having trouble with the dim part for the total. I don't know what to
set it as.
I've attached the code I have so far.
Thanks!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim row As Integer
row = 3
Dim total As Long

Do
Set total = Sheets("Sheet1").Cells(row, F)

If Cells(row, D) = 0 Then
If Cells(row, E) = 0 Then
total.Value = 0
Else
total.Value = Cells(row - 1, F) + Cells(row, E)
End If
Else: total.Value = Cells(row - 1, F) - Cells(row, D)
End If

If row < 100 Then
row = row + 1
Else: End
End If

Loop
End Sub

 

[PCLIVE]

I don't think you can use "row" as a variable. Try just "r".

HTH,
Paul

 

[aimee209]

I changed to just "r" and I'm still seeing the same error message that an
object is required.

 

[PCLIVE]

Aimee,

I may have been mistaken about the row as a variable. It's just not good
practice since Row is already a qualifier.

Anyway, I see a view issues.
When using "Cells([Row],[Column})", the Row and Colunm references should be
numbers or variables that are numbers.
So:
Sheets("Sheet1").Cells(r, F)
Should be:
Sheets("Sheet1").Cells(r, 6)

Also, don't use "Set" for your "total" value.
total = Sheets("Sheet1").Cells(r, 6)

Then when referencing "total" later in the code, remove the ".Value".
So instead of :
total.Value = Cells(row - 1, F) + Cells(row, E)
Use:
total = Cells(r - 1, 6) + Cells(r, 5)

Hope this helps,
Paul




--

 

[Jim Thomlinson]

You are correct in that you have a Dim problem but it is not what you
think... In the line:
Set total = Sheets("Sheet1").Cells(row, F)
F needs to be in Quateation marks otherwise F will be decalred on the fly as
a variable of typw variant.
Set total = Sheets("Sheet1").Cells(row, "F")
To ensure that this does not happen in the future in the VBE select Tools ->
Options -> Editor and check "Require Variable Declarations". This will put
the compiler directive "Option Explicit at the top of any new code modules.
This will mean that you ahve to decalre all varaibles. If you do not declare
a variable (similar to F" then it will give you an error.
Check out this link...

http://www.cpearson.com/excel/variables.htm

Additionally, don't use row as a vairable. Instead you could use something
like
dim lngRow as long
Note that row should be a long as integer is too small to hold 56,536.
Finally Total should probably be a double...

 

[Jim Thomlinson]

Oops...
Dim Total as Range
or better yet change Total to something like
Dim rngTotal as range

 

[aimee209]

Thanks...that helped

Oops...
Dim Total as Range
or better yet change Total to something like
Dim rngTotal as range