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 |
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 |
All times are GMT +1. The time now is 05:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com