VBA for Excel 2007 example-set and copy formula


VBA code to set and copy formula

In this VBA example, you will learn how to set the cells formula and copy the formula to other cells in Excel worksheet. Suppose that you are working with a score sheet of 1000 students. You may want the total score and the message "Fail" or "Pass" will be generated automatically when each record of a student entered in the row. How can you do? The VBA code below is solved the problem. 


Score sheet
ID Name Attendance Assignment Mid-term Final Total Other
S001 Voha Chan 7 8 9 23 47 Fail
S002 Sam Sophal 13 13 8 45 79 Pass
S003 Sok Vibol 16 10 9 34 69 Pass
S004 Kea Meng 17 9 11 28 65 Pass
S005 Dalys Chan 18 13 12 38 81 Pass
S006 Makara Sok 20 11 11 43 85 Pass
S007 Kheam Lida 13 10 9 28 60 Pass
S008 Kheam Lida 12 11 9 29 61 Pass
S009 Virak Chan 11 14 13 40 78 Pass
 
 To run this VBA example code, you can copy and paste the VBA code below in to the block of Worksheet_SelectionChange() procedure. To open this block, double-click the current worksheet that contains your data, then in the objects dropdown list select WorkSheet and in events list select SelectionChange.


VBA code for this example:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
   Dim i As Integer
    Dim f, p As String
   
    f = Chr(34) & "Fail" & Chr(34) _
    ' Make sure Fail is in double quotes
    p = Chr(34) & "Pass" & Chr(34) _
    ' Make sure Pass is in double quotes _
 
    'Double quotes are needed when using if functions in cells to get the text results
   
    i = 3 'Start from row 3
      
    While Range("A" & i) <> ""
   
        Worksheets("Sheet5").Range("G" & i).Formula _
        = "=$C$" & i & "+$D$" & i & "+$E$" & i & "+$F$" & i ' Add scores
        Worksheets("Sheet5").Range("H" & i).Formula _
        = "=if($G$" & i & "<50," & f & "," & p & " )" ' Display Fail or Pass accordingly
        i = i + 1
 
    Wend
   
End Sub

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.