Save input for columns and use in code

I have data that will always have different data in different columns.
I would like to start the code by asking the user to identify the columns that the data is in, then saving that input and using it in the code.

For instance, I would like the user to identify the column with 1. rated weight 2. zone 3. date delivered.

The code would then use this input to 1. change the header names 2. use the rated weight to select packages => a quantity in a
given cell. Example, select all rows where the weight is >= quantity found in cell Sheet 2!A3. Copy those rows with headers to a second sheet.

THANKS!!
sample-data.xlsx
Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Will the column names always be the same no matter where they are?

You mention a "date delivered" column. Did you mean "date shipped"?

Will the new column names always be the same?
0
Euro5Author Commented:
No, they would be not be the same. This data would always be present, but not in same columns or same header names. This is because the customers data comes in varied formats. That is why I need to the user to respond to questions - what column is rated weight in? what column is the zone in? what column is the delivery date in? and then save the input for later use.
0
Euro5Author Commented:
Sorry - the new column names will always be the same.
Yes, the date shipped.
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

aikimarkCommented:
This code will put the filtered results in Sheet3
Sub Q_28666097()
    Dim rng As Range
    Dim rngCols(0 To 2) As Range
    Dim vColPrompt As Variant
    Dim lngCol As Long
    Dim strMsg As String
    Dim intReply As VbMsgBoxResult
    
    vColPrompt = Array("rated weight", "zone", "date delivered")
    '1. rated weight 2. zone 3. date delivered.
    On Error Resume Next
    For lngCol = 0 To 2
        Set rngCols(lngCol) = Application.InputBox("Please click on a cell in the " & vColPrompt(lngCol) & " column", , , , , , , 8)
    Next
    On Error GoTo 0
    For lngCol = 0 To 2
        If rngCols(lngCol) Is Nothing Then
            MsgBox "Nothing selected for the " & vColPrompt(lngCol) & " column" & vbCr & "Please try again"
            Exit Sub
        Else
            strMsg = strMsg & vColPrompt(lngCol) & " column: " & rngCols(lngCol).Column & " (" & rngCols(lngCol).EntireColumn.Cells(1, 1).Value & ")" & vbCr
        End If
    Next
    intReply = MsgBox("Use these columns?" & vbCr & vbCr & strMsg, vbYesNo, "Column selection confirmation")
    If intReply = vbNo Then
        MsgBox "Please try again"
        Exit Sub
    End If
    'Debug.Print strMsg
    'rename selected columns
    For lngCol = 0 To 2
        rngCols(lngCol).EntireColumn.Cells(1, 1).Value = vColPrompt(lngCol)
    Next
    'create criteria range
    Set rng = Sheets("Sheet1").Range("a1").End(xlToRight).Offset(0, 2)
    rng.Value = vColPrompt(0)
    rng.Offset(1, 0).Formula = "="">=""&" & "Sheet2!A3"
    rng.Offset(1, 0).Calculate
    
    Sheets("Sheet1").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Sheet1").Range(rng, rng.Offset(1)), CopyToRange:=Range("sheet3!A1"), Unique:=False
    
    Sheets("Sheet1").Range(rng, rng.Offset(1)).Value = vbNullString
End Sub

Open in new window


Why are you renaming the columns?
0
aikimarkCommented:
Why are you selecting three columns, but only filtering on one of the columns?
0
Euro5Author Commented:
Later, I will want to pivot multiple columns, so I need the column names to be known.
0
Euro5Author Commented:
Error at

    Set rng = Sheets("Sheet1").Range("a1").End(xlToRight).Offset(0, 2)

"subscript out of range"
0
Euro5Author Commented:
I am getting an error 400 trying to run.
It is really critical that I get this project completed.

The code does change the header names, but it does not filter the data or copy it to a new sheet for further processing.
Can anyone help?

I attached the workbook.

Thanks!!
MWT-TOOL-v3.xlsm
0
aikimarkCommented:
Do you have a worksheet named Sheet1?
0
Euro5Author Commented:
There are only two sheets in the workbook. Sheet1 & Sheet2.
Is this a problem?
0
Euro5Author Commented:
It does not create a sheet3 and put the filtered results in.
Can someone help?
0
aikimarkCommented:
create the sheet3 worksheet manually and test the code
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
Got it, thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.