| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create cell formula from a relative position
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create cell formula from a relative position
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create cell formula from a relative position
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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Position drawing object relative to cell | Excel Discussion (Misc queries) | |||
| Excel Formula using relative position of cells in two different worksheets | Excel Discussion (Misc queries) | |||
| Excel Formula using relative position of cells in two different worksheets | Excel Worksheet Functions | |||
| Displaying a cell relative to the position to another cell | Excel Discussion (Misc queries) | |||
| Relative Cell position NOT working with or without macro | Excel Discussion (Misc queries) | |||
![Geoff[_11_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
