looking for VBA code

[Wloven]

I am trying to create a VBA code which copy a cell and paste with three condition:
1- If the cell next to the cell targeted is not duplicate.
2- will continue to paste this data till the certain number exceed.
3- will copy another cell and paste with the two above condition also.

(the picture below explain more:
a) the S columns is the target cell where I want to paste.
b) the red circle is the number of how many each name highlighted in yellow should paste.
c) they should not paste if Duplicate appear in columns AA.

 

[sijpie]

Hi Wloven, welcome to MrExcel. I kind-of understand what you are asking (very clear image example), but I don't understand what needs to be copied where. Can you make a similar screenshot of what the output should look like?

Thanks

 

[Wloven]

Hi Wloven, welcome to MrExcel. I kind-of understand what you are asking (very clear image example), but I don't understand what needs to be copied where. Can you make a similar screenshot of what the output should look like?

Thanks

Thank you sijpie for reply, I want to copy the names in red circles to column S, but the next name will start when the first name finish loop depending on the number on cell X4, with condition not paste if the duplicate show in cell. hope that more clear.

 

[sijpie]

I can look at it in the week of 21st

 

[Wloven]

Any one to help please

 

[Zot]

Thank you sijpie for reply, I want to copy the names in red circles to column S, but the next name will start when the first name finish loop depending on the number on cell X4, with condition not paste if the duplicate show in cell. hope that more clear.

Meaning the name Was will be written in column S as many as 13 times in the row with not Duplicate stated in column AA, right?
Where is the next name? Is it Kira? If so, how many times Kira would be listed? Also 13 times?

What would happened after that? Is there another name coming?

 

[Wloven]

Meaning the name Was will be written in column S as many as 13 times in the row with not Duplicate stated in column AA, right?
Where is the next name? Is it Kira? If so, how many times Kira would be listed? Also 13 times?

What would happened after that? Is there another name coming?

Thank you Zot for reply, yes you are right was will be paste in column S as many as 13 times (depend on cell X4) but if there is a duplicate in column AA it will go to the next cell with not effect the number of paste (13 times Was should appear in column S ). Then the next name Kira same number as in cell X4 with same condition after that the next name and verse versa(I have maximum 4 names but depends if they attend in that day or not). Hope that help

 

[Zot]

I wrote the code in steps which I think easy to understand. Let me know if this works. The max name is 4 as you mentioned and it is from column W to Z. You can adapt to your actual starting row and column

Sub WasKira()

Dim nRow As Long, nCount As Long, TotalCount As Long
Dim ColAssign As String, ColDuplicate As String
Dim Name As Range, rngName As Range
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

nRow = 2                                                           ' Assuming starting row is 2
ColAssign = "S"                                                     ' Define Assign To column
ColDuplicate = "AA"                                                ' Define Duplicate column
Set rngName = ws.Range("W1", "Z1")                        ' Defined where the 4 names reside

TotalCount = ws.Range("X4")

For Each Name In rngName
    If Not Name = "" Then
        Do Until nCount = TotalCount
            If Not ws.Range(ColDuplicate & nRow) = "Duplicate" Then
                ws.Range(ColAssign & nRow) = Name
                nCount = nCount + 1
            End If
            nRow = nRow + 1
        Loop
        nCount = 0
    End If
Next Name
       
End Sub

 

[Wloven]

I wrote the code in steps which I think easy to understand. Let me know if this works. The max name is 4 as you mentioned and it is from column W to Z. You can adapt to your actual starting row and column

Sub WasKira()

Dim nRow As Long, nCount As Long, TotalCount As Long
Dim ColAssign As String, ColDuplicate As String
Dim Name As Range, rngName As Range
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

nRow = 2                                                           ' Assuming starting row is 2
ColAssign = "S"                                                     ' Define Assign To column
ColDuplicate = "AA"                                                ' Define Duplicate column
Set rngName = ws.Range("W1", "Z1")                        ' Defined where the 4 names reside

TotalCount = ws.Range("X4")

For Each Name In rngName
    If Not Name = "" Then
        Do Until nCount = TotalCount
            If Not ws.Range(ColDuplicate & nRow) = "Duplicate" Then
                ws.Range(ColAssign & nRow) = Name
                nCount = nCount + 1
            End If
            nRow = nRow + 1
        Loop
        nCount = 0
    End If
Next Name
      
End Sub

Thank you Zot but the code not working, it not do anything.

 

[Wloven]

Thank you Zot but the code not working, it not do anything.