根据条件在多列中填充和减少单元格 [英] Fill up and down cells in multiple columns based on a condition

问题描述

我有以下列和值：

 开始时间其他值名字姓氏其他信息
 5041 * value1 info1 
 5041 * value2 firstname1 lastname1 info2 
 13089 value3 info3 
 16130 value4 info4 
 19739 value5 info5 
 26300 value6 info6 
 26391 * value7 firstname2 lastname2 info7 
 27878 value8 info8 
 27878 value9 info9 
 28234 value10 info10 
 28738 value11 info11 
 29854 value12 info12 
 63110 value13 info13 
 63189 * value14 firstname3 lastname3 info14 
 64335 value15 info15 
 65423 value16 info16 
 72089 * value17 info17 
 72089 * value18 firstname4 lastname4 info18 
 73495 value19 info19 
 73495 value20 info20 
 74330 value21 info21 
 74877 value22 info22 
 76710 value23 info23 
 82599 * value24 info24 
 82599 * value25 firstname5 lastname5 info25 
 86712 * value26 info26 
 98712 * value27 firstname6 lastname6 info27 
 98725 value28 info28 
 100605 value29 info29 
 1006 05 value30 info30 
 100954 value31 info31 
  

我希望这样：

 开始时间其他值名字姓氏其他信息
 5041 value1 firstname1 lastname1 info1 
 5041 value2 firstname1 lastname1 info2 
 13089 value3 firstname1 lastname1 info3 
 16130 value4 firstname1 lastname1 info4 
 19739 value5 firstname1 lastname1 info5 
 26300 value6 firstname1 lastname1 info6 
 26391 value7 firstname2 lastname2 info7 
 27878 value8 firstname2 lastname2 info8 
 27878 value9 firstname2 lastname2 info9 
 28234 value10 firstname2 lastname2 info10 
 28738 value11 firstname2 lastname2 info11 
 29854 val ue12 firstname2 lastname2 info12 
 63110 value13 firstname2 lastname2 info13 
 63189 value14 firstname3 lastname3 info14 
 64335 value15 firstname3 lastname3 info15 
 65423 value16 firstname3 lastname3 info16 
 72089 value17 firstname4 lastname4 info17 
 72089 value18 firstname4 lastname4 info18 
 73495 value19 firstname4 lastname4 info19 
 73495 value20 firstname4 lastname4 info20 
 74330 value21 firstname4 lastname4 info21 
 74877 value22 firstname4 lastname4 info22 
 76710 value23 firstname4 lastname4 info23 
 82599 value24 firstname5 lastname5 info24 
 82599 value25 firstname5 lastname5 info25 
 86712 value26 firstname6 lastname6 info26 
 98712 value27 firstname6 lastname6 inf o27 
 98725 value28 firstname6 lastname6 info28 
 100605 value29 firstname6 lastname6 info29 
 100605 value30 firstname6 lastname6 info30 
 100954 value31 firstname6 lastname6 info31 
  pre> 
 
 
 
 
 
 
 
我使用以下代码感谢@Jeeped's answer  
  Sub FillColBlanksSpecial（）
 
 Dim wks As Worksheet 
 Dim rng As Range 
 Dim rng2 As Range 
 Dim LastRow As Long 
 Dim col As Long 
 Dim lRows As Long 
 Dim lLimit As Long 
 
 Dim lCount As Long 
 On Error Resume Next 
 
 lRows = 2 
 lLimit = 1000 
 
设置wks = ActiveSheet 
对于工作表中的每个wks 
如果右（wks.Name，2）=-A或右（wks.Name，2）=-B然后
用wks 
带.Cells（1，1）.CurrentRegion 
使用.Columns（C：D）
如果CBool​​（Application.CountBlank（.Cells））然后
 .SpecialCells（xlCellTypeBlanks）.FormulaR1C1 == r [-1] c
结束如果
结束
'取消注释下一行如果您想要公式恢复为
 .Cells = .Cells.Value 
