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
> > >
> > >
> > >
>
>
>