Interesting Excel VBA Example

During a classroom session one of the students asked this question:
What formula can I use in MS Excel to do the following

For example in cell B2 I have a value 12
I will input value 6 in cell C2
Cell B2 will become 6 (Value in Cell B2 – Value in Cell C2)
Cell C2 should become blank
If I again put a value 5 in cell C2, the same process should happen.
Cell B2 will become 1 (6 – 5)
Cell C2 should become blank

The problem cannot be solved with an Excel formula or function. You’ll have to work with Excel VBA.The complete code (macros) is given below followed by a video:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsNumeric(Range(“C2”).Value) Then
Range(“B2”).Value = Range(“B2”).Value – Range(“C2”).Value
Range(“C2”).Value = “”
End If
End Sub

Watch the training video below to learn how to solve this problem using Excel VBA:


One thought on “Interesting Excel VBA Example

  1. Ramesh

    Hi Sir,

    I have a folder which having No. of excel files and which i need to consolidate each and every file into another excel file , here there a challenge i wanted to copy only few columns (which are not in order for all the files) based on the headers. so i wanted copy the entire column(until data is available in a column) based on my requirement, also if the data is not available in a excel file it should close and it will go for another file to open which are available in the folder.

    please provide the vba code for above concern.

    Regards,
    Ramesh

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *