Could anyone can help to tell me what is wrong with the following macro? It
doesn't perform when I execute it. What I want is place the formula in one
cell, then past it to rest of the cells within range. So the output of
calculation changes because of cell "C15" change to "C16" and so on.
Tks

Range("N15").Select
Range("N15").Formula =
"=(C15-VLOOKUP(LEFT(A15,3),$A$2:$J$12,5)-VLOOKUP(LEFT(A15,3),$A$2:$J$12,6))/VLOOKUP(A15,$A$242:$F$352,6)*VLOOKUP(LEFT(A15,3),$ A$2:$J$12,10)+VLOOKUP(LEFT(A15,3),$A$2:$J$12,3)"
Selection.Copy
Range("N16").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

a couple things. your code, if it worked, would fill column N to row 65536 in
excel 2007. I don't think this is what you want.
change the sheet name in my code to match yours.
watch out for wordwrap, the underscores show where the line breaks
change the range to filldown
you'll have to determine if the formula is correct.

Sub test()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
With ws.Range("N15")
.Formula = _
"=(C15-VLOOKUP(LEFT(A15,3),$A$2:$J$12,5)-VLOOKUP(LEFT(A15,3),$A$2:$J$12,6))/"
& _
"VLOOKUP(A15,$A$242:$F$352,6)*VLOOKUP(LEFT(A15,3), $A$2:$J$12,10)"
& _
"+VLOOKUP(LEFT(A15,3),$A$2:$J$12,3)"
End With
ws.Range("N15:N25").FillDown
End Sub


--


Gary K



"Seeker" wrote in message
...
Could anyone can help to tell me what is wrong with the following macro? It
doesn't perform when I execute it. What I want is place the formula in one
cell, then past it to rest of the cells within range. So the output of
calculation changes because of cell "C15" change to "C16" and so on.
Tks

Range("N15").Select
Range("N15").Formula =
"=(C15-VLOOKUP(LEFT(A15,3),$A$2:$J$12,5)-VLOOKUP(LEFT(A15,3),$A$2:$J$12,6))/VLOOKUP(A15,$A$242:$F$352,6)*VLOOKUP(LEFT(A15,3),$ A$2:$J$12,10)+VLOOKUP(LEFT(A15,3),$A$2:$J$12,3)"
Selection.Copy
Range("N16").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

The code will work. It pastes the formula but whether it is calculating the
correct answer is another matter because can't test without all the data.

Need to establish a couple of things.

The following line relies on there being at least one cell below N16 that is
not blank. If they are all blank then it will copy the formula to the bottom
of the worksheet.

Range(Selection, Selection.End(xlDown)).Select

Also, if the code is in an event then perhaps events are turned off. Events
can get inadvertantly turned off when code is interrupted due to code errors
etc and then they remain off until turned back on by the user or you close
and re-start Excel. (This problem occurs when Application.EnableEvents =
False is used at the start of an event to prevent recursive calls to the sub
but it needs to be turned back on at the end of the sub. However, if code is
interrupted then it never gets turned back on until the user executes some
code to re-enable events.)

To turn events back on use the following sub. It can be run from within the
VBA editor.

Sub ReEnableEvents()
Application.EnableEvents = True
End Sub



--
Regards,

OssieMac


"Seeker" wrote:

Could anyone can help to tell me what is wrong with the following macro? It
doesn't perform when I execute it. What I want is place the formula in one
cell, then past it to rest of the cells within range. So the output of
calculation changes because of cell "C15" change to "C16" and so on.
Tks

Range("N15").Select
Range("N15").Formula =
"=(C15-VLOOKUP(LEFT(A15,3),$A$2:$J$12,5)-VLOOKUP(LEFT(A15,3),$A$2:$J$12,6))/VLOOKUP(A15,$A$242:$F$352,6)*VLOOKUP(LEFT(A15,3),$ A$2:$J$12,10)+VLOOKUP(LEFT(A15,3),$A$2:$J$12,3)"
Selection.Copy
Range("N16").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

You need to use another column instead of N to find the last row of your
data. It the code below I used column A

LastRow = Range("A16").End(xlDown).Row
Range("N15").Formula = _
"=(C15-VLOOKUP(LEFT(A15,3),$A$2:$J$12,5)" & _
"-VLOOKUP(LEFT(A15,3),$A$2:$J$12,6))/" & _
"VLOOKUP(A15,$A$242:$F$352,6)*" & _
"VLOOKUP(LEFT(A15,3),$A$2:$J$12,10)+" & _
"VLOOKUP(LEFT(A15,3),$A$2:$J$12,3)"
Range("N15").Copy _
destination:=Range("N16:N" & LastRow)


"Seeker" wrote:

Could anyone can help to tell me what is wrong with the following macro? It
doesn't perform when I execute it. What I want is place the formula in one
cell, then past it to rest of the cells within range. So the output of
calculation changes because of cell "C15" change to "C16" and so on.
Tks

Range("N15").Select
Range("N15").Formula =
"=(C15-VLOOKUP(LEFT(A15,3),$A$2:$J$12,5)-VLOOKUP(LEFT(A15,3),$A$2:$J$12,6))/VLOOKUP(A15,$A$242:$F$352,6)*VLOOKUP(LEFT(A15,3),$ A$2:$J$12,10)+VLOOKUP(LEFT(A15,3),$A$2:$J$12,3)"
Selection.Copy
Range("N16").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

OssieMac,
Tks for your reply. Some of the cells currently is pending for future data
input purpose, so not all cells are filled with data now. Thus, output of
some cells with this formula currently will have #NAME? result, is that what
you mean "when code is interrupted due to code errors"? If so, is there a way
to solve it?
My macro sheet start with "Sub activate()" & end with "End Sub", so where
should I place the
Sub ReEnableEvents()
Application.EnableEvents = True
End Sub

"OssieMac" wrote:

The code will work. It pastes the formula but whether it is calculating the
correct answer is another matter because can't test without all the data.

Need to establish a couple of things.

The following line relies on there being at least one cell below N16 that is
not blank. If they are all blank then it will copy the formula to the bottom
of the worksheet.

Range(Selection, Selection.End(xlDown)).Select

Also, if the code is in an event then perhaps events are turned off. Events
can get inadvertantly turned off when code is interrupted due to code errors
etc and then they remain off until turned back on by the user or you close
and re-start Excel. (This problem occurs when Application.EnableEvents =
False is used at the start of an event to prevent recursive calls to the sub
but it needs to be turned back on at the end of the sub. However, if code is
interrupted then it never gets turned back on until the user executes some
code to re-enable events.)

To turn events back on use the following sub. It can be run from within the
VBA editor.

Sub ReEnableEvents()
Application.EnableEvents = True
End Sub



--
Regards,

OssieMac


"Seeker" wrote:

Could anyone can help to tell me what is wrong with the following macro? It
doesn't perform when I execute it. What I want is place the formula in one
cell, then past it to rest of the cells within range. So the output of
calculation changes because of cell "C15" change to "C16" and so on.
Tks

Range("N15").Select
Range("N15").Formula =
"=(C15-VLOOKUP(LEFT(A15,3),$A$2:$J$12,5)-VLOOKUP(LEFT(A15,3),$A$2:$J$12,6))/VLOOKUP(A15,$A$242:$F$352,6)*VLOOKUP(LEFT(A15,3),$ A$2:$J$12,10)+VLOOKUP(LEFT(A15,3),$A$2:$J$12,3)"
Selection.Copy
Range("N16").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste