putting a formula in a cell - VBA?? 2

[stephenmbell]

I am using access to export to an excel sheet sales for items.

each item has 10 rows on the excel sheet, and they are populated by row, sequentially.

there are 3 rows that I would like to display... for this example we will say rows 4,5,6 (out of the 10 per item).

I want rows 4,5, & 6 to display =[myNumber] - row10 (not yet outputted) across 8 columns (where myNumber is a value coming from a recordset in access.

Any suggestions?

Thanks in advance, hope this was explained in an understandable manner.

 

[SkipVought]

Hi,

Clear a mud!

Perhaps a concrete example demonstrating that you are takling about.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[stephenmbell]

Sorry -- here goes...

for each item i am outputting:
A
------
1 sales
2 (sales - avg_sales_district)
3 profit
4 competitor1price - [retail price]
5 competitor2price - [retail price]
6 competitor3price - [retail price]
7 retailprice

----
This will pretty much be the format for all of my items, it just repeats. So I know that (in this example) that retail price will always be 3 rows below comp1, 2 below comp2 and 1 below comp1...

I am currently outputting simply the comp_prices from a recordset, using range.copyfromrecordset, but i need to show the difference relative to our price (and I have no way of getting the difference into the recordset) -...

SO -

I want to say (for example) instead of putting the competition1price in cell A4, I want to put in there competition1price - A7

At the point in the code where I would set the value of cell A4, cell A7 (the retail price) does not yet have anything in it. (That is the 1st issue)

The second is this: I am learning this stuff "on-the-fly" - I spent a good amount of time today trying to assign a formula to a cell through vba (in MS Access)and was not successful.

I was setting:

wksht.cells(rng.row, rng.column).formula = "somecell - someothercell"

and for some reason it was not working. I also toyed with the FormulaR1C1 and was unsuccessful with that as well.

OK - hopefully this is better, sorry for the brain dump to the forum.

Any input is appreciated.

 

[SkipVought]

You're getting close, but his is NOT a "concrete example"

What wrong with actual values and an explanation of "somecell - someothercell" cuz that could mean almost ANYTHING!!!

HELP ME OUT!


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[stephenmbell]

ok. Take three:
In the most simplistic form


I am outputting competitor prices to column A row 6 with VBA code from access (from a recordset)

In the code after I output the data to A6, in A10 I am outputting retailprice (from a different recordset)

I want to know -

A)instead of putting a value in A6, I want the formula A6-A10.

B)Can I put A10 in the formula if A10 has no value yet??

Thank you for your patience. HTH

 

[SkipVought]

"A)instead of putting a value in A6, I want the formula A6-A10."

That would cause a circular reference. Just try doing that on the sheet manually and see what happens.

"B)Can I put A10 in the formula if A10 has no value yet??"

Yes, but a) nothing is ZERO in the formula and b) you still have a circular reference.

You need to get you logic straightened out, before doing anything in VBA.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[stephenmbell]

I am sorry, I mistyped

For the formula I want it to be:

Competitionprice-A10

Thx

 

[SkipVought]

Range("A6").Value = Competitionprice - Range("A10")

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[stephenmbell]

ok so that would be setting the value.

what if i wanted it to be a formula.. in other words..

I am going through my code... I get to the part where I am working with A6 and i say..

I want A6 to equal compprice - A10 ..

my code continues on and I get to A10 and I set A10 = 199.99

So in theory I want A6 = compprice - 199.99

In the example you gave me, going in the order I just described, A6 will be compprice - 0

Correct?

Wouldnt I want to use the .Formula property of the cell/range???

TY

 

[xlbo]

ok - for your formula setting, you were using:
wksht.cells(rng.row, rng.column).formula = "somecell - someothercell"


this needs to be:

wksht.RANGE(CELLS(rng.row), CELLS(rng.column)).formula = somecell - someothercell


For your relative formula, I think you need to use:

Range("A6").FORMULA = "=" & Competitionprice & "- A10"

Presumably though as you need to use a counter to determine the row, you would need to use something like:

rw1 = 6
rw2 = 10

Range("A" & rw1).FORMULA = "=" & Competitionprice & "- A" & rw2

Not sure how you would set the rw1 & rw2 as I'm not 100% sure of the process that is happening (too early still here in the UK - need more coffee) but hopefully you get the idea - you could base it on a loop and assign them in there:

For i = 2 to SomeLastCell step 10
rw1 = i
rw2 = i+3
'assign formula etc
next i

that is just an example of how you could set these values in a loop - hopefully that helps

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Have you actually done this stuff on the sheet? You might not even need VBA, it seems to me.

How, for instance, are you assigning Competitionprice?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[AnotherHiggins]

This isn't the question, but If I understand your current layout, I'd strongly recommend that you change the structure of your data. This could probably be done back in the Access Query.

This line in your second post:
[ul][li]This will pretty much be the format for all of my items, it just repeats. So I know that (in this example) that retail price will always be 3 rows below comp1, 2 below comp2 and 1 below comp1[/li][/ul]
leads me to think that your data currently looks something like this:
[tt]
A
------
1 Thing 1 sales
2 Thing 1 (sales - avg_sales_district)
3 Thing 1 profit
4 Thing 1 competitor1price - [retail price]
5 Thing 1 competitor2price - [retail price]
6 Thing 1 competitor3price - [retail price]
7 Thing 1 retailprice
1 Thing 2 sales
8 Thing 2 (sales - avg_sales_district)
9 Thing 2 profit
10 Thing 2 competitor1price - [retail price]
11 Thing 2 competitor2price - [retail price]
12 Thing 2 competitor3price - [retail price]
13 Thing 2 retailprice
[/tt]
If that's correct, you should have it laid out like this instead:
[tt]
A B C D E F G H I
1 Thing# Sales Sales-ASD Profit Comp1 Comp2 Comp3 Retail BestPrice
2 1 A1 B1 P1 1C1 2C1 3C1 R1 Formula
3 2 A2 B2 P2 2C1 2C2 2C3 R1 Formula
[/tt]
The formula for BestPrice can find the minimum of (Comp1 - Retail), (Comp2 - Retail) and (Comp3 - Retail).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[stephenmbell]

Thank you all for your input, I got it working.... For those interested, here is the code I used:

nOffset = 4

For nCompCounter = 1 To 3
  Set rs = fnGetRecordset(strsql)
   
  rng.CopyFromRecordset rs
  rs.Close
  
  'loop the range to set the formula
  For nCol = 0 To 8
    strCompPrice = wks.Cells(rng.Row, rng.Column + nCol).Value
    If strCompPrice = "" Then
      strCompPrice = "0"
    End If
    
    If strCompPrice <> "" And CInt(strCompPrice) <> 0 Then
      strRetailPrice = wks.Cells(rng.Row + nOffset, rng.Column + nCol).Address
      wks.Cells(rng.Row, rng.Column + nCol).Formula = "=(" & strCompPrice & "-" & strRetailPrice & ")*100"
    Else
      wks.Cells(rng.Row, rng.Column + nCol).Clear
    End If
  Next nCol
  
  nOffset = nOffset - 1
  
  With rng
    .HorizontalAlignment = xlCenter
  End With
  
  'align the comp name left
  Set rng = wks.Range(cLabelCol & nRowCounter)
  rng.HorizontalAlignment = xlLeft
  
  nRowCounter = nRowCounter + 1
  Set rng = wks.Range(cStartCol & nRowCounter & ":" & cCompEndCol & nRowCounter)
     
Next