Automatically fill cells in excel using VBA

Question

Automatically fill cells in excel using VBA

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

Good afternoon community,

I am trying to work with VBA in Excel to try to apply this knowledge in my master's thesis, but I have some questions.

I'm trying to create a function in VBA that populates cells by a given value. In the next image I have two frames, one with values in the vertices and another one filled with value 1. The left frame represents a surveillance camera, which is in cell B1. In the right frame is supposed to appear the coverage area of that same camera. The chamber area occupies 10 cells.

The code I have is the following:

Sub Limpar()
Range("G5:J8") = ""
End Sub

Sub Executar()
If Cells(5, "B").Value = 1 Then Preencher
End Sub

Sub Preencher()
Dim contador As Integer
contador = 10
Do While contador > 0
Range("G5:J8") = 1
contador = contador - 1
Loop
End Sub

And your result is this:

Asyoucanseemyfunctionfillseverything,andthegoalwastofillonly10cells.WhatIwantedwassomethingliketheoneinthenextimage:

Does anyone know how to do in VBA for the right frame to display only the number of cells filled by the camera area? That in this case 10 filled cells should appear. I know that my code fills the whole frame, but that's not what I was trying to do, I wanted to fill the grid with the grid until it matches the 10 squares corresponding to the chamber area of cell B5.

If I can get to one camera, I'll probably get to other cameras in different areas and put them at different vertices.

Thank you for helping me.

loop excel do-while excel-vba

asked by anonymous 13.07.2017 / 19:41

2 answers

0

If it is something simple and fixed, simply create 4 "Ifs" of these:

If Range("b5") = 1 Then
Range("g5,H5, I5,J5, G6, H6, I6, g7, h7, g8") = 1
End If

17.07.2017 / 04:27

help on github [closed] Problems retrieving DB data with Entity 6.1.3 and Web API

score 0 · Accepted Answer

Try to define a limit of scope and from this subtract the values, something like this:

Sub preencherCameras()
Dim Limite, Linha, Coluna As Integer
Limite = 4

    Linha = Application.ActiveCell.Row 'Linha Atual

    For y = 0 To Limite - 1 'Repetir até o Limite Alcance
        Coluna = Application.ActiveCell.Column 'Coluna Atual
        For x = 1 To Limite
            Cells(Linha + y, Coluna) = 1
            Coluna = Coluna + 1
        Next x
    Limite = Limite - 1 'Diminuir o limite
    Next y

End Sub