| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dim problem when using cell() reference
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dim problem when using cell() reference
I don't think you can use "row" as a variable. Try just "r".
HTH, Paul -- "aimee209" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dim problem when using cell() reference
I changed to just "r" and I'm still seeing the same error message that an
object is required. "PCLIVE" wrote: I don't think you can use "row" as a variable. Try just "r". HTH, Paul -- "aimee209" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dim problem when using cell() reference
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 -- "aimee209" wrote in message ... I changed to just "r" and I'm still seeing the same error message that an object is required. "PCLIVE" wrote: I don't think you can use "row" as a variable. Try just "r". HTH, Paul -- "aimee209" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dim problem when using cell() reference
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dim problem when using cell() reference
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 |
|
#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 |
| Reply |
| 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 | |||
Linear Mode
