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

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

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

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