i got some help yesterday from some of you good people on
building a macro that would allow me to use a function
i've built to pull last names from full names. i'd like
the macro to insert a new column and use my function to
fill that new column with the last names of all the full
names in the first column.

i'm getting an error when i try to run this macro.

here it is:

Sub Macro1()

Dim iRow As Long
Dim rng As Range
Columns("B:B").Insert Shift:=xlToRight
Range("B1").FormulaR1C1 = "=PERSONAL.xls!getlastname(RC
[-1])"
With ActiveSheet
iRow = .Range("A" & Rows.Count).End(x1Up).Row
Set rng = .Range("B2:B" & iRow)
rng.FormulaR1C1 = "PERSONAL.xls!getlastname(RC[-1])"
End With

End Sub

after i run it, there is a new column inserted, and the
top record has the last name pulled out of the full name
and inserted into the new column.......and then the
error. the remainder of the column is empty.

the error states:

run time error '1004':

Application-defined or object-defined error

any ideas? thanks.

i substituted this one in, and it worked just fine!!

Sub Macro1()

Dim iRow As Long
Dim rng As Range
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.xls!getlastname(RC
[-1])"
iRow = ActiveSheet.Range("A65536").End(xlUp).Row
Set rng = ActiveSheet.Range("B2:B" & iRow)
rng.FormulaR1C1 = "=PERSONAL.xls!getlastname(RC[-1])"

End Sub

so, that's great. i really appreciate the help!

now another question.....if there's anyone out there with
an ear for me to lean on......

i need to set it up so that the entries in this long new
column are actual values and not function calls. any help
out there with this? thanks.
-----Original Message-----
i got some help yesterday from some of you good people on
building a macro that would allow me to use a function
i've built to pull last names from full names. i'd like
the macro to insert a new column and use my function to
fill that new column with the last names of all the full
names in the first column.

i'm getting an error when i try to run this macro.

here it is:

Sub Macro1()

Dim iRow As Long
Dim rng As Range
Columns("B:B").Insert Shift:=xlToRight
Range("B1").FormulaR1C1 = "=PERSONAL.xls!getlastname
(RC
[-1])"
With ActiveSheet
iRow = .Range("A" & Rows.Count).End(x1Up).Row
Set rng = .Range("B2:B" & iRow)
rng.FormulaR1C1 = "PERSONAL.xls!getlastname(RC[-1])"
End With

End Sub

after i run it, there is a new column inserted, and the
top record has the last name pulled out of the full name
and inserted into the new column.......and then the
error. the remainder of the column is empty.

the error states:

run time error '1004':

Application-defined or object-defined error

any ideas? thanks.
.

Re "xlUp", the 2nd char is a letter, not a number.

HTH,
Merjet

To change the formula in a cell to an actual value, copy the cell and paste
it back as value. You can record a macro to see how it's done in code.

schreef in bericht
...
i substituted this one in, and it worked just fine!!

Sub Macro1()

Dim iRow As Long
Dim rng As Range
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.xls!getlastname(RC
[-1])"
iRow = ActiveSheet.Range("A65536").End(xlUp).Row
Set rng = ActiveSheet.Range("B2:B" & iRow)
rng.FormulaR1C1 = "=PERSONAL.xls!getlastname(RC[-1])"

End Sub

so, that's great. i really appreciate the help!

now another question.....if there's anyone out there with
an ear for me to lean on......

i need to set it up so that the entries in this long new
column are actual values and not function calls. any help
out there with this? thanks.
-----Original Message-----
i got some help yesterday from some of you good people on
building a macro that would allow me to use a function
i've built to pull last names from full names. i'd like
the macro to insert a new column and use my function to
fill that new column with the last names of all the full
names in the first column.

i'm getting an error when i try to run this macro.

here it is:

Sub Macro1()

Dim iRow As Long
Dim rng As Range
Columns("B:B").Insert Shift:=xlToRight
Range("B1").FormulaR1C1 = "=PERSONAL.xls!getlastname
(RC
[-1])"
With ActiveSheet
iRow = .Range("A" & Rows.Count).End(x1Up).Row
Set rng = .Range("B2:B" & iRow)
rng.FormulaR1C1 = "PERSONAL.xls!getlastname(RC[-1])"
End With

End Sub

after i run it, there is a new column inserted, and the
top record has the last name pulled out of the full name
and inserted into the new column.......and then the
error. the remainder of the column is empty.

the error states:

run time error '1004':

Application-defined or object-defined error

any ideas? thanks.
.

Sub Macro1()

Dim iRow As Long
Dim rng As Range
Columns("B:B").Insert Shift:=xlToRight
iRow = ActiveSheet.Range("A65536").End(xlUp).Row
Set rng = ActiveSheet.Range("B1:B" & iRow)
rng.FormulaR1C1 = "=PERSONAL.xls!getlastname(RC[-1])"
rng.Formula = rng.Value
End Sub

--
Regards,
Tom Ogilvy

wrote in message
...
i substituted this one in, and it worked just fine!!

Sub Macro1()

Dim iRow As Long
Dim rng As Range
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.xls!getlastname(RC
[-1])"
iRow = ActiveSheet.Range("A65536").End(xlUp).Row
Set rng = ActiveSheet.Range("B2:B" & iRow)
rng.FormulaR1C1 = "=PERSONAL.xls!getlastname(RC[-1])"

End Sub

so, that's great. i really appreciate the help!

now another question.....if there's anyone out there with
an ear for me to lean on......

i need to set it up so that the entries in this long new
column are actual values and not function calls. any help
out there with this? thanks.
-----Original Message-----
i got some help yesterday from some of you good people on
building a macro that would allow me to use a function
i've built to pull last names from full names. i'd like
the macro to insert a new column and use my function to
fill that new column with the last names of all the full
names in the first column.

i'm getting an error when i try to run this macro.

here it is:

Sub Macro1()

Dim iRow As Long
Dim rng As Range
Columns("B:B").Insert Shift:=xlToRight
Range("B1").FormulaR1C1 = "=PERSONAL.xls!getlastname
(RC
[-1])"
With ActiveSheet
iRow = .Range("A" & Rows.Count).End(x1Up).Row
Set rng = .Range("B2:B" & iRow)
rng.FormulaR1C1 = "PERSONAL.xls!getlastname(RC[-1])"
End With

End Sub

after i run it, there is a new column inserted, and the
top record has the last name pulled out of the full name
and inserted into the new column.......and then the
error. the remainder of the column is empty.

the error states:

run time error '1004':

Application-defined or object-defined error

any ideas? thanks.
.