set MINUS operation using VBscript on excel sheets

Question

Is it possible to implement Set operation on sheets of Excel using vbscript? Say A - A'=A1

Suppose i do have a matrix A, as below :

 PID  T1   T1Sdate  T1Assign  T2   T2Sdate  T2Assign   T3   T3Sdate  T3Assign   T4    T4Sdate  T4Assign

 10   A      10/11    Ram      B      2/5     Hari     X     03/03     Peter     L      04/09     Mikel

 20   A      10/11    Kajal    T      7/5     Lisa     X     03/03     Peter     L      04/07     Sila

 25   Y      10/11    Sila     T      7/5     Lisa     X     02/03     Peter     L      17/07     Mikel

From above i got the below matrix A':

 PID  T1   T1Sdate  T1Assign  T2   T2Sdate  T2Assign   T3   T3Sdate  T3Assign   T4    T4Sdate  T4Assign

 10   A      10/11    Ram                              X     03/03     Peter     L      04/09     Mikel

 20   A      10/11    Kajal    T      7/5     Lisa    

 25                                                    X     02/03     Peter     L      17/07     Mikel

Now can i get the output matrix using A - A'

 PID  T1   T1Sdate  T1Assign  T2   T2Sdate  T2Assign   T3   T3Sdate  T3Assign   T4    T4Sdate  T4Assign

 10                            B      2/5     Hari    

 20                                                   X     03/03     Peter     L      04/07        Sila

 25   Y      10/11    Sila     T      7/5     Lisa

