Help with formula in vb coding

M

mc

Hi, I am using excel 2003. I have a spreadsheet that has approx 3000 lines.
I am trying to code Col A to equal the values in Col C, D & E. And Col B = H
- G. The following code works, but takes a long time to execute:
xl_WksheetAC05_52712.Range("C2").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(-2,0 ).Value = ActiveCell.Value &
ActiveCell.Offset(0, 1).Value & ActiveCell.Offset(0, 2).Value
ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(0, 5).Value
- ActiveCell.Offset(0, 4).Value
ActiveCell.Offset(1, 0).Select
Loop

Is there a better solution? Thanks in advance.
 
T

Tom Hutchins

Try this:

Sub AddFormulas()
Dim LR As Long
'Activate xl_WksheetAC05_52712 (must set object variable first)
xl_WksheetAC05_52712.Activate
'Find the last cell in row C
LR& = Range("C2").End(xlDown).Row
'Add formulas in row 2
Range("A2").Formula = "=SUM(RC[2]:RC[4])"
Range("B2").Formula = "=RC[6]-RC[5]"
'Use AutoFill to copy the formulas down
Range("A2:B2").Select
Selection.AutoFill Destination:=Range("A2:B" & LR&)
Calculate
End Sub

Hope this helps,

Hutch
 
D

Don Guillett

Hard to visualize
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.
 
P

Patrick Molloy

WITH xl_WksheetAC05_52712.
WITH .Range(.Range("C2") , .Range("C2").End(xlDown) ).Offset(,-2)
.FormulaR1C1= "=RC3 & RC4 & RC5"
END WITH
WITH .Range(.Range("C2") , .Range("C2").End(xlDown) ).Offset(,-1)
.FormulaR1C1= "=RC8"
END WITH
END WITH
 
M

mc

Thanks, Tom for the help, it worked.

Tom Hutchins said:
Try this:

Sub AddFormulas()
Dim LR As Long
'Activate xl_WksheetAC05_52712 (must set object variable first)
xl_WksheetAC05_52712.Activate
'Find the last cell in row C
LR& = Range("C2").End(xlDown).Row
'Add formulas in row 2
Range("A2").Formula = "=SUM(RC[2]:RC[4])"
Range("B2").Formula = "=RC[6]-RC[5]"
'Use AutoFill to copy the formulas down
Range("A2:B2").Select
Selection.AutoFill Destination:=Range("A2:B" & LR&)
Calculate
End Sub

Hope this helps,

Hutch

mc said:
Hi, I am using excel 2003. I have a spreadsheet that has approx 3000 lines.
I am trying to code Col A to equal the values in Col C, D & E. And Col B = H
- G. The following code works, but takes a long time to execute:
xl_WksheetAC05_52712.Range("C2").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(-2,0 ).Value = ActiveCell.Value &
ActiveCell.Offset(0, 1).Value & ActiveCell.Offset(0, 2).Value
ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(0, 5).Value
- ActiveCell.Offset(0, 4).Value
ActiveCell.Offset(1, 0).Select
Loop

Is there a better solution? Thanks in advance.
 
M

mc

Thanks for the help, it worked.

Patrick Molloy said:
WITH xl_WksheetAC05_52712.
WITH .Range(.Range("C2") , .Range("C2").End(xlDown) ).Offset(,-2)
.FormulaR1C1= "=RC3 & RC4 & RC5"
END WITH
WITH .Range(.Range("C2") , .Range("C2").End(xlDown) ).Offset(,-1)
.FormulaR1C1= "=RC8"
END WITH
END WITH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top