Solved

VBA Loop with multiple criteria

Posted on 2015-01-16
7
301 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 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
Comment Utility
Works wonderfully
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
Yes, please
0
 
LVL 45

Expert Comment

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

Author Comment

by:jmac001
Comment Utility
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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

6 Experts available now in Live!

Get 1:1 Help Now