You would need to loop through and compare both ranges, and output the difference. — Tim Williams, Dec 13 '12 at 04:34
Yes, you need to compare 2 identical range with same dimension M x N and you need to define "MINUS" logic for each data type (e.g. for numbers,date is just minus) , For String what do you mean? And define the case if 2 type doesn't match--> treat as String? — Larry, Dec 13 '12 at 05:44
Larry thanks for your suggestions here to. I am glad to get more advanced help from you. Can i have a demo code for this from you? :-) — Arup Rakshit, Dec 13 '12 at 06:51
With reference to your [earlier question](http://stackoverflow.com/questions/13837023/date-value-subtraction-producing-wrong-error) write a code, try yourself - then ask for **advance help** ! And when a logic is given to you, use that to help yourself first. — bonCodigo, Dec 13 '12 at 08:32

Larry · Accepted Answer · 2012-12-14T07:42:50.860

EDIT: Converted VBA solution into VBS solution

Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
xlApp.EnableEvents = False
xlApp.ScreenUpdating = False

set xlBook = xlApp.Workbooks.Open("C:\Users\wangCL\Desktop\sample.xlsm")


Dim rangeA
Dim rangeB
Dim rangeC
Set rangeA = xlBook.Worksheets("Sheet1").Range("A1:E1")
Set rangeB = xlBook.Worksheets("Sheet2").Range("A1:E1")
Set rangeC = xlBook.Worksheets("Sheet3").Range("A1:E1")
Dim valueTypeA
Dim valueTypeB
If rangeA.Rows.Count <> rangeB.Rows.Count Or _
rangeA.Columns.Count <> rangeB.Columns.Count Or _
rangeA.Rows.Count <> rangeC.Rows.Count Or _
rangeA.Columns.Count <> rangeC.Columns.Count Then
    MsgBox "Please input 3 ranges with identical size"
End If
rangeC.clearContents
For i = 1 To rangeA.Rows.Count
    For j = 1 To rangeA.Columns.Count
        valueTypeA = TypeName(rangeA.Cells(i, j).Value)
        valueTypeB = TypeName(rangeB.Cells(i, j).Value)
        If valueTypeA = valueTypeB Then
            If valueTypeA = "Integer" Or valueTypeA = "Double" Or valueTypeA = "Long" Or valueTypeA = "Single" Then
                rangeC.Cells(i, j).Value = rangeA.Cells(i, j).Value - rangeB.Cells(i, j).Value ' just numeric minus
            ElseIf valueTypeA = "String" Then
                ' implement your own String comparsion function here
                ' I provide a simple one here
                ' matching the identical string
                If rangeA.Cells(i, j).Value = rangeB.Cells(i, j).Value Then
                    rangeC.Cells(i, j).Value = ""
                Else
                    'handle if the 2 string not match case here
                    rangeC.Cells(i, j).Value = rangeA.Cells(i, j).Value
                End If
            ElseIf valueTypeA = "Date" Then
                 ' implement your own Date comparsion function here
                ' I provide a simple one here
                ' matching the identical date
                If rangeA.Cells(i, j).Value = rangeB.Cells(i, j).Value Then
                    rangeC.Cells(i, j).Value = ""
                Else
                    'handle if the 2 string not match case here
                    rangeC.Cells(i, j).Value = rangeA.Cells(i, j).Value
                End If
            End If
        Else
            ' handle if the 2 types are different
        End If
    Next
Next

xlBook.save
xlApp.Quit
set xlBook = nothing
set xlApp = nothing

msgbox "Done"

VBA solution

' Assume the range are M*N matrix
Sub MinusOperation()
Dim rangeA As Range
Dim rangeB As Range
Dim rangeC As Range ' output range
Set rangeA = ThisWorkbook.Worksheets("Sheet1").Range("A1:E1")
Set rangeB = ThisWorkbook.Worksheets("Sheet2").Range("A1:E1")
Set rangeC = ThisWorkbook.Worksheets("Sheet3").Range("A1:E1")
Dim valueTypeA As String
Dim valueTypeB As String
If rangeA.Rows.Count <> rangeB.Rows.Count Or _
rangeA.Columns.Count <> rangeB.Columns.Count Or _
rangeA.Rows.Count <> rangeC.Rows.Count Or _
rangeA.Columns.Count <> rangeC.Columns.Count Then
    MsgBox "Please input 3 ranges with identical size"
    exit sub
End If

For i = 1 To rangeA.Rows.Count
    For j = 1 To rangeA.Columns.Count
        valueTypeA = TypeName(rangeA.Cells(i, j).Value)
        valueTypeB = TypeName(rangeB.Cells(i, j).Value)
        If valueTypeA = valueTypeB Then
            If valueTypeA = "Integer" Or valueTypeA = "Double" Or valueTypeA = "Long" Or valueTypeA = "Single" Then
                rangeC.Cells(i, j).Value = rangeA.Cells(i, j).Value - rangeB.Cells(i, j).Value ' just numeric minus
            ElseIf valueTypeA = "String" Then
                ' implement your own String comparsion function here
                ' I provide a simple one here
                ' matching the identical string
                If rangeA.Cells(i, j).Value = rangeB.Cells(i, j).Value Then
                    rangeC.Cells(i, j).Value = ""
                Else
                    'handle if the 2 string not match case here
                    rangeC.Cells(i, j).Value = rangeA.Cells(i, j).Value
                End If
            ElseIf valueTypeA = "Date" Then
                 ' implement your own Date comparsion function here
                ' I provide a simple one here
                ' matching the identical date
                If rangeA.Cells(i, j).Value = rangeB.Cells(i, j).Value Then
                    rangeC.Cells(i, j).Value = ""
                Else
                    'handle if the 2 string not match case here
                    rangeC.Cells(i, j).Value = rangeA.Cells(i, j).Value
                End If
            End If
        Else
            ' handle if the 2 types are different
        End If
    Next j
Next i
End Sub

Can I have a VBScript?I know only this language at all. And one more information i would like to share with you that in my excel sheet there are more columns before T1.here PID stands for all those,because in both A' and A-A' sheet those column should need to be present and they are same. Only the task information are different in both the sheet.Tasks which A' contain A-A' will never,same as output sheet i have shown in my case description. — Arup Rakshit, Dec 14 '12 at 07:44
Hi, My first code is a VBScript, and you can change the range / sheet freely to meet your own requirement — Larry, Dec 14 '12 at 07:47

set MINUS operation using VBscript on excel sheets

1 Answers1

Linked