VBA to insert my text in a range

[berserkdude]

Hello.
Putting it in simple terms: I am looking for a code that puts the text from cell c3 into a range of cells from c5:c75.
For example in range(c5:c75), if the cells c5 and c6 already have text, I would want to fill the cell c7 with my text. Another example is: if cells c5 and c7 have text but cell c6 doesn't have any text, I would like my text to go there.

Right now my code does not seems ideal, but it goes like this:

Sub check text()
'In range A1: =Countif(c5:c75;"<>"&"")
If Range("A1") = "0" Then
Range("c5") = Range("c3")

ElseIf Range("A1") = "1" Then
Range("c6") = Range("C3" )

ElseIf Range("A1") = "2" Then
Range("C7") = Range("c3")

'And it keeps going until:

ElseIf Range("A1") = "74" then
Range("c75") = Range("c3")

ElseIf Range("A1") = "75" then
Msgbox("range is full")

End if
The problem is the massive size of it, and for it stops working if I have a empty cell between cells with text. For example, if cell c5 has text, cell c6 is empty and cell c7 has text. The code wont work.

Thanks for the time

 

[madnes88]

So, if I understand you correctly, you want your text to go into the first blank cell on the row?

 

[JackDanIce]

Welcome to the board. Are you *only* filling blank cells in range C5:C75 with the value in C3? If so, try:

Sub FillMe()

Application.ScreenUpdating = False

On Error Resume Next
Range("C5:C75").SpecialCells(xlCellTypeBlanks).Value = Range("C3").Value
On Error Goto 0

Application.ScreenUpdating = True

End Sub

 

[berserkdude]

Correct. If cell c5 is blank than the text will be inserted there. If not, it'll be inserted in c6 and so on.

 

[berserkdude]

That code dindt work for me.
The first time it worked with cell c5. But after that it doesn't work anymore :-/

 

[JackDanIce]

Put a filter on values C5:C75 and filter for blank cells only. This is what the code does, then populates those blank cells with value in C3.

Did you clear C5 before you tested again? Did you try having other blank cells empty?

 

[berserkdude]

Put a filter on values C5:C75 and filter for blank cells only. This is what the code does, then populates those blank cells with value in C3.

Did you clear C5 before you tested again? Did you try having other blank cells empty?

I tried that.
One time it fill c5 and stopped working
Other time it didn't work
And a third time it fill all the blanks. When what I want is to fill one at a time. C5 has text, then fill c6. If C5 and c6 has text, then fill c7. If c5 and c7 have text, fill c6. And so on, but filling one cell at the time when I run the code. :-/

 

[JackDanIce]

Why does it need to fill in, one cell at a time? My code is doing it in a single pass and filling all blank cells with value of C3.

 

[berserkdude]

Because values from c3 are constantly changing :-/

 

[JackDanIce]

Based on your original code, this might be what you want:

Sub FillMe_2()
    
    If Range("A1").value = 75 Then
        MsgBox "Range is full"
    Else
        Range("C5").Offset(Range("A1").value) = Range("C3").value
    End If

End Sub