Create cell formula from a relative position

I am trying to interate through a MS Access recordset copying the field
contents to each sucessive row in an Excel Worksheet using the following
code and everything is working fine except I would like to create calculated
fields instead for the xlCell.Offset(1, 0).Range("D1").Value =
rstRs.Fields("Total Cost").Value such as (C4 * H4) only using the relative
row position as the fields are written to the row. I'm also having trouble
getting the "xlCell.Offset(0, 4).Range("E1").Font.Bold = True" to bold face
the group header written by "xlCell.Offset(0, 4).Value = rstRs!Desc_". Any
suggestions would be greatly appreciated. My codes is as follows:

Do While rstRs.EOF <> True
If Not (mCatCode = OldCatCode) Then
Set xlCell = xlCell.Offset(1, 0)
xlCell.Offset(0, 4).Value = rstRs!Desc_
xlCell.Offset(0, 4).Range("E1").Font.Bold = True
OldCatCode = mCatCode
End If
xlCell.Offset(1, 0).Range("A1").Value =
rstRs.Fields("Code").Value
xlCell.Offset(1, 0).Range("B1").Value = rstRs.Fields("Bin
#").Value
xlCell.Offset(1, 0).Range("C1").Value =
rstRs.Fields("Price").Value
xlCell.Offset(1, 0).Range("D1").Value = rstRs.Fields("Total
Cost").Value
xlCell.Offset(1, 0).Range("E1").Value =
rstRs.Fields("Inventory").Value
xlCell.Offset(1, 0).Range("F1").Value =
rstRs.Fields("Begin").Value
xlCell.Offset(1, 0).Range("G1").Value =
rstRs.Fields("Buy").Value
xlCell.Offset(1, 0).Range("H1").Value =
rstRs.Fields("End").Value
xlCell.Offset(1, 0).Range("I1").Value =
rstRs.Fields("Total").Value
xlCell.Offset(1, 0).Range("J1").Value =
rstRs.Fields("Aloha").Value
xlCell.Offset(1, 0).Range("K1").Value =
rstRs.Fields("Adjust").Value
rstRs.MoveNext
If rstRs.EOF = False Then
mCatCode = rstRs!CatCode
End If
Set xlCell = xlCell.Offset(1, 0)

Loop

Geoff: Try using
xlCell.Offset(0, 4).Range("A1").Font.Bold =True
instead of
xlCell.Offset(0, 4).Range("E1").Font.Bold = True

And, in general, this is the way it works: Offset gives a range the same
size as your original range but offset by the given number of rows/columns;
if you need to refer to a specific cell within that range you need a way to
do that; if you use the .Range() property to do it then you need to think of
the address you supply as being an "equivalent" address within the result
range, e.g. "A1" is the cell in the upper left corner of the result range,
etc.

"Geoff" wrote:

> I am trying to interate through a MS Access recordset copying the field
> contents to each sucessive row in an Excel Worksheet using the following
> code and everything is working fine except I would like to create calculated
> fields instead for the xlCell.Offset(1, 0).Range("D1").Value =
> rstRs.Fields("Total Cost").Value such as (C4 * H4) only using the relative
> row position as the fields are written to the row. I'm also having trouble
> getting the "xlCell.Offset(0, 4).Range("E1").Font.Bold = True" to bold face
> the group header written by "xlCell.Offset(0, 4).Value = rstRs!Desc_". Any
> suggestions would be greatly appreciated. My codes is as follows:
>
> Do While rstRs.EOF <> True
> If Not (mCatCode = OldCatCode) Then
> Set xlCell = xlCell.Offset(1, 0)
> xlCell.Offset(0, 4).Value = rstRs!Desc_
> xlCell.Offset(0, 4).Range("E1").Font.Bold = True
> OldCatCode = mCatCode
> End If
> xlCell.Offset(1, 0).Range("A1").Value =
> rstRs.Fields("Code").Value
> xlCell.Offset(1, 0).Range("B1").Value = rstRs.Fields("Bin
> #").Value
> xlCell.Offset(1, 0).Range("C1").Value =
> rstRs.Fields("Price").Value
> xlCell.Offset(1, 0).Range("D1").Value = rstRs.Fields("Total
> Cost").Value
> xlCell.Offset(1, 0).Range("E1").Value =
> rstRs.Fields("Inventory").Value
> xlCell.Offset(1, 0).Range("F1").Value =
> rstRs.Fields("Begin").Value
> xlCell.Offset(1, 0).Range("G1").Value =
> rstRs.Fields("Buy").Value
> xlCell.Offset(1, 0).Range("H1").Value =
> rstRs.Fields("End").Value
> xlCell.Offset(1, 0).Range("I1").Value =
> rstRs.Fields("Total").Value
> xlCell.Offset(1, 0).Range("J1").Value =
> rstRs.Fields("Aloha").Value
> xlCell.Offset(1, 0).Range("K1").Value =
> rstRs.Fields("Adjust").Value
> rstRs.MoveNext
> If rstRs.EOF = False Then
> mCatCode = rstRs!CatCode
> End If
> Set xlCell = xlCell.Offset(1, 0)
>
> Loop
>
>
>

Thanks! The bold worked great but can you give me an example of adressing
the relative position I tried xlCell.Offset(1, 0).Range("D1") = "=(C1*H1)"
but it only calculates the absolute position of C1*H1

"K Dales" <KDales@discussions.microsoft.com> wrote in message
news:3E6D8D40-9B89-4B07-A07C-FAA1D3C7083C@microsoft.com...
> Geoff: Try using
> xlCell.Offset(0, 4).Range("A1").Font.Bold =True
> instead of
> xlCell.Offset(0, 4).Range("E1").Font.Bold = True
>
> And, in general, this is the way it works: Offset gives a range the same
> size as your original range but offset by the given number of
rows/columns;
> if you need to refer to a specific cell within that range you need a way
to
> do that; if you use the .Range() property to do it then you need to think
of
> the address you supply as being an "equivalent" address within the result
> range, e.g. "A1" is the cell in the upper left corner of the result range,
> etc.
>
> "Geoff" wrote:
>
> > I am trying to interate through a MS Access recordset copying the field
> > contents to each sucessive row in an Excel Worksheet using the following
> > code and everything is working fine except I would like to create
calculated
> > fields instead for the xlCell.Offset(1, 0).Range("D1").Value =
> > rstRs.Fields("Total Cost").Value such as (C4 * H4) only using the
relative
> > row position as the fields are written to the row. I'm also having
trouble
> > getting the "xlCell.Offset(0, 4).Range("E1").Font.Bold = True" to bold
face
> > the group header written by "xlCell.Offset(0, 4).Value = rstRs!Desc_".
Any
> > suggestions would be greatly appreciated. My codes is as follows:
> >
> > Do While rstRs.EOF <> True
> > If Not (mCatCode = OldCatCode) Then
> > Set xlCell = xlCell.Offset(1, 0)
> > xlCell.Offset(0, 4).Value = rstRs!Desc_
> > xlCell.Offset(0, 4).Range("E1").Font.Bold = True
> > OldCatCode = mCatCode
> > End If
> > xlCell.Offset(1, 0).Range("A1").Value =
> > rstRs.Fields("Code").Value
> > xlCell.Offset(1, 0).Range("B1").Value =
rstRs.Fields("Bin
> > #").Value
> > xlCell.Offset(1, 0).Range("C1").Value =
> > rstRs.Fields("Price").Value
> > xlCell.Offset(1, 0).Range("D1").Value =
rstRs.Fields("Total
> > Cost").Value
> > xlCell.Offset(1, 0).Range("E1").Value =
> > rstRs.Fields("Inventory").Value
> > xlCell.Offset(1, 0).Range("F1").Value =
> > rstRs.Fields("Begin").Value
> > xlCell.Offset(1, 0).Range("G1").Value =
> > rstRs.Fields("Buy").Value
> > xlCell.Offset(1, 0).Range("H1").Value =
> > rstRs.Fields("End").Value
> > xlCell.Offset(1, 0).Range("I1").Value =
> > rstRs.Fields("Total").Value
> > xlCell.Offset(1, 0).Range("J1").Value =
> > rstRs.Fields("Aloha").Value
> > xlCell.Offset(1, 0).Range("K1").Value =
> > rstRs.Fields("Adjust").Value
> > rstRs.MoveNext
> > If rstRs.EOF = False Then
> > mCatCode = rstRs!CatCode
> > End If
> > Set xlCell = xlCell.Offset(1, 0)
> >
> > Loop
> >
> >
> >

When you use "=(C1*H1)" it is exactly as if you typed that into the cell, and
so no matter what cell you put this formula in it will refer to C1 and H1
(although if you then copy it and paste, it would behave as a relative
reference - just as if you had typed it). So you have two options:

1) You can try to use your code to figure out the proper cell addresses to
use in the particular cell you are pointing at. I assume you always want
columns C and H, but you want the row to be relative? The row you are on
would be xlCell.Row, so you could do this:
xlCell.Offset(1, 0).Range("D1") = "=(C" & xlCell.Row & "*H" & xlCell.Row & ")"
Note how I am piecing the formula together - it is a little awkward, but I
think it should work.

2) After the long, complicated option, here is a simpler one: use R1C1 type
references in your formula:
xlCell.Offset(1, 0).Range("D1") = "=R[0]C[-1]*R[0]C[4]"
(assuming you are in column D, this takes the number in same row, column C -
i.e. -1 column to the left, and multiplies it by number in same row, column H
- i.e. 4 columns to the right)

Sorry to be so long-winded in the explanations but it appears this is new to
you and I always try not to simply answer the question but to try to give
enough info so you can understand and learn a bit. HTH!


"Geoff" wrote:

> Thanks! The bold worked great but can you give me an example of adressing
> the relative position I tried xlCell.Offset(1, 0).Range("D1") = "=(C1*H1)"
> but it only calculates the absolute position of C1*H1
>
> "K Dales" <KDales@discussions.microsoft.com> wrote in message
> news:3E6D8D40-9B89-4B07-A07C-FAA1D3C7083C@microsoft.com...
> > Geoff: Try using
> > xlCell.Offset(0, 4).Range("A1").Font.Bold =True
> > instead of
> > xlCell.Offset(0, 4).Range("E1").Font.Bold = True
> >
> > And, in general, this is the way it works: Offset gives a range the same
> > size as your original range but offset by the given number of
> rows/columns;
> > if you need to refer to a specific cell within that range you need a way
> to
> > do that; if you use the .Range() property to do it then you need to think
> of
> > the address you supply as being an "equivalent" address within the result
> > range, e.g. "A1" is the cell in the upper left corner of the result range,
> > etc.
> >
> > "Geoff" wrote:
> >
> > > I am trying to interate through a MS Access recordset copying the field
> > > contents to each sucessive row in an Excel Worksheet using the following
> > > code and everything is working fine except I would like to create
> calculated
> > > fields instead for the xlCell.Offset(1, 0).Range("D1").Value =
> > > rstRs.Fields("Total Cost").Value such as (C4 * H4) only using the
> relative
> > > row position as the fields are written to the row. I'm also having
> trouble
> > > getting the "xlCell.Offset(0, 4).Range("E1").Font.Bold = True" to bold
> face
> > > the group header written by "xlCell.Offset(0, 4).Value = rstRs!Desc_".
> Any
> > > suggestions would be greatly appreciated. My codes is as follows:
> > >
> > > Do While rstRs.EOF <> True
> > > If Not (mCatCode = OldCatCode) Then
> > > Set xlCell = xlCell.Offset(1, 0)
> > > xlCell.Offset(0, 4).Value = rstRs!Desc_
> > > xlCell.Offset(0, 4).Range("E1").Font.Bold = True
> > > OldCatCode = mCatCode
> > > End If
> > > xlCell.Offset(1, 0).Range("A1").Value =
> > > rstRs.Fields("Code").Value
> > > xlCell.Offset(1, 0).Range("B1").Value =
> rstRs.Fields("Bin
> > > #").Value
> > > xlCell.Offset(1, 0).Range("C1").Value =
> > > rstRs.Fields("Price").Value
> > > xlCell.Offset(1, 0).Range("D1").Value =
> rstRs.Fields("Total
> > > Cost").Value
> > > xlCell.Offset(1, 0).Range("E1").Value =
> > > rstRs.Fields("Inventory").Value
> > > xlCell.Offset(1, 0).Range("F1").Value =
> > > rstRs.Fields("Begin").Value
> > > xlCell.Offset(1, 0).Range("G1").Value =
> > > rstRs.Fields("Buy").Value
> > > xlCell.Offset(1, 0).Range("H1").Value =
> > > rstRs.Fields("End").Value
> > > xlCell.Offset(1, 0).Range("I1").Value =
> > > rstRs.Fields("Total").Value
> > > xlCell.Offset(1, 0).Range("J1").Value =
> > > rstRs.Fields("Aloha").Value
> > > xlCell.Offset(1, 0).Range("K1").Value =
> > > rstRs.Fields("Adjust").Value
> > > rstRs.MoveNext
> > > If rstRs.EOF = False Then
> > > mCatCode = rstRs!CatCode
> > > End If
> > > Set xlCell = xlCell.Offset(1, 0)
> > >
> > > Loop
> > >
> > >
> > >
>
>
>