• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • Last Modified:

Update VBA loop

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
jmac001
Asked:
jmac001
  • 2
1 Solution
 
Martin LissRetired ProgrammerCommented:
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
 
jmac001Author Commented:
Thank you for thinking of what-if scenario. Works great
0
 
Martin LissRetired ProgrammerCommented:
You're welcome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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