CODEWITHSUNDEEP
vbaexcel
Michael Alfred Winklestine
Michael Alfred Winklestine

Reputation: 3

Excel Replacing Cell formatting with VBA

OK so i would like to figure out a way to change the cells within the worksheet behind the scenes of Excel 2007 with VBA so if i have something in Cell A1 and B1 then C1 will automatically populate a answer without having any formulas in the box.

i tried 

Private Sub Worksheet_Change()
dim weekCost as integer
dim monthCost as integer
dim salesTax as integer
dim totalCost as integer

weekCost = Range("$A$1")
monthCost = Range("$B$2")

salesTax = (weekCost + monthCost) * .08
totalCost = weekCost + monthCost

totalCost = salesTax + totalCost

totalCost = range("$C$1")

end sub

I cant get the totalcost to stick to that cell any one know how?

Upvotes: 0

Views: 181

Answers (3)

gwhenning
gwhenning

Reputation: 138

Or Worksheets("Sheet1").Cells(3,1).Value = totalCost

Upvotes: 0

Blackhawk
Blackhawk

Reputation: 6120

The most obvious fix is to change the last line to range("$C$1").Value = totalCost.

If that doesn't work, here are some other things to consider:

  1. One immediate issue I see with the code is that the Worksheet_Change sub should actually be defined as Private Sub Worksheet_Change(ByVal Target As Range)

  2. Make sure that the function is in the code page of the specific worksheet where you want the event to be captured. If you put it into a regular code module it will not be called.

  3. Another issue you may want to address is that right now the code would recalculate every time that any cell is modified, not just A1 or B1. You can put the code inside an If statement to limit when the recalculation is performed.

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

Your assignment statement is backwards, try:

Range("C1") = totalCost

Upvotes: 2

Related Questions