How to search a string and split it?

[zerfetzen]

Hi all,
I am using Excel for statistics. Each column contains descriptive statistics on a variable, including its name (let's say, row 1, and the current value of the variable in row 2). I'm trying to program it to search from left to right through the variable names for a variable name with _x_ in the middle of it. For example, it may find a variable called Age_x_Sex. When it finds a _x_, it should search for the variable name before it (Age) and after it (Sex), to find these variable locations. Once I have the locations, I can have it multiply the values of the variables together, reproducing the interaction effect.

Here's what I have so far:

Sub Main()
Dim CurCol As Integer 'Current Column
Dim TempVar1 As String
Dim TempVar2 As String
Dim TempVar3 As String
Dim Var_Num 'Number of Variables

For CurCol = 0 To Var_Num
TempVar1 = Range("A1").Offset(0,CurCol)
'Non-code begins
'Search TempVar1 for "_x_"
'Assign string prior to _x_ to TempVar2
'Assign string after _x_ to TempVar3
'Search list of variables for TempVar2, store its column location (like G)
'Search list of variables for TempVar3, store its column location (like M)
'Non-code ends
Range("A2").Offset(0,CurCol) = "=" & TempVar2 & "2*" & TempVar3 & "2"
Next Var_Num
End Sub

I'm new at searching strings. Help? Thanks.

 

[agihcam]

please post a few sample data.