使用VBScript缩小到excel列 [英] Subtraction between to excel column using VBScript

问题描述

假设我有以下excel表，我需要找到它们之间的区别，结果需要放回到另一个列：

  ColA ColB ColC 
 
 9/3/2012 8:31:59 AM 09/17/2012 6:45:56 PM结果
 9/4/2012 8:31： 59 AM 10/17/2012 6:45:56 PM结果
  

我使用循环和行-By-Row技术寻找一种方法，如果可以直接通过列级减法完成。说ColB-ColA - > ColC。整个操作应该一次执行。结果应该是 hh：mm：ss 。

代码

  IntRow4 = 2 
尽管objSheet4。细胞（IntRow4,1）.Value< 
 
如果objSheet4.Cells（IntRow4,9）.Value<> Open然后
 
 Date1 = objSheet4.Cells（IntRow4,7）.Value 
 Date2 = objSheet4.Cells（IntRow4,8）.Value 
 objSheet4.Cells（IntRow4， 11）= TimeSpan（Date1，Date2）
 
如果
 
 IntRow4 = IntRow4 + 1 
循环
  pre> 
 
 
  更新  
 code> ColA1 ColB1 ColC1 ColA2 ColB2 ColC2 ... Colan ColBN ColCN TotaltimeDurtion 
日期日期11:25:20日期日期10:25:00日期日期11:25:20？ 
  
  这里我只显示一行，但可以有多个或N个我需要做的是，我想添加时间持续时间，并将它们放在最后一个列TotaltimeDurtion。但是最后一列不能被修复。并且每行的所有列不应该是必需的值，但是所有这些都不会是空的。我们也在列级别中执行此操作。持续时间为hh：mm：ss格式或根据您的指令[h]：mm：ss。 TotaltimeDurtion<  -  ColC1 + ColC2 + ... + ColCN。  
解决方案
对象，我可以一次在一个范围内的所有单元格上设置公式。
  range（C1：C10）。 == B1-A1
  
它还将根据绝对的正常复制流程来调整公式寻址。
 
 
 
 eg以上例子，C10将为 = B10-A10 。如果我把公式设为= B1- $ A $ 1，那么C10将是 = B10- $ A $ 1  
 
Suppose I have below excel sheet,And I need to find the difference between them and result need to put back to another column:
      ColA                      ColB                    ColC

9/3/2012  8:31:59 AM      09/17/2012  6:45:56 PM        Result
9/4/2012  8:31:59 AM      10/17/2012  6:45:56 PM        Result
I did it using Loop and Row-By-Row technique. Looking for a way if it can be done directly by column level subtraction. Say ColB-ColA - > ColC. The whole operation should be performed at a time.Result should come "hh:mm:ss".

CODE
  IntRow4=2
  Do While objSheet4.Cells(IntRow4,1).Value <> ""

    If objSheet4.Cells(IntRow4,9).Value <> "Open" Then

       Date1=objSheet4.Cells(IntRow4,7).Value
       Date2=objSheet4.Cells(IntRow4,8).Value
       objSheet4.Cells(IntRow4,11)=TimeSpan(Date1,Date2)

   End If

  IntRow4=IntRow4+1
  Loop
Update
      ColA1    ColB1      ColC1       ColA2    ColB2     ColC2   .....   ColAN    ColBN    ColCN    TotaltimeDurtion
      Date     Date     11:25:20      Date     Date     10:25:00          Date    Date     11:25:20      ?
here i have shown only one row,But there can be multiple or N number of rows.What I need to do is,I want to add the time durations and put them to the last colum "TotaltimeDurtion".But the last column can not be fixed.And all the columns for each row shouldn't required values,but all never will be empty.Can we also do this also in column level.here the duration is hh:mm:ss format or as per your instruction [h]:mm:ss. TotaltimeDurtion <- ColC1  + ColC2 + ...+ ColCN.
解决方案
using the range object, I can set the formula on all the cells within a range at once
range("C1:C10").Formula="=B1-A1"
It will also adjust the formula based on the normal copying riles for absolute addressing.

e.g. with the above example, C10 will be =B10-A10. If I had put the formula as "=B1-$A$1" then C10 would have been =B10-$A$1

                        
这篇关于使用VBScript缩小到excel列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！