VBA FIND text question

[hitbid]

I am trying to return a name from a long line of text.

Below is a sample line:

Shipdate: 04/03/18 Stk Br # 1 Writer: Mark Smith Tax Jurisdiction: 1

Every line is edited the same every time, with a shipdate, writer, and tax jurisdiction. I am trying to extract the name of the writer in every line, which can be different.

Here I want to extract Mark Smith and paste it to a new sheet.

I understand the FIND function as a formula, but it isn't being as intuitive to me in VBA.

I had started to write something like this, but I am lost and not sure how to solve for this.

Sub Writer()Dim Writ As Variant
    FindIVZ
Cells.Find(What:="Writer:*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Writ = Application.WorksheetFunction.Find(":", "Writer:*")

This is all wrong I know. Any ideas?

 

[hitbid]

I think I figured it out.

I used this and it comes back with the name. Maybe not the absolute best code, but it works.

Sub Writer()Dim Writ As Double
Dim Tax As Double
Dim Wr As Variant


    FindIVZ
    
    Cells.Find(What:="Writer:*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Writ = InStr(ActiveCell, "Writer:")
    Tax = InStr(ActiveCell, "Tax Jur")
    Wr = Mid(ActiveCell, Writ + 8, Tax - (Writ + 8))
    
    MsgBox Wr


End Sub

 

[Peter_SSs]

Maybe not the absolute best code, but it works.

If it works, that's fine.
However, if you are interested, here is another way. (I gather from your code, we don't know what column the text is in each time?)

Sub Find_Writer()
  Dim WriterCell As Range
  
  Set WriterCell = Cells.Find(What:="Writer:", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
  If Not WriterCell Is Nothing Then
    MsgBox Trim(Split(Split(WriterCell.Value, "Writer:")(1), "Tax Jurisdiction:")(0))
  End If
End Sub

 

[hitbid]

Interesting, I have not used the split function, but reading up on it I am realizing I should, thank you.

Your's worked. So I tried to finish the subroutine by pasting in the final four lines, but getting an error on the last line, where I am trying to have the active cell value match the range value variable you just gave me.

    Sheets("Totals").Select    Range("B900000").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = WriterCell.Value

 

[Peter_SSs]

Interesting, I have not used the split function, but reading up on it I am realizing I should, thank you.

Your's worked. So I tried to finish the subroutine by pasting in the final four lines, but getting an error on the last line, where I am trying to have the active cell value match the range value variable you just gave me.

    Sheets("Totals").Select    Range("B900000").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = WriterCell.Value

1. What is the full code?

2. What is the full error message

3. What line is highlighted if you click debug when you get the error?

 

[jolivanes]

or with a formula

=MID(A2,FIND("Writer:",A2,1)+8,FIND(" Tax Jurisdiction",A2)-FIND("Writer:",A2,1)-8)