Hello Abdul
You can choose to make it compulsory to define all variables. This means you would then be forced to Dim all variables
If you choose this option, then sometimes you will see such simple problems immediately when you try to run your macro
In your case, Answer was seen by VBA as a variable that was not declared explicitly, ( - I mean it was not Dimed like Dim Answer As …… )
If, in your original macro, you had Option Explicit at the top of your code module, then , when you tried to run your macro, you would have been warned by VBA of your small error:
http://i.imgur.com/yn4q13F.jpg

It is just personal preference. You can choose. Most people prefer to include Option Explicit, because it is helpful to see small errors
Alan
Code:
Option Explicit ' I choose to define all my variables explicitly
Sub TestPaper()
Dim FoundCell As Range
Dim FindWhat As String
FindWhat = Worksheets("Test").Range("D3").Value
Set FoundCell = Worksheets("Result").Range("C:C").Find(What:=FindWhat, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then
MsgBox "Register Number " & FindWhat & " is already submitted Test at " & Sheets("Test").Range("J6").Value & ". It can't be edited or modified. You have already secured " & Sheets("Test").Range("J5").Value & " marks."
Exit Sub
End If
Dim x As Long
Dim y As Worksheet
Set y = Sheets("Result")
Dim Answer2 As VbMsgBoxResult
Answer2 = MsgBox("Do you want to save your Result, If once submitted can't be edited", vbYesNo + vbQuestion + vbDefaultButton2, "Save Result")
If Answer = vbYes Then
Else
x = Sheets("Result").Range("B" & Rows.Count).End(xlUp).Row
With y
Sheets("Test").Range("D2:D4").Copy
.Range("B" & x + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
.Cells(x + 1, 6).Value = Sheets("Test").Range("J2").Value
.Cells(x + 1, 5).Value = Sheets("Test").Range("J4").Value
Sheets("Test").Range("L4:AZ4").Copy
.Range("G" & x + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Test").Range("L5:AZ5").Copy
.Range("Q" & x + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
With Sheets("Result").Range("A2:Z100" & x)
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
With Sheets("Result").Range("A3:A" & x + 1)
.Formula = "=Row() - 2"
.Value = .Value
With Sheets("Result").Range("A2:Z" & x + 1)
.BorderAround xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
End With
End With
End With
MsgBox "Your Result is submitted successfully. You have secured Total Marks is " & Sheets("Test").Range("J4").Value
ActiveWorkbook.Save
'MsgBox "Your Data not saved, you can make correction if you want then submit again"
Exit Sub
End If
End Sub
Bookmarks