Variable in " " in excel

[Kingnight]

Hey,

this is what I am trying to do

For Index = LBound(k) To UBound(k)

b.Cells(a, 1) = Application.WorksheetFunction.Substitute(b.Cells(a, 1), "k(Index)", "")

Next Index

but the variable K(index) does not work in this formula because of " ". (only show test " k(index) ". How it could still work in this situation? I really appreciate it.


Thanks.

 

[Scott Huish]

What are you trying to accomplish exactly and why the worksheet function?

I also wouldn't use INDEX as a variable as it is a VBA keyword.

 

[Kingnight]

HOTPEPPER, you again.

I am trying to remove several strings in one string:

ex: Relating to the Nov 12, 2008 payment date
related to the Sep 5, 2009 distribution date
...
I only want dates: Nov 12, 2008 or Sep 5, 2009.

I am creating above formula (like filter) to remove "relating to the", "payment date", "related to the", "distribution date".

Hope it is clear.

 

[Rick Rothstein]

HOTPEPPER, you again.

I am trying to remove several strings in one string:

ex: Relating to the Nov 12, 2008 payment date
related to the Sep 5, 2009 distribution date
...
I only want dates: Nov 12, 2008 or Sep 5, 2009.

Assuming you only want to retrieve one date per string value, would this UDF (user defined function) be of any help to you (it retrieves the date directly)?

Function GetDate(StringIn As String) As Date
  Dim X As Long, MaybeDate As Variant, Parts() As String
  Parts = Split(StringIn)
  For X = 0 To UBound(Parts) - 4
    MaybeDate = Parts(X) & " " & Parts(X + 1) & " " & Parts(X + 2)
    If IsDate(MaybeDate) Then
      GetDate = CDate(MaybeDate)
      Exit Function
    End If
  Next
  GetDate = CVErr(xlErrValue)
End Function