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...
--
HTH...
Jim Thomlinson
"aimee209" wrote:
> 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
Similar Threads
