With ActiveSheet 3

[WaterSprite]

This code will work:

Worksheets("ChemBalanceSheet").select
With ActiveSheet
.Range(.cells(10, 94), .Cells(40, 94).Value = "=sum(I4:J4)
End With

This code will not work. Why not?

Worksheets("ChemBalanceSheet").select
With ActiveSheet
.Range(.cells(10, 94), .Cells(40, 94).Value = "=sum(.range(.cells(4,9), (.cells(4, 10))))
End With

 

[SkipVought]

Hi,

Try NOT using the Select method.

With Worksheets("ChemBalanceSheet")

.Range(.cells(10, 94), .Cells(40, 94).Value = "=sum(.range(.cells(4,9), (.cells(4, 10)).address(false, false)))"
End With

I believe I'm correct with the two false for RELATIVE, arguments to address function

Skip,

_[red][/red]

 

[ThomasLafferty]

One thing I noticed right away is the lack of closing quotes:

.Range(.cells(10, 94), .Cells(40, 94).Value = [!]"[/!]=sum(.range(.cells(4,9), (.cells(4, 10))))[!]"[/!]

Also, [!]Range{/!] is native to VBA, but not to Excel - it's not an Excel function.

What exactly would you like to accomplish?

You could establish a variable to hold a range reference

Dim Target As Range
'Select some cells to get your range
     Range("I4:J4").Select

'Dump that range into your variable
     Set Target = Selection

'Select some cells and write in your formula
     Range(Cells(10, 94), .Cells(40, 94).Select
     With Selection
         .Formula = "SUM(" & Target.Address & ")"
     End With

Hope this helps...
Tom

Live once die twice; live twice die once.

 

[SkipVought]

Tom

Isn't it...

"Born once die twice; Born twice die once."

???


Skip,

_[red][/red]

 

[ThomasLafferty]

Chip: Touché! I will modify it right away.

 

[WaterSprite]

Thanks for the help. I guess if I am going to keep messing around with this stuff I better take some classes at my local college so I don't waste so much time when I am trying to do something with VBA or VB6

 

[firefytr]

VBA is a subset of VB (which has up to version 6, which is no longer supported by Microsoft), so VBA and VB6 are very similar in their own regard. If only in Excel, you are not dealing with VB6, only VBA.

If you want to learn more about writing VBA code, I recommend hanging around the free online forums. First of all they're free, second of all you'll find some of the worlds smartest people in their respective fields hanging around those forums. Sometimes that's better than any college professor who decides they want to teach it the wrong way (it seems to happens quite often unfortunately).

Also, there is no real reason to select anything, be it sheets or a range. There is very little real world value held in that. Mostly, that is stuff picked up from the macro recorder or under-developed code. I generally recommend (without knowing anything of your data or even the point to this) setting the ranges to variables, something like ...

Dim ws as worksheet, rngFormula as range, rngResult as range
set ws = Worksheets("ChemBalanceSheet")
set rngFormula = ws.Range(ws.cells(10, 94), ws.Cells(40, 94))
set rngResult = ws.range(ws.cells(4,9), ws.cells(4, 10))
rngFormula.Formula = "=SUM(" & rngResult.Address & ")"

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[WaterSprite]

I know I am not supposed to use the Select statement, but sometimes it gets so frustrating trying to do something and nothing works, so I end grabbing a crutch, like the select statement.

Never in a million years would I have come up with the few lines of code that you have given me. Now that I see it, I understand, and I can take it and apply it to what I am doing. Thank you very much for your interest and assistance in what I am trying to do.

 

[firefytr]

Good luck WaterSprite!! Try, try and try again! 'Tis the way of the coder! ;-)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci