automatly fill a given number of cells with a given value

[alexandru barabas]

Hi,

I'm trying to fill automatly a certain number of cells with a given value. i want to fill the blank spaces in the table between x, c, and b , with number "8''. but i want it to fill only the first 10 blank cells from the AA row. Then the first 10 at AB , etc. Do i have to use VBA codes or it can be don by formulas.

thank you

 

[PCosta87]

Hi,

Most likely you will have to use code.
Try the following:

Sub LoopColumns()

    Dim x, i As Integer
  
    x = 1
    For i = 27 To 32
        Do While x <= 10
            Columns(i).Find(what:="").Value = 8
            x = x + 1
        Loop
        x = 1
    Next i
  
End Sub

27 is the index of the "AA" column... 32 is "AF". Replace 32 with the index number of the last column you wish to loop through.

Hope this helps

 

[alexandru barabas]

can you change it to search blnakspaces only in that array? let's say i want to search blankspaces in the table (AA,35 ; AZ;8 ). can it be done like this?
thanks

 

[PCosta87]

can you change it to search blnakspaces only in that array? let's say i want to search blankspaces in the table (AA,35 ; AZ;8 ). can it be done like this?
thanks

Hi,

Sure, try:

Sub LoopColumns()

    Dim x, i As Integer

    x = 1
    For i = 27 To 52
        Do While x <= 10
            Columns(i).Rows("8:35").Find(what:="").Value = 8
            x = x + 1
        Loop
        x = 1
    Next i

End Sub

It should work!

 

[alexandru barabas]

hi,

i don't know why but it gives me an error 91 object variable or with block variable not set.

 

[Marc L]

Hi !

Just a bad use of Find method ! See its example within VBA inner help …​

 

[PCosta87]

Try the attached file... works fine here.

 

[alexandru barabas]

Sub Loopcells()

Dim x, i As Integer, j As Integer

For j = 5 To 35
x = 1
For i = 27 To 52
Do While x <= 10
Cells(i, j).Find(what:="").Value = 8
x = x + 1
Loop
x = 1
Next i
Next j


End Sub


something like this its possible?

 

[PCosta87]

Sub Loopcells()

Dim x, i As Integer, j As Integer

For j = 5 To 35
x = 1
For i = 27 To 52
Do While x <= 10
Cells(i, j).Find(what:="").Value = 8
x = x + 1
Loop
x = 1
Next i
Next j


End Sub


something like this its possible?

Just a correction first... the statement goes like "Cells(Row, Column)" and I believe you are doing it the other way around: j are your rows and i the columns.

Now, I think you were going for something like "if the cell is empty":

If IsEmpty(Cells(j, i)) = True Then
Cells(j, i).value = 8
End If

I can't test it now but I think it won't work because you need to be able to set the direction in which excel goes through the cells (in order to count the cells and stop at 10). In this case you will need it to go from top to bottom of the range.
This is why I used the find method as you can set the direction.
One problem may be if you don't have 10 empty cells available to use, in which case it would probably throw an error.
Using "On Error Resume Next" should be enough to fix that, I think.

 

[alexandru barabas]

the problem is that i can't make this work

Sub LoopColumns()

Dim x, i As Integer

x = 1
For i = 27 To 52
Do While x <= 10
Columns(i).Rows("8:35").Find(what:="").Value = 8
x = x + 1
Loop
x = 1
Next i

End Sub

it gives me an error when i want to run it. and after i delete Rows("8:35"), it gives me this. i want to starputting 8 in that table not under it.
and how can i make this changes whit the protect seet mod on?

 

[PCosta87]

Hi,

I'm confused now... weren't the numbers arranged vertically before? In column "Z"?

Anyway, if you can please upload a sample excel file... it is much easier to give a targeted answer that way.

 

[alexandru barabas]

its only a sheet , and it takes data from other 12 sheets so it's not complete.

i want to fill only 10 blank spaces in each column (AA5:AA35) then (AB5:AB35) of the table AA5:BB35 whit nr 8. i will allways have more blankspaces then 10 so it wont be any error if it can't fill 10 .
tanks

 

[PCosta87]

Hi,

You were getting the error 91 because the cells in that range aren't empty... Although they don't show anything, they all have formulas.

Please refer to attachment and let me know if that works for you.

 

[alexandru barabas]

you are right , there werent empty spaces , there were coppying with formula the values from the other table.

then can i make a vba to coppy all the values from the BE5:CF35 to AA5:BB35, and replace a number of values that are number ( ex 8 or 4) with the letter '''N". the number of values that i want to repleace is finde at each row in the table AA5:B35 in the AA3:BB3.

i'm trying to do this for a week , i realy don't know how ,thanks

 

[PCosta87]

Hi,

Sorry for the late response.

I didn't fully understand this last requirement... could you explain it better?
Thanks.