How to make the VBA code I have loop

ostrdevi

New Member
Joined
Apr 25, 2022
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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.

1650925503678.png
1650925089305.png
 

Attachments

  • 1650925339319.png
    1650925339319.png
    35.3 KB · Views: 1
  • 1650925361583.png
    1650925361583.png
    28.4 KB · Views: 1
  • 1650925400053.png
    1650925400053.png
    28.4 KB · Views: 1

ostrdevi

New Member
Joined
Apr 25, 2022
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Try this:
VBA Code:
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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Max1616

Well-known Member
Joined
Nov 25, 2015
Messages
514
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 25, 2022
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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"
 

Forum statistics

Threads
1,176,122
Messages
5,901,505
Members
434,898
Latest member
dez091

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top