Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative & Absolute References in Formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative & Absolute References in Formula
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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "DennisW" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert selected formula references from relative to absolute | Excel Discussion (Misc queries) | |||
absolute & relative references | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
how to switch between relative and absolute references | New Users to Excel | |||
Absolute / Relative References | Excel Programming |