Solved

Update VBA loop

Posted on 2015-01-19
4
70 Views
Last Modified: 2015-01-19
Have existing VBA would like to have it updated to address the potential scenario


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.

Thanks

===========
Prior related question: http:Q_28598192.html
0
Comment
Question by:jmac001
  • 2
4 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40558328
Try this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngReplace 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(Target, Range("S:S")) Is Nothing Then
    With Sheets("PARTS REQUEST")
        If LCase(Target) = "yes" Then
            .Cells(lngLastRow, 2) = Sheets("Sheet1").Cells(Target.Row, 2)
            .Cells(lngLastRow, 3) = Sheets("Sheet1").Cells(Target.Row + 5, 1)
        Else
            For lngRow = 9 To lngLastRow
                If .Cells(lngRow, 2) = Sheets("Sheet1").Cells(Target.Row, 2) Then
                    For lngReplace = lngRow To lngLastRow
                        If .Cells(lngReplace, 2) <> "" Then
                            .Cells(lngReplace, 2) = .Cells(lngReplace + 1, 2)
                            .Cells(lngReplace, 3) = .Cells(lngReplace + 1, 3)
                        Else
                            Exit For
                        End If
                    Next
                    Exit For
                End If
            Next
        End If
    End With
End If

End Sub

Open in new window

0
 

Author Closing Comment

by:jmac001
ID: 40558635
Thank you for thinking of what-if scenario. Works great
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40558637
You're welcome.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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

19 Experts available now in Live!

Get 1:1 Help Now