• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • 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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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