VBA Help, for adding text to column

[jayserna]

Hello this code works fine except the text I want to add to column T, adds it all the way down the entire columns 100,000+ rows. I only want the text to be added in rows with data in column S. I have tried to fix it and I am sure it's an easy fix, but I have been unable to pin point the correction. Name code is last line at bottom.


Sub NeedHelp()
' MoveColumns Macro

' Description: Rearrange columns in Excel based on column header

Dim iRow As Long
Dim iCol As Long


'Constant values
data_sheet1 = "Original_" 'Create Input Box to ask the user which sheet needs to be reorganised
target_sheet = "Load" 'Specify the sheet to store the results
iRow = Sheets(data_sheet1).UsedRange.Rows.Count 'Determine how many rows are in use


'Create a new sheet to store the results
Worksheets.Add.Name = "Load" 'New Worksheet to load to EC

'Start organizing columns
For iCol = 1 To Sheets(data_sheet1).UsedRange.Columns.Count

'Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns
TargetCol = 0



Range("B1").Value = result
'Read the header of the original sheet to determine the column order
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item1" Then TargetCol = 1
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item2" Then TargetCol = 2
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item3" Then TargetCol = 3
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item4" Then TargetCol = 4
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item5" Then TargetCol = 5
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item6" Then TargetCol = 6
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item7" Then TargetCol = 7
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item8" Then TargetCol = 8
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item9" Then TargetCol = 9
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item10" Then TargetCol = 10
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item11" Then TargetCol = 11
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item12" Then TargetCol = 12
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item13" Then TargetCol = 13

'If a TargetColumn was determined (based upon the header information) then copy the column to the right spot
If TargetCol <> 0 Then
'Select the column and copy it
Sheets(data_sheet1).Range(Sheets(data_sheet1).Cells(1, iCol), Sheets(data_sheet1).Cells(iRow, iCol)).Copy Destination:=Sheets(target_sheet).Cells(1, TargetCol)
End If

Next iCol 'Move to the next column until all columns are read

Worksheets("Header").Range("1:1").Copy Worksheets("Load").Range("1:1") 'Copy + Paste Header
Columns("A:Z").EntireColumn.AutoFit 'Column Width Fix
Worksheets("Load").Range("T:T").Value = "Customer Name" 'Add Client Name

End Sub

 

[My Aswer Is This]

I would like for you to just tell us what your wanting to do and let us see if we can write you a script. And not want us to read this very long script and figure out how to fix it to do what you want.

 

[kelly mort]

I would like for you to just tell us what your wanting to do and let us see if we can write you a script. And not want us to read this very long script and figure out how to fix it to do what you want.

You have been knocking your head on your desk since i came here. What is that challenge you have?? Haha

 

[jayserna]

This is the only part that needs editing.

Worksheets("Load").Range("T:T").Value = "Customer Name" 'Add Client Name

i just need the code to check to see if there is data in column S if there is then add client name in column T. Right now data is added in column T all the way down.

 

[My Aswer Is This]

Try something like this:

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "T").End(xlUp).Row
Worksheets("Load").Range("T1:T" & Lastrow).Value = "Customer Name" 'Add Client Name

 

[jayserna]

Try something like this:

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "T").End(xlUp).Row
Worksheets("Load").Range("T1:T" & Lastrow).Value = "Customer Name" 'Add Client Name

That worked. Learn something new everyday! Thanks.

 

[Rick Rothstein]

'Read the header of the original sheet to determine the column order
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item1" Then TargetCol = 1
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item2" Then TargetCol = 2
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item3" Then TargetCol = 3
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item4" Then TargetCol = 4
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item5" Then TargetCol = 5
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item6" Then TargetCol = 6
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item7" Then TargetCol = 7
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item8" Then TargetCol = 8
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item9" Then TargetCol = 9
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item10" Then TargetCol = 10
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item11" Then TargetCol = 11
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item12" Then TargetCol = 12
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item13" Then TargetCol = 13

In case you are interested, because the number after the word "Item" is the same as the column number you want to assign to TargetCol, and assuming Sheets(data_sheet1).Cells(1, iCol).Value will only contain one of the listed "Item#" values, all of the above quote code lines can be replaced by this single line of code...

TargetCol = CLng(Mid(Sheets(data_sheet1).Cells(1, iCol).Value, 5))