[VBA] Autofill selected cells

[Alexlin]

Hi guys, I’ve encountered a difficult task.
There are two sheets (“sheet1” and “sheet2”)
In sheet1, Column A1:A100 are filled with 5 names (some are blank cells)

In sheet2, there are some cells required to be filled in (I.e.,A13, B23, C18, D50, E5). The value to be filled are exactly the five names from sheet1.

What should the VBA be composed so that no matter how the five names located in different row, they would still automatically be filled into those five no-pattern cells in sheet2?

Thanks a lot!

 

[Alexlin]

Try this macro. It will also automatically adjust if the number of names in column A changes.

Sub FillCells()
    Application.ScreenUpdating = False
    Dim Rng As Range, lastRow As Long, sVal As String, Key As Variant, RngList As Object, x As Long, y As Long
    y = 1
    lastRow = Sheets("E&P Allocation").Range("A" & Sheets("Data Input").Rows.Count).End(xlUp).Row
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In Sheets("Data Input").Range("A20:A" & lastRow)
        If Rng <> "" Then
            If Not RngList.Exists(Rng.Value) Then
                RngList.Add Rng.Value, Nothing
            End If
        End If
    Next Rng
    For Each Key In RngList.keys
        sVal = sVal & "!" & Key
    Next Key
    splitVal = Split(sVal, "!")
    With Sheets("E&P Allocation")
        For x = 14 To lastRow Step 44
            .Range("D" & x) = splitVal(y)
            y = y + 1
            If y > UBound(splitVal) Then Exit For
        Next x
    End With
    Sheets("Data Input").Range("A19").AutoFilter
    Application.ScreenUpdating = True
End Sub

Thanks! It works!!

 

[mumps]

You are very welcome.