查找值并在多个后续范围内填充.filldown? [英] Find a value and .filldown over multiple subsequent ranges?

问题描述

VBA新手在这里.我有多个组的动态列表.每个小组在顶部列出小组的负责人，在下面列出小组的成员.每个列出的人员旁边的A列中都有一个主键.我想获取组长的密钥#并将其应用于组长和组中每个成员的F列，以使每个成员在A列中都有自己的主键# 与他们的 leader's 的主键.这是我之前和之后需要的两张图片:

VBA newbie here. I have a dynamic list of multiple groups. Each group lists the leader of the group at the top with the members of the group below. There is a primary key in Column A next to each listed person. I want to take the leader's key # and apply it to Column F for the leader and each member of the group, such that each member has their own primary key # in Column A and is associated with their leader's primary key # in Column F. Here are two images of what I need for the before and after:

之前

之后

这是我在概念上正在使用的代码:

Here is the code I am playing around with conceptually:

Sub Apply_Leader_Code()

Dim wSht As Worksheet
Dim lStart As Long, lEnd As Long, lLdrID As Long

Set wSht = ThisWorkbook.Sheets("Upload")

With wSht.Range("A1:G" & Range("A" & Rows.Count).End(xlUp).Row)
    lStart = .Rows.Find("78") 'Find the first row for each group
    lEnd = .Rows.FindNext("78") - 1 'Find the last row for each group
If .Range("G" & lStart & ":G" & lEnd).Value = "" Then 
        'If there is no leader ID yet, then continue...
    lLdrID = .Cells(lStart, 1).Value 'Assign leader's primary key to the variable
    .Cells(lStart, 7).Value = lLdrID 'Place lLdrID value into Column G
    .Range("F" & lStart & ":F" & lEnd).FillDown 'Fill value to end of group range
Else
        '..otherwise, set start/end rows for next group.
    lStart = .Rows.FindNext("Leader")
    lEnd = .Rows.FindNext("Leader") - 1
End If
End With
End Sub

以上代码实际上并不适用，但我希望代表我认为是解决此问题的合理方法.我可以(也许?)弄清楚如何对第一组执行此操作，但是接下来我如何对每个后续组执行相同的.FillDown功能?

The above code isn't actually applicable, but I hope represents what I think is a reasonable way to solve this problem. I can (maybe?) figure out how to do this for the first group, but how do I then perform the same .FillDown function for each subsequent group?

-编辑-

关于Siddarth Rout的以下回答，这是我的更新代码:

In regards to Siddarth Rout's answer below, here is my newer code:

Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-6]"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=78,RC[-6],R[-1]C)"
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row)

我在Excel中使用了Macro创建器，然后将其编辑为我认为可以使它具有动态范围而不是设置范围的内容.现在我遇到了400错误.有什么想法吗?关于如何输入公式而不选择范围的任何想法?我聚集了许多程序员，认为选择单元格是不好的编程...

I used the Macro creator in Excel and then edited it to what I thought would enable it to have a dynamic range instead of a set range. Now I'm getting a 400 error. Any ideas why? Any ideas on how to input the formulas w/o "selecting" the range? I am gathering that many programmers think that selecting cells is bad programming...

推荐答案

您是否需要VBA?这可以使用excel公式实现

Do you need VBA for this? This can be achieved using excel formulas

单元格G2

=A2

单元格G3

=IF(F3=78,A3,G2)

现在只需将公式从G3复制到G14

now simply copy the formula from G3 down to G14

如果您仍然需要VBA，则只需为上述步骤记录一个宏，然后对其进行修改:)

If you still need VBA then simply record a macro for the above steps and amend it :)

关注(来自评论)

是.Select应该避免. 有趣的阅读

也无需使用R1C1格式.您可以直接指定公式

Also no need to use R1C1 format. You can directly specify the formula

还有一件事.您不需要使用Autofill.您可以通过在一键通

And one more thing. You don't need to use Autofill. You can skip that step by directly filling all the relevant cells with the relevant formula in ONE GO

这是您要尝试的吗?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find the last cell in Col A which has data
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Enter first formula
        .Range("G2").Formula = "=A2"

        '~~> Enter 2nd formula in all the
        '~~> cells in one go rather than autofill
        .Range("G3:G" & lRow).Formula = "=IF(F3=78,A3,G2)"
    End With
End Sub

这篇关于查找值并在多个后续范围内填充.filldown?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！