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
----

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
----


.

Works great!

Many thanks, Frank

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

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
----


.

.

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
----


.

.

.

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
----