Loop to Repeat Names

Question

Loop to Repeat Names

Navigation

#1 by (1 votes)
#2 by (-1 votes)

1

Dear Community,

I think the solution is simple, but I'm trying to do the following business rule:

I have a list of names in a Plan1, I need the name of each of them to be repeated by 12x each in Plan2, I have a code that can repeat the first name, but I can not increase my value.

Sub RODAR()


Dim num As Integer
Dim wb As Workbook
Dim ws As Worksheet
Dim wss As Worksheet
Dim nome As String

Set wb = ActiveWorkbook
Set ws = Sheets("Plan1")
Set wss = Sheets("Plan3")

nome = 1

For cont = 2 To 13

wss.Select
nome = Cells(2, 2)

ws.Select
Cells(cont, 2).Value = nome

nome = nome

Next

loop vba excel-vba

asked by anonymous 06.09.2017 / 19:18

2 answers

1

I would need to increment your loop with some information, follow a small template I did (I could not test ...) but to see the logic:

Dim i As Integer
Dim j As Integer
Dim wsORIGEM As Worksheet
Dim wsDESTINO As Worksheet
Dim NOMES As Variant
Dim NOME As Variant

Set wsORIGEM = Sheets("Plan1")
Set wsDESTINO = Sheets("Plan2")

NOMES = wsORIGEM.Range("A1:A3").Value

For Each NOME In NOMES
    j = j + 1
    For i = 1 To 12
        If j = 1 Then
            wsDESTINO.Cells(i, 1).Value = NOME
        Else
            wsDESTINO.Cells(i + (12 * (j - 1)), 1).Value = NOME
        End If
    Next
Next

I think you can clean this loop and these variables, however, I did over your code just to work , but I confess that it was pretty "ugly" code ai ... sorry for that.

06.09.2017 / 19:37

Incorrect display of characters in html / jsp page What is the advantage of having a single instance when using the singleton pattern in web development?

score -1 · Accepted Answer

Many thanks Evert,

Here is a solution to the above question:

Remembering that I had as much help from Mr. Evert as from Mr. Anderson

Sub RODAR()
    
    Dim num As Integer
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wss As Worksheet
    Dim nome As String
    Dim linha As Long
    Dim linha2 As Long
    
    
    Set wb = ActiveWorkbook
    Set ws = Sheets("Plan1")
    Set wss = Sheets("Plan3")
    
    
    nome = 1
    linha = 2
    linha2 = 2
    
    While wss.Cells(linha, 2) <> ""
    While ws.Cells(linha2, 2) <> ""
    linha2 = linha2 + 1
    Wend
        For cont = linha2 To linha2 + 11
        nome = wss.Cells(linha, 2)
        ws.Cells(cont, 2).Value = nome
        Next cont
        
        linha = linha + 1
    Wend
    
End Sub