0

I have various empty cells in a table that I want to fill with the last known value in that column.

Sub autofiller()

Dim DataRange As Range
Set DataRange = Range("A1:D4")
Dim i As Integer

FillA = ""

For Each cell In DataRange

If cell(1, 1).Value <> "" Then

FillA = cell(1, 1).Value

Else
    cell(1, 1).Value = FillA

End If

i = i + 1

Range("C1").Value = i

Next

End Sub

There will be no empty values in the first row. My logic is that it will look through every cell in that range, and if it is not an empty cell, pick up the value until it hits an empty cell where then I will place that value. The i counter is for me to keep track. It seems to me that the code is checking cell by cell horizontally but I want it to check cell by cell vertically. How can I make it check vertically?

I am new to VBA so any additional comments/guides will help.

AltoidsBenefitsH
  • 55
  • 2
  • 7
  • Can you elaborate a little? Range `A1:D4` should not be blank, correct? If a cell *is* blank, you want to fill it with the value in Cell `A1`? You need to make your references relative, as right now, only Cell `A1` is being checked if it's blank. Do you want to check each cell in `A1:D4`? – BruceWayne Mar 09 '16 at 21:45
  • A1:D1 is not blank, but any below it may still be blank. Yes, that is what I want, a blank to be filled with a value that is known previously. I thought cell(1,1) meant I start the iteration at A1, but for some reason it checks cell(1,2) and not cell(2,1) first like I want. – AltoidsBenefitsH Mar 09 '16 at 21:48

1 Answers1

1

Try this:

Sub autofiller()
Dim cell as range
Dim DataRange As Range
Set DataRange = Range("A1:D4")

For Each cell In DataRange
    If cell.Value = "" Then
        cell.Value = cell.Offset(-1).Value
    End If
Next

End Sub

Because the for each will go left to right, top to bottom it will fill it with the cell above as it encounters empty cells.

You are confusing your variable cell which is a range variable with Cells() which is a range object. cell(1,1) is equal to cell as it is getting the first cell in a range of one.

Another way to write cell.Offset(-1) would be cell(-1,1) this gets the cell directly above the cell.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This works, perfectly and clear in logic. If I wanted to make the lower right corner dynamic as in I know for sure that it will have a value, how will I be able to do that? – AltoidsBenefitsH Mar 09 '16 at 21:51
  • 1
    After so many times of trying to point out not to use `Cell` as a variable, I kind of gave up...Thanks for pointing that out, I think that causes some issues with a lot of people. – BruceWayne Mar 09 '16 at 21:52
  • Ah, so did I implicitly create a variable "cell"? – AltoidsBenefitsH Mar 09 '16 at 21:53
  • @AltoidsBenefitsH that is a different question. The answer is [HERE](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). – Scott Craner Mar 09 '16 at 21:54
  • @AltoidsBenefitsH yes you did by using it in the for each loop. It sets the range to the variable cell. This variable then changes as the loop iterates. – Scott Craner Mar 09 '16 at 21:55
  • Thanks Scott, I accepted, but I can't upvote it, not enough rep yet – AltoidsBenefitsH Mar 09 '16 at 21:56