| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
dim problem when using cell() reference
Thanks...that helped
"Jim Thomlinson" wrote: Oops... Dim Total as Range or better yet change Total to something like Dim rngTotal as range -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell reference formula problem | Excel Discussion (Misc queries) | |||
| Problem w/ cell reference | Excel Discussion (Misc queries) | |||
| Cell reference problem | Excel Worksheet Functions | |||
| problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
| Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Threaded Mode
