Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object range method runtime error
In my application there are values i want to place in consistently
named cells. The location of these cells can vary by row. They must appear after the row which has a row number of endmanifrow. my "developing" code is below. Things were going fine until i got to the "method" I really wanted, "try #3" when I got the runtime error. I suppose I can build a string variable to hold the E6 value, (try#2) from the endmanifrow and trackcol values, but I'd prefer not to. In the module's declarations: tpdmanif is dim'd as worksheet. zzbranchstdT is dim'd as object Any suggestions on how to implement the try#3 method? Thanks. Neal Z Sub e_develop() Call n_AJC_TPD_Common_Values ''' holds the sheet names If UCase(ActiveSheet.name) < ajc_sheetname And _ UCase(ActiveSheet.name) < tpd_sheetname And ActiveSheet.name < "Sheet1" Then MsgBox ActiveSheet.name & " IS INVALID" Exit Sub End If Call zfind_endofmanifest(1, endmanifrow, "test") If endmanifrow = 0 Then Exit Sub Call zfind_trackcol(endmanifrow, 4, 50, trackcol, "test") If trackcol = 0 Then Exit Sub Set tpdmanif = Sheets(tpd_sheetname) Set tpdmanif = Sheets("Sheet1") '''test ''' Set zzbranchstdT = tpdmanif.range("E6") ''' try #1, this worked ''' Dim cell: cell = "E6" ''' try#2 lines worked, but not what I really want ''' Set zzbranchstdT = tpdmanif.range(cell) ''' try #3 THIS IS WHAT I WANT BUT AM GETTING RUNTIME ERROR, METHOD RANGE OF OBJECT FAILED Dim cell: cell = "Cells(endmanifrow + 1, trackcol)" Set zzbranchstdT = tpdmanif.range(cell) ''' THIS IS THE LINE THAT ERROR'D OUT ''' Set zzrowT = tpdmanif.range(Cells(endmanifrow + 2, trackcol)) ''' Set postitT = tpdmanif.range(Cells(endmanifrow + 3, trackcol)) zzbranchstdT = 811 ''' TEST LOADING THE CELL, worked for trys 1 and 2 End Sub -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object range method runtime error
Neal
I can see two reasons this method may fail. The first is that when using the cells property with Range you need to specify Cells twice. So your code would read: Set zzbranchstdT = tpdmanif.range(cell,cell) Alternately you could just use the cells property of the sheet. So Set zzbranchstdT = tpdmanif.cell. However this still doesn't work for me as VBA doesn't seem to want to accept the string variable here. Why don't you just use Set zzbranchstdT = tpdmanif.Cells(endmanifrow + 1, trackcol) which takes away the need to set and use the cell variable. Hope this helps Rowan "Neal Zimm" wrote: In my application there are values i want to place in consistently named cells. The location of these cells can vary by row. They must appear after the row which has a row number of endmanifrow. my "developing" code is below. Things were going fine until i got to the "method" I really wanted, "try #3" when I got the runtime error. I suppose I can build a string variable to hold the E6 value, (try#2) from the endmanifrow and trackcol values, but I'd prefer not to. In the module's declarations: tpdmanif is dim'd as worksheet. zzbranchstdT is dim'd as object Any suggestions on how to implement the try#3 method? Thanks. Neal Z Sub e_develop() Call n_AJC_TPD_Common_Values ''' holds the sheet names If UCase(ActiveSheet.name) < ajc_sheetname And _ UCase(ActiveSheet.name) < tpd_sheetname And ActiveSheet.name < "Sheet1" Then MsgBox ActiveSheet.name & " IS INVALID" Exit Sub End If Call zfind_endofmanifest(1, endmanifrow, "test") If endmanifrow = 0 Then Exit Sub Call zfind_trackcol(endmanifrow, 4, 50, trackcol, "test") If trackcol = 0 Then Exit Sub Set tpdmanif = Sheets(tpd_sheetname) Set tpdmanif = Sheets("Sheet1") '''test ''' Set zzbranchstdT = tpdmanif.range("E6") ''' try #1, this worked ''' Dim cell: cell = "E6" ''' try#2 lines worked, but not what I really want ''' Set zzbranchstdT = tpdmanif.range(cell) ''' try #3 THIS IS WHAT I WANT BUT AM GETTING RUNTIME ERROR, METHOD RANGE OF OBJECT FAILED Dim cell: cell = "Cells(endmanifrow + 1, trackcol)" Set zzbranchstdT = tpdmanif.range(cell) ''' THIS IS THE LINE THAT ERROR'D OUT ''' Set zzrowT = tpdmanif.range(Cells(endmanifrow + 2, trackcol)) ''' Set postitT = tpdmanif.range(Cells(endmanifrow + 3, trackcol)) zzbranchstdT = 811 ''' TEST LOADING THE CELL, worked for trys 1 and 2 End Sub -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object range method runtime error
Hi Rowan,
Not only did it help like crazy but also worked like a charm. Thanks. I'm kinda new to VBA with Excel, and keep forgetting about the properties, shame really, but I find them hard to look up in the excel help arena. Do you know of a book or reference that presents them in more of a "lookable upable" format? by the way, when it comes to using range with cells, when I only want one, I "cheat" and use: range(Cells(x, y), Cells(x, y)).Formula = "aaa" but I felt this would muddy the question I was asking. Again, Thanks, Neal "Rowan" wrote: Neal I can see two reasons this method may fail. The first is that when using the cells property with Range you need to specify Cells twice. So your code would read: Set zzbranchstdT = tpdmanif.range(cell,cell) Alternately you could just use the cells property of the sheet. So Set zzbranchstdT = tpdmanif.cell. However this still doesn't work for me as VBA doesn't seem to want to accept the string variable here. Why don't you just use Set zzbranchstdT = tpdmanif.Cells(endmanifrow + 1, trackcol) which takes away the need to set and use the cell variable. Hope this helps Rowan "Neal Zimm" wrote: In my application there are values i want to place in consistently named cells. The location of these cells can vary by row. They must appear after the row which has a row number of endmanifrow. my "developing" code is below. Things were going fine until i got to the "method" I really wanted, "try #3" when I got the runtime error. I suppose I can build a string variable to hold the E6 value, (try#2) from the endmanifrow and trackcol values, but I'd prefer not to. In the module's declarations: tpdmanif is dim'd as worksheet. zzbranchstdT is dim'd as object Any suggestions on how to implement the try#3 method? Thanks. Neal Z Sub e_develop() Call n_AJC_TPD_Common_Values ''' holds the sheet names If UCase(ActiveSheet.name) < ajc_sheetname And _ UCase(ActiveSheet.name) < tpd_sheetname And ActiveSheet.name < "Sheet1" Then MsgBox ActiveSheet.name & " IS INVALID" Exit Sub End If Call zfind_endofmanifest(1, endmanifrow, "test") If endmanifrow = 0 Then Exit Sub Call zfind_trackcol(endmanifrow, 4, 50, trackcol, "test") If trackcol = 0 Then Exit Sub Set tpdmanif = Sheets(tpd_sheetname) Set tpdmanif = Sheets("Sheet1") '''test ''' Set zzbranchstdT = tpdmanif.range("E6") ''' try #1, this worked ''' Dim cell: cell = "E6" ''' try#2 lines worked, but not what I really want ''' Set zzbranchstdT = tpdmanif.range(cell) ''' try #3 THIS IS WHAT I WANT BUT AM GETTING RUNTIME ERROR, METHOD RANGE OF OBJECT FAILED Dim cell: cell = "Cells(endmanifrow + 1, trackcol)" Set zzbranchstdT = tpdmanif.range(cell) ''' THIS IS THE LINE THAT ERROR'D OUT ''' Set zzrowT = tpdmanif.range(Cells(endmanifrow + 2, trackcol)) ''' Set postitT = tpdmanif.range(Cells(endmanifrow + 3, trackcol)) zzbranchstdT = 811 ''' TEST LOADING THE CELL, worked for trys 1 and 2 End Sub -- Neal Z |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object range method runtime error
You're welcome, Neal.
Search on Amazon for John Walkenbach. I started out with his Excel 2000 Programming for Dummies and still use it as a reference from time to time. He also has a range of Excel Power Programming books if you are after something a little more comprehensive. Have fun Rowan "Neal Zimm" wrote: Hi Rowan, Not only did it help like crazy but also worked like a charm. Thanks. I'm kinda new to VBA with Excel, and keep forgetting about the properties, shame really, but I find them hard to look up in the excel help arena. Do you know of a book or reference that presents them in more of a "lookable upable" format? by the way, when it comes to using range with cells, when I only want one, I "cheat" and use: range(Cells(x, y), Cells(x, y)).Formula = "aaa" but I felt this would muddy the question I was asking. Again, Thanks, Neal "Rowan" wrote: Neal I can see two reasons this method may fail. The first is that when using the cells property with Range you need to specify Cells twice. So your code would read: Set zzbranchstdT = tpdmanif.range(cell,cell) Alternately you could just use the cells property of the sheet. So Set zzbranchstdT = tpdmanif.cell. However this still doesn't work for me as VBA doesn't seem to want to accept the string variable here. Why don't you just use Set zzbranchstdT = tpdmanif.Cells(endmanifrow + 1, trackcol) which takes away the need to set and use the cell variable. Hope this helps Rowan "Neal Zimm" wrote: In my application there are values i want to place in consistently named cells. The location of these cells can vary by row. They must appear after the row which has a row number of endmanifrow. my "developing" code is below. Things were going fine until i got to the "method" I really wanted, "try #3" when I got the runtime error. I suppose I can build a string variable to hold the E6 value, (try#2) from the endmanifrow and trackcol values, but I'd prefer not to. In the module's declarations: tpdmanif is dim'd as worksheet. zzbranchstdT is dim'd as object Any suggestions on how to implement the try#3 method? Thanks. Neal Z Sub e_develop() Call n_AJC_TPD_Common_Values ''' holds the sheet names If UCase(ActiveSheet.name) < ajc_sheetname And _ UCase(ActiveSheet.name) < tpd_sheetname And ActiveSheet.name < "Sheet1" Then MsgBox ActiveSheet.name & " IS INVALID" Exit Sub End If Call zfind_endofmanifest(1, endmanifrow, "test") If endmanifrow = 0 Then Exit Sub Call zfind_trackcol(endmanifrow, 4, 50, trackcol, "test") If trackcol = 0 Then Exit Sub Set tpdmanif = Sheets(tpd_sheetname) Set tpdmanif = Sheets("Sheet1") '''test ''' Set zzbranchstdT = tpdmanif.range("E6") ''' try #1, this worked ''' Dim cell: cell = "E6" ''' try#2 lines worked, but not what I really want ''' Set zzbranchstdT = tpdmanif.range(cell) ''' try #3 THIS IS WHAT I WANT BUT AM GETTING RUNTIME ERROR, METHOD RANGE OF OBJECT FAILED Dim cell: cell = "Cells(endmanifrow + 1, trackcol)" Set zzbranchstdT = tpdmanif.range(cell) ''' THIS IS THE LINE THAT ERROR'D OUT ''' Set zzrowT = tpdmanif.range(Cells(endmanifrow + 2, trackcol)) ''' Set postitT = tpdmanif.range(Cells(endmanifrow + 3, trackcol)) zzbranchstdT = 811 ''' TEST LOADING THE CELL, worked for trys 1 and 2 End Sub -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error 434, object does not support this method or property | Excel Discussion (Misc queries) | |||
Runtime 1004 error -- insert method of range class failed. | Excel Discussion (Misc queries) | |||
runtime error 1004 method range of object '_global failed | Excel Discussion (Misc queries) | |||
Runtime Error 1004 - Method Range of '_Global failed' | Excel Programming | |||
runtime error '1004' delete Method of Range Class Failed | Excel Programming |