Looped formula macro to populate a table

[princess21]

Hi there,

I'm new to macros and am a bit stuck with this one.

I need a looped formula macro for a large data table.
I will try and illustrate my boggle with the following example.
I workbooks called is "farm.xls", the worksheet name is "fieldtype"
<TABLE dir=ltr cellSpacing=0 cellPadding=2 width=336 border=0><TBODY><TR><TD width="20%" height=16>
</TD><TD width="20%" height=16>A
</TD><TD width="20%" height=16>B
</TD><TD width="20%" height=16>C
</TD><TD width="20%" height=16>D
</TD></TR><TR><TD width="20%" height=16>

1​

​

​

​

​

</TD><TD width="20%" height=16>FieldA
</TD><TD width="20%" height=16>Stock
</TD><TD width="20%" height=16>Count
</TD><TD width="20%" height=16>Item
</TD></TR><TR><TD width="20%" height=16>

2​

​

​

​

​

</TD><TD width="20%" height=16>North
</TD><TD width="20%" height=16>10 ****erel
</TD><TD width="20%" height=16>10
</TD><TD width="20%" height=16>****erel
</TD></TR><TR><TD width="20%" height=16>

3​

​

​

​

​

</TD><TD width="20%" height=16>North
</TD><TD width="20%" height=16>50 Hens
</TD><TD width="20%" height=16>50
</TD><TD width="20%" height=16>Hens
</TD></TR><TR><TD width="20%" height=16>

4​

​

​

​

​

</TD><TD width="20%" height=16>West
</TD><TD width="20%" height=16>
</TD><TD width="20%" height=16>
</TD><TD width="20%" height=16>
</TD></TR><TR><TD width="20%" height=16>

5​

​

​

​

​

</TD><TD width="20%" height=16>South
</TD><TD width="20%" height=16>200 Eggs
</TD><TD width="20%" height=16>200
</TD><TD width="20%" height=16>Eggs
</TD></TR><TR><TD width="20%" height=16>

5​

​

​

​

​

</TD><TD width="20%" height=16>South
</TD><TD width="20%" height=16>5 Huts
</TD><TD width="20%" height=16>5
</TD><TD width="20%" height=16>Huts
</TD></TR></TBODY></TABLE>


Columns A and B (FieldA and Stock) are prepopulated. I have written excel formulas in columns C and D to extract the constituents of column B. The following excel formulas were used.
Column C =IF(B2<>0,MID(B2,1,FIND(" ",B2,1)-1),"")
Column D =IF(B2<>0,MID(B2,FIND(" ",B2,1)+1,LEN(B2)-FIND(" ",B2,1)),"")
The MID function retrieves the relevant information and the IF function misses out the blank rows.
The real table is a live updating sheet and is much much larger. I really need a looped macro that has can scan through the worksheet populate column C and D with an equivalent respective formula.
I have been playing around with the macro recorder and came up with this macro:
Sub Farmlife()
Windows("farm.xls").Activate
Sheets("fieldtype").Select
Cells.Find("FieldA").Offset(0, 1).Select 'finds the cell containing FieldA
Do Until ActiveCell = ""
If ActiveCell <> "" And ActiveCell.Offset(0, 1) <> "" Then
ActiveCell.Offset(0, 2).FormulaR1C1 = "=MID(RC[1],1,FIND("" "",RC[1],1)-1)"
ActiveCell.FormulaR1C1 = _
"=IF(R[1]C[-2]<>0,MID(R[1]C[-2],FIND("" "",R[1]C[-2],1)+1,LEN(R[1]C[-2])-FIND("" "",R[1]C[-2],1)),"""")"
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

Ok, that's all I have so far. Any help would be greatly appreciated.

 

[nightcrawler23]

Isn't your fieldA always in "A1"? Isn't your Row1 and ColA Fixed?

 

[princess21]

Ni Nightcrawler23,
Yes fieldA always in "A1"? Correct the whole of Row1 are fixed and just column headings. Essentially what I trying to figure out how to write a MID, LEN and FIND formulas in a macro.

 

[nightcrawler23]

Why dont you record the macro to drag down(Autofill) your formulas in Cols C and D.? Wil that work for you.

 

[princess21]

Thank you