View Full Version : add 4 formula to current macro
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
Bob Phillips
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.