Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

VBA Loop with multiple criteria

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
jmac001
Asked:
jmac001
  • 4
  • 3
1 Solution
 
Martin LissOlder than dirtCommented:
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
 
jmac001Author Commented:
Works wonderfully
0
 
Martin LissOlder than dirtCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Martin LissOlder than dirtCommented:
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
 
jmac001Author Commented:
Yes, please
0
 
Martin LissOlder than dirtCommented:
Since I answered your original question would you consider opening a new one?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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