Improvement to sub: Detect data extent and convert to values
Hi guys,
I'm trying to improve the sub below, so that: a) it auto-detects the extent of the data range in col A (A2:Ax, where x = last cell) and inserts the vlookup formula correspondingly into col C b) it will then auto-convert the returns in col C to values, after calculation Sub InsertFormulas() With Sheets("Book In").Range("C2:C100") ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = _ "=IF(RC1="""","""",VLOOKUP(RC1,PartNumbers!R2C1:R1 000C2,2,FALSE))" End With End Sub Thanks for insights -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- |
Improvement to sub: Detect data extent and convert to values
Hi
try Sub InsertFormulas() dim lastrow as long With Sheets("Book In") lastrow = .Cells(Rows.count, "A").End(xlUp).row end with with Sheets("Book In").Range("C2:C" & lastrow) ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = _ "=IF(RC1="""","""",VLOOKUP(RC1,PartNumbers! R2C1:R1000C2,2,FALSE))" 'convert formulas to values ..value=.value End With End Sub -----Original Message----- Hi guys, I'm trying to improve the sub below, so that: a) it auto-detects the extent of the data range in col A (A2:Ax, where x = last cell) and inserts the vlookup formula correspondingly into col C b) it will then auto-convert the returns in col C to values, after calculation Sub InsertFormulas() With Sheets("Book In").Range("C2:C100") ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = _ "=IF(RC1="""","""",VLOOKUP(RC1,PartNumbers! R2C1:R1000C2,2,FALSE))" End With End Sub Thanks for insights -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- . |
Improvement to sub: Detect data extent and convert to values
Works great!
Many thanks, Frank -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- |
Improvement to sub: Detect data extent and convert to values
sorry to butt in ... but some tidy up....
Sub InsertFormulas() dim lastrow as long dim sFormula as string sFormula="=IF(RC1="""","""",VLOOKUP(RC1, _ PartNumbers!R2C1:R1000C2,2,FALSE))" With Sheets("Book In") lastrow = .Cells(Rows.count, "A").End(xlUp).row with .Range(.Range("C2"),.Cells(lastrow,"C")) ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = sFormula 'convert formulas to values .value=.value End With END WITH End Sub :) -----Original Message----- Hi try Sub InsertFormulas() dim lastrow as long With Sheets("Book In") lastrow = .Cells(Rows.count, "A").End(xlUp).row end with with Sheets("Book In").Range("C2:C" & lastrow) ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = _ "=IF(RC1="""","""",VLOOKUP(RC1,PartNumbers! R2C1:R1000C2,2,FALSE))" 'convert formulas to values ..value=.value End With End Sub -----Original Message----- Hi guys, I'm trying to improve the sub below, so that: a) it auto-detects the extent of the data range in col A (A2:Ax, where x = last cell) and inserts the vlookup formula correspondingly into col C b) it will then auto-convert the returns in col C to values, after calculation Sub InsertFormulas() With Sheets("Book In").Range("C2:C100") ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = _ "=IF(RC1="""","""",VLOOKUP(RC1,PartNumbers! R2C1:R1000C2,2,FALSE))" End With End Sub Thanks for insights -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- . . |
Improvement to sub: Detect data extent and convert to values
Hi Patrick
no problem at all :-) -----Original Message----- sorry to butt in ... but some tidy up.... Sub InsertFormulas() dim lastrow as long dim sFormula as string sFormula="=IF(RC1="""","""",VLOOKUP(RC1, _ PartNumbers!R2C1:R1000C2,2,FALSE))" With Sheets("Book In") lastrow = .Cells(Rows.count, "A").End(xlUp).row with .Range(.Range("C2"),.Cells(lastrow,"C")) ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = sFormula 'convert formulas to values .value=.value End With END WITH End Sub :) -----Original Message----- Hi try Sub InsertFormulas() dim lastrow as long With Sheets("Book In") lastrow = .Cells(Rows.count, "A").End(xlUp).row end with with Sheets("Book In").Range("C2:C" & lastrow) ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = _ "=IF(RC1="""","""",VLOOKUP(RC1,PartNumbers! R2C1:R1000C2,2,FALSE))" 'convert formulas to values ..value=.value End With End Sub -----Original Message----- Hi guys, I'm trying to improve the sub below, so that: a) it auto-detects the extent of the data range in col A (A2:Ax, where x = last cell) and inserts the vlookup formula correspondingly into col C b) it will then auto-convert the returns in col C to values, after calculation Sub InsertFormulas() With Sheets("Book In").Range("C2:C100") ' Adjust the range in col C to suit ' the max likely number of data rows in col A .FormulaR1C1 = _ "=IF(RC1="""","""",VLOOKUP(RC1,PartNumbers! R2C1:R1000C2,2,FALSE))" End With End Sub Thanks for insights -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- . . . |
Improvement to sub: Detect data extent and convert to values
Hey, that works great, too!
Thanks for the "tidy up", Patrick I'm going to try and grasp the refinements made .. -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- |
All times are GMT +1. The time now is 06:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com