0

Trying to copy one value into the end of one populated column.

The simple structure of the column is filled with random numbers and looks like this without empty cells inbetween. 

11
12
23
45

The function should find the next available empty cell in that column and adds a value at the end of that column. And it then should stop. Mine doesn't stop and populates the entire column. 

Sub FindNextEmptyCell()

    If IsEmpty(Range("A:A")) Then
    Range("A:A") = Sheets("Test").Range("A1").Value
    End If

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
purpleblau
  • 589
  • 2
  • 7
  • 18
  • 2
    Are you just trying to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba)? – BigBen Dec 19 '19 at 13:41
  • 1
    Watch out for implicit sheet references too using `Range` objects =). Bit of a ridle also why your range gets populated at all since `If IsEmpty(Range("A:A")) Then` is probably never `TRUE`. Either way, the link provided by Ben will direct you further. – JvdV Dec 19 '19 at 13:45
  • Thanks for the input. The search doesn't start from the top row of the column, it starts say at row 10 and goes to 100. 100 is the last value. I need to put a value to row 101 and the code needs to stop then. I have no idea how to do it exactly. Can someone maybe put a working code? – purpleblau Dec 19 '19 at 14:12

1 Answers1

1

Are you looking for something like that?

Option Explicit

Sub test()

    Dim LastRow As Long, i As Long

    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 10 To LastRow+1

            If .Range("A" & i).Value = "" Then
                .Range("A" & i).Value = .Range("A" & i - 1).Value + 1
            End If

        Next i

    End With

End Sub
Error 1004
  • 7,877
  • 3
  • 23
  • 46
  • This works like magic! Thanks a bunch. Two questions. What does .Range mean? And what did you do exactly here .Range("A" & i - 1).Value + 1 ? – purpleblau Dec 19 '19 at 16:27
  • 1
    `.Range("A" & i).Value` refer to the range in column A and row i. `.Range("A" & i - 1).Value + 1` add one to the value in range column A row i – Error 1004 Dec 19 '19 at 20:19