Solved

VBA Loop with multiple criteria

Posted on 2015-01-16
7
310 Views
Last Modified: 2015-01-19
Hi Experts,

After recording what I would like to be done I realized it will only work on a loop and I am horrible with writing loops.  I would like to populate columns B and C on the Parts Request tab when on Sheet 1 Column S has yes.   If you look at the attachment on Sheet1 S6 is yes no I would like to see B6 the Item # populated to B15 on the Parts Request tab and then  A11 (Sheet1) would be populated to C15 on the Parts Request.

Is it also possible to have this automatically populate on the Parts Request tab once "Yes" is selected (from Column s).  The example has 37 items populated but the items that may require action can be less or greater.
EE-Help-Snag-2015.01.16.xls
0
Comment
Question by:jmac001
  • 4
  • 3
7 Comments
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40554303
Put this code in sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLastRow As Long
Dim lngRow As Long
With Sheets("PARTS REQUEST")
    For lngRow = 9 To .UsedRange.Rows.Count
        If .Cells(lngRow, 3) = "" Then
            lngLastRow = lngRow
            Exit For
        End If
    Next
End With
If lngLastRow = 0 Then
    MsgBox "Too much data is already on the PARTS REQUEST sheet. Add more lines"
    Exit Sub
End If
If Not Intersect(ActiveCell, Range("S:S")) Is Nothing Then
    If LCase(Target) = "yes" Then
        Sheets("PARTS REQUEST").Cells(lngLastRow, 2) = ActiveSheet.Cells(Target.Row, 2)
        Sheets("PARTS REQUEST").Cells(lngLastRow, 3) = ActiveSheet.Cells(Target.Row + 5, 1)
    End If
End If

End Sub

Open in new window

0
 

Author Closing Comment

by:jmac001
ID: 40554521
Works wonderfully
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40554547
Look ma, no loops:)

Seriously, you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 46

Expert Comment

by:Martin Liss
ID: 40554680
I was just thinking about your workbook and I can think of two possible problems.

1) If the user changes his mind after selecting 'yes' and changes it to 'no' or 'n/a', the data will still be in the parts request sheet.

2) To take it a step further if he does yes then no and then yes again, the information will be there twice.

Would you like the code modified to handle those situations?
0
 

Author Comment

by:jmac001
ID: 40557592
Yes, please
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40557841
Since I answered your original question would you consider opening a new one?
0
 

Author Comment

by:jmac001
ID: 40558296
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question