Solved

VBA Loop with multiple criteria

Posted on 2015-01-16
7
305 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now