结束
结束
结束如果
下一个wks 
结束Sub 
  
 < br> 
目标是填写列C& D ，直到下一个以 -A 和 -B 的表格中的下一个值在后面的标题中。
 
 
 < h2>但是 
 
 
如您在示例数据中看到的，我希望这个填写会考虑第一列中的代码。 
例如：在 A2 &  A3 代码 5041 ，所以我想用填充第2行  C3 和 D3 （即 firstname1 &  lastname1 ），然后填写相同的值，直到下一次出现值列C& D （即 firstname2 &  lastname2 ）或有时候在下一次发生之前的一行，因为该行在列A中与之相同的代码在本身之后。而后者的例子就是填写一下firstname3 &  lastname3 ，直到出现 firstname4 &  lastname4 ，因为该行在列A 中共享相同的代码，其中包含以下 firstname4 &  lastname4 将由 firstname4 & 
 
 
 
 如何修改此vba代码以适应这种情况？ 
 
 
 
 这里是excel具有较长示例数据的工作表。该工作簿包含两个表格：我有什么&     
 
 
 
  PS 除了第一列中的代码之外的星号除外视觉可达性的目的;否则它们不会出现在原始数据表中。
解决方案
你将不得不填写一个公式一点点复杂此外，由于该公式在默认情况下上下查找，所以在继续下一个空白单元格之前，必须将公式 解析为其返回值，以便可以在将来的查找中使用该值没有引用循环引用。
  Sub FillColBlanksSpecial2（）
 
 Dim wks As Worksheet 
 Dim rng As Range 
 Dim rng2 As Range 
 Dim blnk As Range 
 Dim LastRow As Long 
 Dim col As Long 
 Dim lRows As Long 
 Dim lLimit As Long 
 
 Dim lCount As Long 
 On Error Resume Next 
 
 lRows = 2 
 lLimit = 1000 
 
设置wks = ActiveSheet 
对于每个wks在工作表
如果右（wks.Name，2）=-A或右（wks.Name，2）=-B然后
与wks 
带.Cells（1，1）.CurrentRegion 
带.Columns（C：D）
如果CBool​​（Application.C ountBlank（.Cells））然后
对于每个blnk In .SpecialCells（xlCellTypeBlanks）
 blnk.FormulaR1C1 == if（countifs（r1c1：r [-1] c1，rc1，r1c：r [ - 1] c， - ），索引（r1c：r [-1] c，match（rc1，r1c1：r [-1] c1,0）），if（countifs（r [1] c1：r9999c1，rc1，r [1] c：r9999c， - ），index（r [1] c：r9999c，match（rc1，r [1] c1：r9999c1,0） r [-1] c））
 blnk.Value = blnk.Value 
 Next blnk 
 End If 
 End With 
 End With 
 End With 
结束如果
下一个wks 
 End Sub 
  
新的公式在xlR1C1和xlA1（从C2看到）是，
  = IF（COUNTIFS（R1C1：R [-1] RC1，R1C：R [-1] C， - ），INDEX（R1C：R [-1] C，MATCH（RC1，R1C1：R [-1] C1,0）），IF （R [1] C1：R9999C1，RC1，R [1] C：R9999C， - ），INDEX（R [1] C：R9999C，MATCH（RC1，R [1] C1：R999 （$ A $ 1：$ A1，$ A2，C $ 1：C1，），INDEX（C $ 1） ：C1，MATCH（$ A2，$ A $ 1：$ A1,0）），IF（COUNTIFS（$ A3：$ A $ 9999，$ A2，C3：C $ 9999， - ），INDEX（C3： C $ 9999，MATCH（$ A2，$ A3：$ A $ 9999，0）），C1））
  
 
 
 
 
 
该公式首先查看是否在其上面的列A中有一个非空白名字的值，如果有一个，它接受并移动。
 
 
如果找不到高于该值的值，那么它将在下面找到一个匹配，其中名字不为空。 
 
 
如果两者都没有被发现，它接受它的正上方的值作为默认值。
 
 
 
 
 
