根据Excel中的两行或更多行获取行上的值 [英] Get values on a row based on two or more rows in Excel

问题描述

我有一个DEMAND行和COLLECTION行中的值的值，现在我想要BALANCE = DEMAND-COLLECTION，有两次收集一个条目，所以根据收集的发生，应该出现余额。你可以让我知道这个宏代码。

I have values in DEMAND row and values in the COLLECTION row, now I want BALANCE = DEMAND-COLLECTION, there are two times collection for an entry so according to the occurrence of collection the balance should arise. Can you please let me know the macro code for that.

我有DEMAND值D1：S1收集来自D2：S2的值，余额应该在下一行

I have DEMAND values D1:S1 COLLECTION values from D2:S2 and the balance should be there in the next row.

我从
根据特定文本插入行及其出现

我正在使用以下内容代码

I am using the following code

Sub try()
 Dim c As Range
 Dim lRow As Long
 lRow = 1
 Dim lRowLast As Long
 Dim bFound As Boolean
 With ActiveSheet
  lRowLast = .Cells(.Rows.Count, 1).End(xlUp).Row
  Do
   Set c = .Range("A" & lRow)
   If c.Value Like "*COLLECTION*" Then
    bFound = True
   ElseIf bFound Then
    bFound = False
    If c.Value <> "BALANCE" Then
     c.EntireRow.Insert
     lRowLast = lRowLast + 1
     c.Offset(-1, 0).Value = "BALANCE"
     c.Offset(-1, 0).Font.Color = RGB(0, 0, 0)
    End If
   End If
   lRow = lRow + 1
  Loop While lRow <= lRowLast + 1
 End With
End Sub

在宏检查之前IMAGE

宏之后我想要这个检查图片

推荐答案

因此，我将使用 SUMIF 应用于 FormulaR1C1 。优点是我们可以为整行设置公式。

So I would use SUMIF applied with FormulaR1C1 for that. The advantage is that we can set the formula in one step for the whole row.

Sub try()
 Dim c As Range
 Dim lRow As Long
 lRow = 1
 Dim lRowLast As Long
 Dim lRowDiff As Long
 Dim lRowPortion As Long
 lRowPortion = 1
 Dim bFoundCollection As Boolean
 With ActiveSheet
  lRowLast = .Cells(.Rows.Count, 1).End(xlUp).Row
  Do
   Set c = .Range("A" & lRow)
   If c.Value Like "*COLLECTION*" Then
    bFoundCollection = True
   ElseIf bFoundCollection Then
    bFoundCollection = False
    If c.Value <> "BALANCE" Then
     c.EntireRow.Insert
     lRowLast = lRowLast + 1
     Set c = c.Offset(-1, 0)
     c.Value = "BALANCE"
    End If
    If c.Value = "BALANCE" Then
     .Range(c, c.Offset(0, 18)).Font.Color = RGB(0, 0, 0)
     .Range(c, c.Offset(0, 18)).Interior.Color = RGB(200, 200, 200)
     lRowDiff = c.Row - lRowPortion
     .Range(c.Offset(0, 3), c.Offset(0, 18)).FormulaR1C1 = _
      "=SUMIF(R[-" & lRowDiff & "]C1:RC1, ""*DEMAND*"", R[-" & lRowDiff & "]C:RC)" & _
      "-SUMIF(R[-" & lRowDiff & "]C1:RC1, ""*COLLECTION*"", R[-" & lRowDiff & "]C:RC)"
     lRowPortion = c.Row + 1
    End If
   End If
   lRow = lRow + 1
  Loop While lRow <= lRowLast + 1
 End With
End Sub

这篇关于根据Excel中的两行或更多行获取行上的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！