• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Finding a value from a variable on a sheet and marking a Column

Excel 2003
vba routine needed

I have some code that sorts a sheet based another sheets value.

What I have:
A sheet Named "List"
A sheet Named "Sheet2"

some code...

Sheets("Sheet2").Activate
   c = Sheets("Sheet2").Range("K1").Value
   w = Sheets("Sheet2").Range("L1").Value

If c = "SKU" Then
   
   Sheets("List").Activate
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
     
End If

If c = "MFRNUM" Then
   
   Sheets("List").Activate
    Cells.Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
     
End If

What I need:
on Sheet "List" there are several columns.

I need to use the variable  "w" from Sheet2
Look in Column E on Sheet "List"
If the Value "w"  Exists  mark Column Q on SHEET "List" with the word   "Yes" for that row.

Example:
w = "Toyota"

A     ....        E             .....           Q
121       FORD                        
32A       TOYOTA                    YES
342       TOYOTA                    YES
212       TOYOTA CARS           YES
RR4       CHEVY                          
EE3       SATURN                        
RR1       TOYOTAS                  YES

So Once it finds and marks the Q column..

I need to sort  Sheet "List" first Descending in Column Q and then  BY THE CODE ABOVE depending on what "c" is

If  c =  "Sku"

THE RESULT WOULD BE

A             E             .....           Q
212       TOYOTA CARS           YES
32A       TOYOTA                    YES
342       TOYOTA                    YES
RR1       TOYOTAS                  YES
121       FORD                        
EE3       SATURN                        
RR4       CHEVY                          



Thanks
fordraiders


0
Fordraiders
Asked:
Fordraiders
  • 2
1 Solution
 
StephenJRCommented:
Post a workbook perhaps?
0
 
Davy2270Commented:
Assuming that your list in worksheet List goes up to column Q, you should try this code:

 
Sub test()
Dim errorhandler
   
Sheets("List").Activate
   c = Sheets("Sheet2").Range("K1").Value
   w = Sheets("Sheet2").Range("L1").Value

If c = "SKU" Then
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If

If c = "MFRNUM" Then
    Cells.Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If

'find variable w in sheet List column E, put YES in column Q when found
On Error GoTo errorhandler 'If variable w is not found then stop the code
Columns("E:E").Select
    Selection.Find(What:="" & w & "", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Do Until ActiveCell.Offset(0, 12).Value = "YES"
        ActiveCell.Offset(0, 12).Value = "YES"
        Selection.FindNext(After:=ActiveCell).Activate
    Loop

'sort the list
Cells.Select
    ActiveWorkbook.Worksheets("List").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("List").Sort.SortFields.Add Key:=Range("Q:Q"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("List").Sort.SortFields.Add Key:=Range("C:C"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("List").Sort
        .SetRange Range("A:Q")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

errorhandler:
Exit Sub
End Sub

Open in new window


If your list is wider than column Q then adjust the code in line 41 to the appropriate end column
0
 
FordraidersAuthor Commented:
davy2270, Still checking sorry
0
 
FordraidersAuthor Commented:
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

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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