Use INDEX MATCH combo to INSERT value - VBA

[raphads]

Hello.

I have a code that uses the index match combo to set the cells value into the userForm, it works as expected.

Now I'm strugling trying to write a code that insert value into cells that are right next to the ones where the values came from.

here's the code I'm working

ps = txtPS.Value
inicioreal = txtInicioReal.Value


Range(Application.WorksheetFunction.Index(Sheets("Projetos").Range("K1:k1000"), Application.WorksheetFunction.Match(ps, Sheets("Projetos").Range("A1:A1000"), 0))).Value = inicioreal

When I run it gets the "Runtime error 1004 - unable to get match property of worksheetfunction class" message.


So, I want to get a value from a userform and write this value on a cell using index match, 'cause it's a table that is updated frequently so I cannot use a fixed range.
Can anyone help me with this or tell me another way of achieving this?

 

[DanteAmor]

Hi and welcome to MrExcel.

According to your formula, I understand that you want to look in column A for the value that you have in txtPS and if it finds it, put the value txtInicioReal in column K.
If the above is correct, try like this:

Private Sub CommandButton1_Click()
  Dim ps As String, inicioreal As String
  Dim f As Range
  
  ps = txtPS.Value
  inicioreal = txtInicioReal.Value

  With Sheets("Projetos")
    Set f = .Range("A:A").Find(ps, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      .Range("K" & f.Row).Value = inicioreal
    Else
      MsgBox "Data ps does not exist"
    End If
  End With
End Sub

 

[raphads]

Hi and welcome to MrExcel.

According to your formula, I understand that you want to look in column A for the value that you have in txtPS and if it finds it, put the value txtInicioReal in column K.
If the above is correct, try like this:

Private Sub CommandButton1_Click()
  Dim ps As String, inicioreal As String
  Dim f As Range
 
  ps = txtPS.Value
  inicioreal = txtInicioReal.Value

  With Sheets("Projetos")
    Set f = .Range("A:A").Find(ps, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      .Range("K" & f.Row).Value = inicioreal
    Else
      MsgBox "Data ps does not exist"
    End If
  End With
End Sub

It works PERFECTLY!

Thanks for the quick reply and thanks for the solution!