Changing Data in Row using a userform and vlookup

[skidda420]

Hello all, I've configured the following workbook to manage our IT workorders and create a ticketing system. Everything I have is working with exception to closing a ticket. I've tried wrapping my head around this one and keep drawing a blank and I'm hoping someone here will have a revelation as this is my only hold up in the project. The main purpose is the system knows when there's an open ticket, the user clicks 'close/finish ticket' (1a) and is prompted to type in the ticket number (1b), after clicking 'ok' using vba the system pulls up the prior information antered into a userform (1c). The problem I'm having is on the submit information button is to be able to adjust the data in the corresponding row to match the new data that is entered in the userform. Help! I've tried multiple ways of doing this and keep coming up empty handed.

 

[skidda420]

this is the userform initialization i have in place so far,not sure if it really makes a difference or not.

Private Sub UserForm_Initialize()
On Error Resume Next
Dim notes As Variant
Dim APP As Variant
Dim descrip As Variant
Dim TS As Variant
Dim Tech As Variant
notes = Application.VLookup(Sheet6.Range("e4"), Sheet6.Range("b7:aa1000"), 18, False) & vbLf & "*Additional Notes:*"
APP = Application.VLookup(Sheet6.Range("e4"), Sheet6.Range("b7:aa1000"), 2, False)
descrip = Application.VLookup(Sheet6.Range("e4"), Sheet6.Range("b7:aa1000"), 6, False) & vbLf & "*Additional Notes:*"
TS = Application.VLookup(Sheet6.Range("e4"), Sheet6.Range("b7:aa1000"), 13, False) & vbLf & "*Additional Notes:*"
Tech = Application.VLookup(Sheet6.Range("e4"), Sheet6.Range("b7:aa1000"), 24, False)

Notestxt.Value = notes
APPcombobox.Value = APP
Descriptiontxt.Value = descrip
Troubleshootingtxt.Value = TS
ResolveCombobox.Value = ""
OpenTicketfrm.Label12.Caption = Sheet6.Range("e4").Value
Numbertxt.Value = ""
TimeComboBox.Value = ""
Techtxt.Value = Sheet4.Range("f1").Value
StartTechtxt.Value = Tech

With TimeComboBox
.AddItem "Minute(s)"
.AddItem "Hour(s)"
.AddItem "Day(s)"
.AddItem "Week(s)"
End With

With ResolveCombobox
.AddItem "Yes"
.AddItem "No"
.AddItem "Active"
End With
On Error GoTo 0
End Sub

 

[skidda420]

....so I never received a response to this, however after due diligence and some intensive googling I found somewhat of a solution online and constructed it to my needs. It works beautifully, so in case someone else ever runs into a similar issue with changing row data by looking up values. A combination of range.find and offsetting did the job.

Private Sub Finishcmd_Click()


Dim MyRange As Range
Dim MyRange2 As Range
Dim MyRange3 As Range
Dim MyRange4 As Range
Dim MyRange5 As Range
Dim MyRange6 As Range
Dim MyRange7 As Range
Dim MyRange8 As Range
Dim MyRange9 As Range
Sheet6.Unprotect Password:="password"
Sheet8.Visible = False
Sheet9.Visible = False
Sheet6.Visible = True
Sheet6.Activate
Sheet6.Select
ActiveWindow.Zoom = 75




Ticket = Sheet6.Range("e4").Value
Set MyRange = Sheet6.Range("B:B").Find(Ticket)

Set MyRange2 = MyRange.Offset(0, 6)

Set MyRange3 = MyRange2.Offset(0, 1)

MyRange3 = Troubleshootingtxt.Value
Set MyRange4 = MyRange3.Offset(0, 1)

MyRange4 = Notestxt.Value
Set MyRange5 = MyRange4.Offset(0, 1)


MyRange5 = ResolveCombobox.Value
Set MyRange6 = MyRange5.Offset(0, 4)


Set MyRange7 = MyRange6.Offset(0, 1)

MyRange7 = Techtxt.Value
Set MyRange8 = MyRange7.Offset(0, 1)

MyRange8 = Numbertxt.Value & " " & TimeComboBox.Value
Set MyRange9 = MyRange8.Offset(0, 1)

MyRange9 = "=today()"

If MyRange5.Value = "Yes" Then
MyRange6.Value = ""
Else
MyRange6.Value = 1
End If



Sheet6.Protect Password:="password"
If ResolveCombobox.Value = "No" Then
Call Sheet6.Unresolved
End If
If ResolveCombobox.Value = "Yes" Then
MsgBox "Setting ticket " & Ticket & "'s status as resolved and completed.", vbInformation, "Closing Work Order"
End If
Unload Me
ThisWorkbook.Save
End Sub