Okay, now I've created a VBA routine that inserts this formula into those files that I mentioned in my first post. And it works great (thanks again). However, I feel certain that there's gotta be a better way to put that formula into VBA besides the way I am doing it.
I am seeking to get a formula like this in each cell
=INDEX('Grower ID'!$B$7:$B$186,MATCH("*"& SUBSTITUTE($D$4," ","*")&"*",'Grower ID'!$A$7:$A$186,0))
Here is how my tortured code looks.
For Each cell In rngGrowerNameList
If cell <> "" Then
cell.Offset(0, -1).Formula = "=INDEX('Grower ID'! $B$7:$B$186,MATCH(" & Chr(34) & "*" & Chr(34) & _
"& SUBSTITUTE(" & cell.Address & "," & Chr(34) & " " & Chr(34) & "," & Chr(34) & "*" & Chr(34) & ")&" & Chr(34) & _
"*" & Chr(34) & ",'Grower ID'!$A$7:$A$186,0))"
End If
Next cell
Again. It works, but it sure ain't pretty.
I never have figured out the best way to convert the quotation marks in a cell formula into a formula for VBA.
Am I right in thinking there must be a better way?
Thanks,
Jim