How to make the VBA code I have loop

[ostrdevi]

Hi, I am trying to write up a code that will go through this Excel sheet I have that has it's information split into individual columns. I have code that works but I have to manually change the range every time , I am really hoping there is a way to have this Loop or something that is faster. Here is the code I have right now and an Example of the Excel sheet I am working with. I had to change one of the values in the excel because I don't think I can show it by so I replaced it with the words, "fill". This should really affect anything because the values that are actually supposed to be there are three letters. Thank you in advance to anyone who takes the time to read this and or try and help with it, I really appreciate it.

 

[Max1616]

Hello, and welcome to the forum!

I may be confused as to what the final output is that you're looking for. Could you provide some context?

• Are you looking for column "O" to contain First+Mid+Last name for all records in your data set?
• Do you always get First+Mid+Last from columns E/F/G or do you also need to grab it from B/D/C & J/K/L?
• If you are trying to loop through EFG then BCD then JKL, where do you want the outputs of the loop to live? All in column O? Or in a different spot?

It would be helpful if you could provide a screenshot of what you would like the Excel to look like once the code has finished.

Thanks!

 

[ostrdevi]

Hello, and welcome to the forum!

I may be confused as to what the final output is that you're looking for. Could you provide some context?

• Are you looking for column "O" to contain First+Mid+Last name for all records in your data set?
• Do you always get First+Mid+Last from columns E/F/G or do you also need to grab it from B/D/C & J/K/L?
• If you are trying to loop through EFG then BCD then JKL, where do you want the outputs of the loop to live? All in column O? Or in a different spot?

It would be helpful if you could provide a screenshot of what you would like the Excel to look like once the code has finished.

Thanks!

Thank you for responding so fast, I am trying to have it loop through ABC then EFG then IJK. All of the outputs going into O is fine, I just need all of the outputs in the same column. I went through and did ABC and put it in O, I would like all of the data that could be in ABC, EFG, and IJK to be added together kind of like this.

 

[Max1616]

Give this a shot:

Sub FillName()

Dim i As Long
Dim j As Long
Dim StartClm As Integer
Dim jlrow As Long
Dim DestClm As Integer
Dim Destlrow As Long
Dim LoopAmt As Integer

DestClm = 15 '15 represents Column O - Which will be the destination of the macro
LoopAmt = Application.CountIf(Range("1:1"), "ULD")

If LoopAmt = 0 Then Exit Sub

For i = 1 To LoopAmt
    StartClm = (i * 4) - 3
    jlrow = Cells(Rows.Count, StartClm).End(xlUp).Row
    
    For j = 2 To jlrow
        Destlrow = Cells(Rows.Count, DestClm).End(xlUp).Row + 1
        Cells(Destlrow, DestClm) = Cells(j, StartClm + 1) & Cells(j, StartClm + 2) & Cells(j, StartClm + 3)
    Next j
  
Next i

End Sub

This will determine the number of times it needs to loop based off the # of times "ULD" is in row 1. This will loop through any number of rows you have (And you can have a different number of rows in each section (a-d, e-h, i-l, etc...). You can add as many sections as you'd like, just make sure to update the "15" to a different number if you'd like the results to be in a column other than "O".

Let me know if you need any additional help!

 

[ostrdevi]

Give this a shot:

Sub FillName()

Dim i As Long
Dim j As Long
Dim StartClm As Integer
Dim jlrow As Long
Dim DestClm As Integer
Dim Destlrow As Long
Dim LoopAmt As Integer

DestClm = 15 '15 represents Column O - Which will be the destination of the macro
LoopAmt = Application.CountIf(Range("1:1"), "ULD")

If LoopAmt = 0 Then Exit Sub

For i = 1 To LoopAmt
    StartClm = (i * 4) - 3
    jlrow = Cells(Rows.Count, StartClm).End(xlUp).Row
   
    For j = 2 To jlrow
        Destlrow = Cells(Rows.Count, DestClm).End(xlUp).Row + 1
        Cells(Destlrow, DestClm) = Cells(j, StartClm + 1) & Cells(j, StartClm + 2) & Cells(j, StartClm + 3)
    Next j
 
