Relative & Absolute References in Formula

[Guest]

I'm imorting a TXT file into 2 columns where the no. of rows is not known.
The 2nd column has numeric values. I'd like to write a macro that replaces
the values in Col 2 to formulas based on the value in Col 1 such that the
formula in Col2 becomes the imported Col 2 value multiplied by a cell
reference. For example:

Import Table Revised Table
Col 1 Col 2 Col 1 Col 2
A 10 A = 10 * C1
A 14 A = 14 * C1
C 5 C = 5 * C3
D 7 D = 7 * C4
B 3 B = 3 * C2
B 25 B = 25 * C2

 

[Guest]

Try this one, imported data in columns a:b and no headings.
the formula is assuming a=1, b=2, c=3, d=4......:

Sub convert2formula()

Dim szAns As String, szCell
Dim lRowStart As Long, lRowEnd As Long, lRow As Long

With ActiveSheet.UsedRange
lRowStart = .Row
lRowEnd = lRowStart + .Rows.Count - 1
End With

For lRow = lRowStart To lRowEnd
With ActiveSheet.Cells(lRow, 2)
szAns = "=" & .Value & "*C" & _
Asc(UCase(.Offset(0, -1))) - Asc("A") + 1
.Formula = szAns
End With
Next lRow

End Sub