Index match using VBA

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

Thanks - I'm afraid the file has confidential info in it and it's hard to sanitise...

I tried something like .Range(4)=appplication.worksheetfunctions(INDEX....) - but I couldn't get it to work... Is that the right ay to think about it?

Thanks again

I get an error - cannot find the worksheet function index...

Hi this is the code i tried

Sub CmdEnterAddUpdate_click()


Dim ws3 As Worksheet
Dim lo As ListObject
Dim newrow As ListRow
Dim lastrow As Long
Dim r As Long 'This is the counter
Dim n As Long


Set ws3 = Sheets("live cases input")
Set lo = ws3.ListObjects("Tbl_Live_Cases_Input")


With lo
.AutoFilter.ShowAllData
End With


Set newrow = lo.ListRows.Add(Position:=1) 'put a new role at the top of the table


'Turn off sheet updating
Application.ScreenUpdating = False


With newrow 'add the userform inputs to the new row - number = column number
.Range(1) = CmbSelectEntity
.Range(2) = Format(txtdateadded, "DD-MMM-YYYY")
.Range(9) = Format(txtdateadded, "DD-MMM-YYYY") & " - " & TxtCaseUpdate & Chr(10) & Chr(10) & _
Chr(13) & "**********"
.Range(12) = "CASE UPDATE"
.Range(26) = Format(txtdateadded, "DD-MMM-YYYY")
.Range(4) = Application.WorksheetFunction.Index(lo, Application.WorksheetFunction.Match(CmbSelectEntity, lo.ListColumns(1), 0), 4)


.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)


End With

I get "1004: Unable to get the Match property of the worksheet function class"

Also need to figure out how to make sure it pulls from the right row (with "NEW CASE" in .range(12))...

Sorry - not sure what you mean...

thanks - same error though...

tried with .range(4)=Application.WorksheetFunction.Match(CmbSelectEntity, lo.ListColumns(1), 0)
and got the same error...

hi - yes, i wrote the macro (adapted from code i found online)...

cmbselectentity is text and gets placed in the first column of lo, with the macro (.range(1)) - inputted through the userform

i should mention, the vales in the first column are not unique - there are multiple rows where cmbselectentity is "Case 1" (for example)... hence i need to also refer to range(12) where i note if it's a "NEW CASE" or "CASE UPDATE" - newrow needs to pull data from the row with "NEW CASE" in it...

i tried:

test = Application.Match(CmbSelectEntity, lo.ListColumns(1), 0)
MsgBox test

and got a type mismatch - hovering over cmbselectentity shows it's pulling the right value/text... does that help debug?