如果您遵循该逻辑，您可以看到公式具有可以立即恢复为值，或者后续公式可以将其作为公式找到，并创建一个循环参考。这是一个慢一点，因为它循环通过 xlCellTypeBlanks ，而不是推动公式和值块，但它是彻底的。
 
I have the following columns and values:

Begin Time  Other values    First Name  Last Name   other info
5041*       value1                                  info1
5041*       value2          firstname1  lastname1   info2
13089       value3                                  info3
16130       value4                                  info4
19739       value5                                  info5
26300       value6                                  info6
26391*      value7          firstname2  lastname2   info7
27878       value8                                  info8
27878       value9                                  info9
28234       value10                                 info10
28738       value11                                 info11
29854       value12                                 info12
63110       value13                                 info13
63189*      value14         firstname3  lastname3   info14
64335       value15                                 info15
65423       value16                                 info16
72089*      value17                                 info17
72089*      value18         firstname4  lastname4   info18
73495       value19                                 info19
73495       value20                                 info20
74330       value21                                 info21
74877       value22                                 info22
76710       value23                                 info23
82599*      value24                                 info24
82599*      value25          firstname5 lastname5   info25
86712*      value26                                 info26
98712*      value27          firstname6 lastname6   info27
98725       value28                                 info28
100605      value29                                 info29
100605      value30                                 info30
100954      value31                                 info31



I expect this:
Begin Time  Other values    First Name  Last Name   other info
5041        value1          firstname1  lastname1   info1
5041        value2          firstname1  lastname1   info2
13089       value3          firstname1  lastname1   info3
16130       value4          firstname1  lastname1   info4
19739       value5          firstname1  lastname1   info5
26300       value6          firstname1  lastname1   info6
26391       value7          firstname2  lastname2   info7
27878       value8          firstname2  lastname2   info8
27878       value9          firstname2  lastname2   info9
28234       value10         firstname2  lastname2   info10
28738       value11         firstname2  lastname2   info11
29854       value12         firstname2  lastname2   info12
63110       value13         firstname2  lastname2   info13
63189       value14         firstname3  lastname3   info14
64335       value15         firstname3  lastname3   info15
65423       value16         firstname3  lastname3   info16
72089       value17         firstname4  lastname4   info17
72089       value18         firstname4  lastname4   info18
73495       value19         firstname4  lastname4   info19
73495       value20         firstname4  lastname4   info20
74330       value21         firstname4  lastname4   info21
74877       value22         firstname4  lastname4   info22
76710       value23         firstname4  lastname4   info23
82599       value24         firstname5  lastname5   info24
82599       value25         firstname5  lastname5   info25
86712       value26         firstname6  lastname6   info26
98712       value27         firstname6  lastname6   info27
98725       value28         firstname6  lastname6   info28
100605      value29         firstname6  lastname6   info29
100605      value30         firstname6  lastname6   info30
100954      value31         firstname6  lastname6   info31



I am using the following code thanks to @Jeeped's answer
Sub FillColBlanksSpecial()

Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long

Dim lCount As Long
On Error Resume Next

lRows = 2
lLimit = 1000

Set wks = ActiveSheet
 For Each wks In Worksheets
        If Right(wks.Name, 2) = "-A" Or Right(wks.Name, 2) = "-B" Then
            With wks
                With .Cells(1, 1).CurrentRegion
                    With .Columns("C:D")
                        If CBool(Application.CountBlank(.Cells)) Then
                            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
                        End If
                    End With
                    'un comment the next line if you want the formulas to revert to values only
                    .Cells = .Cells.Value
                End With
            End With
        End If
    Next wks
End Sub


The goal is to fill down the values in the columns C & D until the next value in sheets with -A and -B in suffixed in the sheetnames.

But

