Sub From Yesterday

  • Thread starter Thread starter Josh in Tampa
  • Start date Start date
J

Josh in Tampa

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

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.
.
Click to expand...
Click to expand...
 
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

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.
.
Click to expand...
Click to expand...
 
Back
Top