PDA

View Full Version : add 4 formula to current macro



Pete
12-10-2009, 06:06 AM
Hi

I want to add the following conditions to the current macro. In the same format as shown in the VBA code.

All formaula to start at row 6

1. columns F+H+K = M

2. columns G+H+I = N

3. columns O = M - N

4. columns P = (N/M)-1


Sub Edit_Columns_2()
Application.ScreenUpdating = False
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "d").End(xlUp).Row
Range("k6:k" & lngLastRow).Value = Evaluate("=j6:j" & lngLastRow & "*c6:c" & lngLastRow)
Range("l6:l" & lngLastRow).Value = Range("d6:d" & lngLastRow).Value


Application.ScreenUpdating = True
End Sub

xld
12-10-2009, 07:18 AM
Sub Edit_Columns_2()
Application.ScreenUpdating = False
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "d").End(xlUp).Row
Range("k6:k" & lngLastRow).Value = Evaluate("=j6:j" & lngLastRow & "*c6:c" & lngLastRow)
Range("l6:l" & lngLastRow).Value = Range("d6:d" & lngLastRow).Value
Range("M6:M" & lngLastRow).Formula = "=F6+H6+K6"
Range("N6:N" & lngLastRow).Formula = "=G6+H6+I6"
Range("O6:O" & lngLastRow).Formula = "=M6-N6"
Range("P6:P" & lngLastRow).Formula = "=N6/M6-1"

Application.ScreenUpdating = True
End Sub

p45cal
12-10-2009, 08:00 AM
You haven't made it clear whether you want to have formulae in columns M to P or values. If you want to have values you could add a line to xld's code:
Range("M6:P" & lngLastRow).Value = Range("M6:P" & lngLastRow).Value
directly before the
Application.ScreenUpdating = True
line.

If you want to keep to the same vein (using Evaluate throughout) then perhaps:
Sub Edit_Columns_2()
Application.ScreenUpdating = False
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "d").End(xlUp).Row
Range("k6:k" & lngLastRow).Value = Evaluate("=j6:j" & lngLastRow & "*c6:c" & lngLastRow)
Range("l6:l" & lngLastRow).Value = Range("d6:d" & lngLastRow).Value
Range("M6:M" & lngLastRow).Value = Evaluate("=F6:F" & lngLastRow & " + H6:H" & lngLastRow & " + K6:K" & lngLastRow)
Range("N6:N" & lngLastRow).Value = Evaluate("=G6:G" & lngLastRow & " + H6:H" & lngLastRow & " + I6:I" & lngLastRow)
Range("O6:O" & lngLastRow).Value = Evaluate("=M6:M" & lngLastRow & " - N6:N" & lngLastRow)
Range("P6:P" & lngLastRow).Value = Evaluate("=N6:N" & lngLastRow & " / M6:M" & lngLastRow & "-1")
Application.ScreenUpdating = True
End Sub