Next i

End Sub

This will determine the number of times it needs to loop based off the # of times "ULD" is in row 1. This will loop through any number of rows you have (And you can have a different number of rows in each section (a-d, e-h, i-l, etc...). You can add as many sections as you'd like, just make sure to update the "15" to a different number if you'd like the results to be in a column other than "O".

Let me know if you need any additional help!

Thank you so much, This worked amazingly but it using Columns BCD for example and I need it to use ABC, is it possible to change this? It is outputting 0350fill506 and using the Tare column instead of the ULD column when it outputs. The output I am hoping for is AAX0350fill. Thanks again, I hope this doesn't come across as rude, I don't mean it to.

 

[Max1616]

Not rude at all, and that's totally on me, I should have read closer what you were asking for. Here's the update:

Sub FillName()

Dim i As Long
Dim j As Long
Dim StartClm As Integer
Dim jlrow As Long
Dim DestClm As Integer
Dim Destlrow As Long
Dim LoopAmt As Integer

DestClm = 15 '15 represents Column O - Which will be the destination of the macro
LoopAmt = Application.CountIf(Range("1:1"), "ULD")

If LoopAmt = 0 Then Exit Sub

For i = 1 To LoopAmt
    StartClm = (i * 4) - 3
    jlrow = Cells(Rows.Count, StartClm).End(xlUp).Row
    
    For j = 2 To jlrow
        Destlrow = Cells(Rows.Count, DestClm).End(xlUp).Row + 1
        Cells(Destlrow, DestClm) = Cells(j, StartClm) & Cells(j, StartClm + 1) & Cells(j, StartClm + 2)
    Next j
  
Next i

End Sub

 

[ostrdevi]

Not rude at all, and that's totally on me, I should have read closer what you were asking for. Here's the update:

Sub FillName()

Dim i As Long
Dim j As Long
Dim StartClm As Integer
Dim jlrow As Long
Dim DestClm As Integer
Dim Destlrow As Long
Dim LoopAmt As Integer

DestClm = 15 '15 represents Column O - Which will be the destination of the macro
LoopAmt = Application.CountIf(Range("1:1"), "ULD")

If LoopAmt = 0 Then Exit Sub

For i = 1 To LoopAmt
    StartClm = (i * 4) - 3
    jlrow = Cells(Rows.Count, StartClm).End(xlUp).Row
   
    For j = 2 To jlrow
        Destlrow = Cells(Rows.Count, DestClm).End(xlUp).Row + 1
        Cells(Destlrow, DestClm) = Cells(j, StartClm) & Cells(j, StartClm + 1) & Cells(j, StartClm + 2)
    Next j
 
Next i

End Sub

this works perfectly, Thank you so much for all of your help. I really appreciate it! I can't put into words how much I really do appreciate it. You are awesome!

 

[Max1616]

Glad I could help!

 

[ostrdevi]

Glad I could help!

is it possible to have the code also grab the tare weight and put it in the cell next to the first output? so the final output for example would be, AAX0350fill in O2 and 506 in P2 because 506 is associated with the can's name, AAX0350?

 

[Max1616]

Try this:

Sub FillName()

Dim i As Long
Dim j As Long
Dim StartClm As Integer
Dim jlrow As Long
Dim DestClm As Integer
Dim Destlrow As Long
Dim LoopAmt As Integer

DestClm = 15 '15 represents Column O - Which will be the destination of the macro
LoopAmt = Application.CountIf(Range("1:1"), "ULD")

If LoopAmt = 0 Then Exit Sub

For i = 1 To LoopAmt
    StartClm = (i * 4) - 3
    jlrow = Cells(Rows.Count, StartClm).End(xlUp).Row
    
    For j = 2 To jlrow
        Destlrow = Cells(Rows.Count, DestClm).End(xlUp).Row + 1
        Cells(Destlrow, DestClm) = Cells(j, StartClm) & Cells(j, StartClm + 1) & Cells(j, StartClm + 2)
        Cells(Destlrow, DestClm + 1) = Cells(j, StartClm + 3)
    Next j
  
Next i

End Sub