根据另一列中的值，在多列中填充和关闭单元格

本文介绍了根据另一列中的值，在多列中填充和关闭单元格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有以下列和值： 开始时间其他值名字姓氏其他信息 5041 * value1 info1 5041 * value2 firstname1 lastname1 info2 13089 value3 info3 16130 value4 info4 26391 * value5 info5 26391 * value6 info6 26391 * value7 firstname2 lastname2 info7 27878 value8 info8 27878 value9 info9 28234 value10 info10 63189 * value11 info11 63189 * value12 info12 63189 * 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 98712 * 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 26391 value5 firstname2 lastname2 info5 26391 value6 firstname2 lastname2 info6 26391 value7 firstname2 lastname2 info7 27878 value8 firstname2 lastname2 info8 27878 value9 firstname2 lastname2 info9 28234 value10 firstname2 lastname2 info10 63189 value11 firstname3 lastname3 info11 63189 val ue12 firstname3 lastname3 info12 63189 value13 firstname3 lastname3 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 98712 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 FillColBlanksSpecial2（） Dim wks As Worksheet Dim rng As Range Dim rng2 As范围 Dim blnk As Range Dim LastRow As Long Dim col As Long Dim lRows As Long Dim lLimit As Long Dim lCount As Long 错误恢复下一步 lRows = 2 lLimit = 1000 设置wks = ActiveSheet 对于工作表中的每个wks 如果Ri ght（wks.Name，2）=-A或右（wks.Name，2）=-B然后用wks 带.Cells（1，1）.CurrentRegion 与.Columns（C：D）如果CBool​​（Application.CountBlank（.Cells））然后对于每个blnk在.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， - ] c：r9999c，match（rc1，r [1] c1：r9999c1,0）），r [-1] c）） blnk.Value = blnk.Value 下一个blnk 结束如果结束结束结束结束如果下一个wks 结束Sub 使用xlR1C1风格的公式和它的xlA1等价物： = IF（COUNTIFS（R1C1：R [-1] C1，RC1，R1C： R [-1] C， - ），INDEX（R1C：R [-1] C，MATCH（RC1，R1C1：R [-1] C1,0）），IF ] 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 ，$ A $ 1：$ A1，0）），IF（COUNTIFS（$ A3：$ A $ 9999，$ A2，C3：C $ 9999，），INDEX（C3：C $ 9999，MATCH ，$ A3：$ A $ 9999，0）），C1）） 目标是根据条件 C：D 填充现有值： 1。以 C：D 中的值填充，直到相同列中的下一个非空单元格（这些非空单元格包含唯一值firstname1，firstname2等） 2。取 C：D 中的值，并填写向上，如果上面的空行在列A 中具有相同的值，其行在下面，我们正在处理的价值。 @ Jeeped的答案正常工作： 它需要列 C：D ，并填写空行，直到包含新值的下一行;如果该空行在列 A 中共享相同的值，那么它还会填满一个空行，下面的行包含要复制的值。 ，但只有填写一行。我的示例数据（以下链接）显示可能有 多个空行 在包含需要填写的列 C：D 中的值的行之上。 如何修改此vba代码以适应此条件？ 这是具有较长示例数据的excel工作表。该工作簿包含两个表格：我有什么& PS 除了第一列中的代码之外的星号除外视觉可访问性的目的是识别在列 A 中共享相同值的空行与下面的行具有列 C：D 中的值;否则它们不会出现在原始数据表中。 通过空行，我的意思是那些没有列 C：D 中的值。 解决方案我已经对公式进行了更多的复杂化，以实现垂直查找到多个列。需要修改的单行是所有嵌套代码的中心。 blnk.FormulaR1C1 == if（countifs r1c1：r [-1] c1，rc1，r1c：r [-1] c， - ），索引（r1c：r [-1] c，match（rc1，r1c1： 1] c1，0）），if（countifs（r [1] c1：r9999c1，rc1，r [1] c：r9999c， - ），index（r [1] c：r9999c，分钟（指数（行（R：r9998あ）行（R [-1]）+（（R [1] C1：r9999c1<> RC1）+未（LEN（R [1] C：r9999c）））* 1e + 99，，））），r [-1] c）） 一个xlA1风格的公式，如下所示（从C2看到）。 = IF（COUNTIFS（$ A $ 1：$ A1， （A2 $，A $ 1：$ A1,0）），IF（COUNTIFS（$ A3：$ A $ 9999，$ A $，C $ 1：C1，& $ A2，C3：C $ 9999，），INDEX（C3：C $ 9999，MIN（INDEX（ROW（2：$ 9998）-ROW（1：1）+（（$ A3：$ A $ 9999< ; $ A2）+ NOT（LEN（C3：C $ 9999）））* 1E + 99，，））），C1）） 请注意，编码公式需要将带引号的字符串中的引号加倍。 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 info426391* value5 info526391* value6 info626391* value7 firstname2 lastname2 info727878 value8 info827878 value9 info928234 value10 info1063189* value11 info1163189* value12 info1263189* 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 info2598712* 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 info426391 value5 firstname2 lastname2 info526391 value6 firstname2 lastname2 info626391 value7 firstname2 lastname2 info727878 value8 firstname2 lastname2 info827878 value9 firstname2 lastname2 info928234 value10 firstname2 lastname2 info1063189 value11 firstname3 lastname3 info1163189 value12 firstname3 lastname3 info1263189 value13 firstname3 lastname3 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 info2598712 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 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 Subwith the formula in xlR1C1 style and its xlA1 equivalent:=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 goal is to fill up and down the columns C:D with the existing values based on the conditions:1. Take the values in C:D and fill down until the next non-empty cell in the same columns.(these non-empty cells contain unique values, firstname1, firstname2, etc)2. Take the values in C:D and fill up if the empty row(s) above share the same value in Column A with the row which is below and whose values we are coping up.@Jeeped's answer is working correctly:it takes the values from columns C:D and fills down the empty rows until the next row that contains new values; it also fills up one empty row if the that empty row shares the same value in column A with the row below that contains the to-be-copied values. but it only fills up one row. My example data (link below) shows that there might be more than one empty row above the rows that contain values in column C:D that need to be filled up.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 to identify the empty rows that share the same value in column A with the row below that has values in columns C:D; otherwise they don't appear in the original data sheet. By empty row, I mean those rows which don't have values in columns C:D. 解决方案 I've complicated the formula a bit more to achieve the vertical lookup to more than a single column. The single line that needs modification is in the center of all the nested code.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, min(index(row(r:r9998)-row(r[-1])+((r[1]c1:r9999c1<>rc1)+not(len(r[1]c:r9999c)))*1e+99, , ))), r[-1]c))"That translates to an xlA1 style formula like the following (as seen from C2).=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, MIN(INDEX(ROW(2:$9998)-ROW(1:1)+(($A3:$A$9999<>$A2)+NOT(LEN(C3:C$9999)))*1E+99, , ))), C1))Note that the coded formula requires doubling up the quotes within the quoted string. 这篇关于根据另一列中的值，在多列中填充和关闭单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！