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.

 

[ostrdevi]

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

This worked Amazingly thank you so much for you help. Now I can focus more on my other big project I'm trying to learn. do you happen to know how to make some VBA code that (1) will read an input that is put into A1, (2) search through the second sheet that is kind of like a master sheet of a bunch of different information, (3) find the value on the mastersheet that matches the value in A1 and find the Tare weight associated with the can value in A1. (4) then take the tare weight from the sheet 2 "mastersheet" and put it into B1, (5) and then move the information in A1 and B1 down one row so that the next can value to be scanned appears in A1 and then the code repeats it again?

 

[Max1616]

This seems doable, could you send me a screenshot of what the master sheet looks like, and the name of the sheet?

Perhaps we should take this off the thread (since the original problem was solved for) and into a separate conversation?

 

[ostrdevi]

This seems doable, could you send me a screenshot of what the master sheet looks like, and the name of the sheet?

Perhaps we should take this off the thread (since the original problem was solved for) and into a separate conversation?

Yep, I can do all that. Ill name the new thread "How to use VBA to read data on one worksheet and imput data on another"