• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 25
  • Last Modified:

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
0
Euro5
Asked:
Euro5
  • 8
  • 4
1 Solution
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
Euro5Author Commented:
Got it, thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now