根据条件在多列中填充和减少单元格

本文介绍了根据条件在多列中填充和减少单元格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有以下列和值： 开始时间其他值名字姓氏其他信息 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 目标是填写列C& D ，直到下一个以 -A 和 -B 的表格中的下一个值在后面的标题中。 但是 如您在示例数据中看到的，我希望这个填写会考虑第一列中的代码。 例如：在 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 info5041* value1 info15041* value2 firstname1 lastname1 info213089 value3 info316130 value4 info419739 value5 info526300 value6 info626391* value7 firstname2 lastname2 info727878 value8 info827878 value9 info928234 value10 info1028738 value11 info1129854 value12 info1263110 value13 info1363189* value14 firstname3 lastname3 info1464335 value15 info1565423 value16 info1672089* value17 info1772089* value18 firstname4 lastname4 info1873495 value19 info1973495 value20 info2074330 value21 info2174877 value22 info2276710 value23 info2382599* value24 info2482599* value25 firstname5 lastname5 info2586712* value26 info2698712* value27 firstname6 lastname6 info2798725 value28 info28100605 value29 info29100605 value30 info30100954 value31 info31I expect this:Begin Time Other values First Name Last Name other info5041 value1 firstname1 lastname1 info15041 value2 firstname1 lastname1 info213089 value3 firstname1 lastname1 info316130 value4 firstname1 lastname1 info419739 value5 firstname1 lastname1 info526300 value6 firstname1 lastname1 info626391 value7 firstname2 lastname2 info727878 value8 firstname2 lastname2 info827878 value9 firstname2 lastname2 info928234 value10 firstname2 lastname2 info1028738 value11 firstname2 lastname2 info1129854 value12 firstname2 lastname2 info1263110 value13 firstname2 lastname2 info1363189 value14 firstname3 lastname3 info1464335 value15 firstname3 lastname3 info1565423 value16 firstname3 lastname3 info1672089 value17 firstname4 lastname4 info1772089 value18 firstname4 lastname4 info1873495 value19 firstname4 lastname4 info1973495 value20 firstname4 lastname4 info2074330 value21 firstname4 lastname4 info2174877 value22 firstname4 lastname4 info2276710 value23 firstname4 lastname4 info2382599 value24 firstname5 lastname5 info2482599 value25 firstname5 lastname5 info2586712 value26 firstname6 lastname6 info2698712 value27 firstname6 lastname6 info2798725 value28 firstname6 lastname6 info28100605 value29 firstname6 lastname6 info29100605 value30 firstname6 lastname6 info30100954 value31 firstname6 lastname6 info31I am using the following code thanks to @Jeeped's answerSub FillColBlanksSpecial()Dim wks As WorksheetDim rng As RangeDim rng2 As RangeDim LastRow As LongDim col As LongDim lRows As LongDim lLimit As LongDim lCount As LongOn Error Resume NextlRows = 2lLimit = 1000Set 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 wksEnd SubThe 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.Butas 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 wksEnd SubThe 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. 这篇关于根据条件在多列中填充和减少单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！