使用VBscript在Excel表上设置MINUS操作 [英] set MINUS operation using VBscript on excel sheets
本文介绍了使用VBscript在Excel表上设置MINUS操作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以使用vbscript在Excel工作表上实现Set操作?说A - A'= A1
Is it possible to implement Set operation on sheets of Excel using vbscript? Say A - A'=A1
假设我有一个矩阵A,如下所示:
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
从上面我得到了下面的矩阵A':
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
现在我可以使用A - A获得输出矩阵'
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
推荐答案
编辑:将VBA解决方案转换为VBS解决方案
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解决方案
' 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
这篇关于使用VBscript在Excel表上设置MINUS操作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文