Excel VBA - 查找并填写值 [英] Excel VBA - Find and Fill in Values

问题描述

对于noobish问题抱歉，但我是新的，我没有太多的编程背景。我自己尝试了几个小时，但我只是不够了解。我检查了此和这个，但是我无法弄清楚如何修改它。

Sorry for the noobish question, but I'm new and I don't have much of a programming background. I tried this on my own for a few hours, but I just don't know enough. I checked this and this, but I couldn't figure out how to modify it enough.

示例数据

第一部分是我如何获取文件，第二部分是我想要的看看。

The first part is how I get the file, the 2nd part is how I want it to look.

前3列在列中的某个地方有值。我需要找到这些值，复制它们，并将它们粘贴到下一个值，然后重复一遍到范围的底部。有时每列有许多值，有时只有1。数据的最后一行可以由列4确定。基本上，我只需要填写所有空白单元格。注意：第2行并不总是包含第一个值。

The first 3 columns have values somewhere in the column. I need to find those values, copy them, and paste them down to the next value, then repeat all the way to the bottom of the range. Sometimes there are many values per column, sometimes only 1. The last row of the data could be determined by column 4. Basically I just need to fill in all the blank cells. Note: Row 2 does not always contain the first value.

以下是我目前为止（更新）：

Sub FindFillIn()

    Dim columnValues  As Range, i As Long
    Dim cellstart As Integer
    Dim cellend As Integer
    cellstart = 2
    cellend = ActiveSheet.Range("E" & ActiveSheet.Rows.Count).End(xlUp).Row

    i = 1

    For i = cellstart To cellend
        If Cells(i, 1).Value = "" Then
            Cells(i, 1).Value = Cells(i - 1, 1).Value
        End If
    Next i

End Sub

更新：

它似乎在第一列正确运行，但只能执行一列。如何让它运行在第1,2列和第3列？

It appears to run correctly on the first column, but it only does one column. How do I get it to run on columns 1, 2, and 3?

推荐答案

Sub FillInBlanks()

    Dim rng As Range

    On Error Resume Next
    With ActiveSheet
        Set rng = .Range(.Range("A2"), _
         .Cells(Rows.Count, 4).End(xlUp)).SpecialCells(xlCellTypeBlanks)
    End With
    On Error GoTo 0

    If Not rng Is Nothing Then
        With rng
            .FormulaR1C1 = "=R[-1]C"
            .Value = .Value
        End With
    End If


End Sub

这篇关于Excel VBA - 查找并填写值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！