Excel VBA formula

syoun02 · Apr 19th, 2002, 10:00 AM

I am just learning VBA for excel. I have a formula that I am having repeat numerous times. In this formula I am subtracting 2 cells. How do I preserve the formula while allowing it to change to the next row? If you look below, I am asking how to change "b5-C5" to "b6-C6" and so on as the code repeats.

Selection.Offset(0, 2).Formula = "=B5-C5"

Thanks!

Mark O'Brien · Apr 19th, 2002, 10:47 AM

Use something like:



Selection.AutoFill Destination:=Range("A1:A14"), Type:=xlFillDefault

Where "A1:A14" is your target range and "A1" is the selected cell to copy from.

HTH

Al Chara · Apr 19th, 2002, 10:47 AM

Do you want something like this:

For i = 5 To 6
Selection.Offset(i - 5, 2).Formula = "=" & Cells(i, 2).Address(rowabsolute:=False, columnabsolute:=False) _
& "-" & Cells(i, 3).Address(rowabsolute:=False, columnabsolute:=False)
Next i

trauco · Apr 19th, 2002, 11:53 AM

I have the same problem, but it is in a search of a range I specify (a2:a10) and that it looks (search) for the content of a cell specifies (b2) and it replaces by the content of a cell specifies (c2).
¿How I make to change the values of the cells (b2, c2) to (b3, c3) to (b4,c4)etc ?
please help
thanks
Pedro

Apr 19th, 2002, 10:47 AM	#2
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Use something like: Selection.AutoFill Destination:=Range("A1:A14"), Type:=xlFillDefault Where "A1:A14" is your target range and "A1" is the selected cell to copy from. HTH __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Apr 19th, 2002, 10:47 AM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Do you want something like this: For i = 5 To 6 Selection.Offset(i - 5, 2).Formula = "=" & Cells(i, 2).Address(rowabsolute:=False, columnabsolute:=False) _ & "-" & Cells(i, 3).Address(rowabsolute:=False, columnabsolute:=False) Next i __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 10:00 AM	#1
syoun02 New Member Join Date: Apr 2002 Posts: 23	I am just learning VBA for excel. I have a formula that I am having repeat numerous times. In this formula I am subtracting 2 cells. How do I preserve the formula while allowing it to change to the next row? If you look below, I am asking how to change "b5-C5" to "b6-C6" and so on as the code repeats. Selection.Offset(0, 2).Formula = "=B5-C5" Thanks!

Apr 19th, 2002, 11:53 AM	#4
trauco New Member Join Date: Apr 2002 Location: Chile Posts: 3	I have the same problem, but it is in a search of a range I specify (a2:a10) and that it looks (search) for the content of a cell specifies (b2) and it replaces by the content of a cell specifies (c2). ¿How I make to change the values of the cells (b2, c2) to (b3, c3) to (b4,c4)etc ? please help thanks Pedro