Hi
I'm trying to figure out how to write code for the following:
I have an Excel table, which contains "cases". It has roughly 25 fields, filled through a userform with the following code:
Private Sub CmdEnterNewCase_Click() Dim ws As Worksheet Dim lo As ListObject Dim newrow As ListRow Set ws = Sheets("Sheet1") Set lo = ws.ListObjects("Tbl_Input") With lo .AutoFilter.ShowAllData End With Set newrow = lo.ListRows.Add(Position:=1) 'put a new role at the top of the table With newrow 'add the userform inputs to the new row - number = column number .Range(1) = TxtCaseName .Range(2) = Format(CDate(Me.TxtDate.Value), "DD-MMM-YYYY") 'CDate converts the text to a date .Range(3) = CmbType .Range(4) = CmbPrimaryLocation .Range(7) = TxtLocalLead .Range(8) = TxtHQLead .Range(9) = Format(CDate(Me.TxtDate.Value), "DD-MMM-YYYY") & " - " & TxtSummaryCase & Chr(10) & Chr(10) & Chr(13) _ & "==================================START OF CASE RECORDS==================================" _ & Chr(10) & Chr(10) & Chr(13) .Range(12) = "NEW CASE" .Range(13) = CmbPRIVATE5 .Range(14) = CmbPRIVATE6 .Range(15) = CmbPRIVATE7 .Range(19) = TxtPRIVATE8 .Range(22) = TxtOtherContacts .Range(25) = TxtPRIVATE9 .Range(26) = Format(TxtDate, "DD-MMM-YYYY") 'puts case creation date into latest file update field .Range(26).NumberFormat = "dd/mmm/yyyy" 'formats the date as dd-mmm-yyyy // removes timestamp .Range(27) = Now & " - " & "Case created" .Range(29) = CmbPRIVATE10 .Range(30) = CmbStatus .Range(34) = TxtOtherCaseID .Range(54) = TxtLink .Range.EntireRow.WrapText = False 'sets the whole row to no text wrapping .Range.EntireRow.Font.Bold = False 'turns off the bold text of new role (picked up from title row in table) Application.CutCopyMode = False 'change text in relevant field to hyperlinks - see sub below ToHyperlink End With 'unloads the form Unload Me 'Show messgae box that new case added successfully MsgBox "New case created", vbInformation EndRoutine: 'Optimize Code Application.ScreenUpdating = True Application.EnableEvents = True 'Clear The Clipboard Application.CutCopyMode = False 'Close the userform FrmNewCase.Hide End Sub
-/ I also have a module that adds "updates" to the table by matching the case name and adding a new row, with a few of the fields above filled in from a separate userform, e.g. date and "txtsummarycase". this module adds "CASE UPDATE" to .Range(12), where the new case module adds "NEW CASE"
-/ I would like to incorporate code that pulls data from the new case row into the update row - for instance, the location (.Range(4)) of the case, which won't change. So, the "case update" code needs to match the case name from the NEW CASE row, and pull data from that row
-/ I would normally do this with an Index/Match formula...
Any help very welcome
Phillipus