as you see in the example data, I want this filling down take into account the codes in the first column.
For example: in A2 & A3 the code is 5041, so I want to fill up the row 2 with the values in C3 and D3 (i.e. firstname1 & lastname1), and then fill down the same values until the next occurrence of values in columns C & D (i.e. firstname2 & lastname2) or sometimes one row before the next occurrence because that one row shares the same code in column A with the one after itself. And example for this latter one is filling down firstname3 & lastname3 until one row before the occurrence of firstname4 & lastname4 because the row shares the same code in column A with the following firstname4 & lastname4 which is going to be filled up by firstname4 & lastname4 in its turn.

How to modify this vba code to accommodate this condition?

Here is the excel worksheet with a longer example data. The workbook contains two sheets: what I have & what I expect.

P.S. the asterisk mark besides the codes in first column are solely for the purpose of the visual accessibility; otherwise they don't appear in the original data sheet.
解决方案
You are going to have to fill in a formula that is a little more complicated. Additionally, since this formula looks both up and down before settling on a default, the formulas must be resolved to their returned values before proceeding to the next blank cell in order that the value can be used in future lookups without causing a circular reference.
Sub FillColBlanksSpecial2()

    Dim wks As Worksheet
    Dim rng As Range
    Dim rng2 As Range
    Dim blnk As Range
    Dim LastRow As Long
    Dim col As Long
    Dim lRows As Long
    Dim lLimit As Long

    Dim lCount As Long
    On Error Resume Next

    lRows = 2
    lLimit = 1000

    Set wks = ActiveSheet
    For Each wks In Worksheets
        If Right(wks.Name, 2) = "-A" Or Right(wks.Name, 2) = "-B" Then
            With wks
                With .Cells(1, 1).CurrentRegion
                    With .Columns("C:D")
                        If CBool(Application.CountBlank(.Cells)) Then
                            For Each blnk In .SpecialCells(xlCellTypeBlanks)
                                blnk.FormulaR1C1 = "=if(countifs(r1c1:r[-1]c1, rc1, r1c:r[-1]c, ""<>""), index(r1c:r[-1]c, match(rc1, r1c1:r[-1]c1, 0)), if(countifs(r[1]c1:r9999c1, rc1, r[1]c:r9999c, ""<>""), index(r[1]c:r9999c, match(rc1, r[1]c1:r9999c1, 0)), r[-1]c))"
                                blnk.Value = blnk.Value
                            Next blnk
                        End If
                    End With
                End With
            End With
        End If
    Next wks
End Sub
The new formula in xlR1C1 and xlA1 (as seen from C2) is,
=IF(COUNTIFS(R1C1:R[-1]C1, RC1, R1C:R[-1]C, "<>"), INDEX(R1C:R[-1]C, MATCH(RC1, R1C1:R[-1]C1, 0)), IF(COUNTIFS(R[1]C1:R9999C1, RC1, R[1]C:R9999C, "<>"), INDEX(R[1]C:R9999C, MATCH(RC1, R[1]C1:R9999C1, 0)), R[-1]C))
=IF(COUNTIFS($A$1:$A1, $A2, C$1:C1, "<>"), INDEX(C$1:C1, MATCH($A2, $A$1:$A1, 0)), IF(COUNTIFS($A3:$A$9999, $A2, C3:C$9999, "<>"), INDEX(C3:C$9999, MATCH($A2, $A3:$A$9999, 0)), C1))



The formula first looks to see if there is a value in column A above it with a non-blank First Name and if there is one, it accepts it and moves on.
If a value above it was not found, it then looks for a match below it where First Name is not blank. If found, it accepts it and moves on.
If neither was found, it accepts the value directly above it as the default.


If you follow that logic, you can see that the formulas have to be reverted to values right away or a subsequent formula could find it as a formula and create a circular reference. This is a little slower as it loops through the xlCellTypeBlanks instead of pushing formulas and values in blocks but it is thorough.

                        
这篇关于根据条件在多列中填充和减少单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！