Solved

VBA Loop with multiple criteria

Posted on 2015-01-16
7
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 47

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 47

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 47

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 47

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

Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

Question has a verified solution.

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

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 article describes a serious pitfall that can happen when deleting shapes using VBA.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

752 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