Macro Polo

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

Josh in Tampa

okay.....i like to think that i've used Excel for years,
and that i know it like the back of my hand....BUT....i'm
just getting started in a new job that involves using
Excel in a way that has really exposed some of its
complexities.

i have been asked to automate a few processes that one of
the departments here in my organization perform on a
regular basis in Excel. the staff opens up an Excel
spreadsheet, and they basically perform string
manipulation on most of the fields.....manually. i have
been working on figuring out how to automate this.

the coding itself was pretty easy for the most part. i
opened up the VBE and away i went. it didn't take long
before i was able to build a few "user defined functions"
that accomplish the same things that the staff was doing
manually.

what i need to do now is figure out how to implement a
macro that utilizes the few functions that i've written.

let me spell out an example for you:

A B
Bob Smith 123 Rocky Rd
Lisa Lowe 1322 My Lane
Dr. Harry Hower 987 Chimp St
Jim B. Kimson 773 Cayuga St
Lloyd Sowers IV 98 Hamner Pl

two columns.......what is required is that the last name
be pulled out of column A and inserted into a new column
inbetween A and B.

so, easy enough, i suppose.......i INSERT a new column
inbetween A and B. then, i type "=getlastname(a1)" into
the new b1 cell. poof......my function works and Smith
appears in the cell. then i copy and paste the contents
of b1 down the entire column. i need to automate
this.......AND

i would also like to be able set it up so that when i
enter in a new value in column A, that column B
automatically pulls the last name. the way it's set up
now, i have to copy and paste the function down into the
new column b cell.

now, i've recorded a macro while i ran through the process
i described above. then, to test the macro, i deleted the
column b, added a couple new records to column a, and ran
the macro. the result was that i got the last names
populated, but only so many as were done when i recorded
the macro. the new records had no last names in column b.

whew.....this has been a run on sentence from hell, and
i'm sorry if i'm going overboard.

i'm taking a book home to read this evening, and
hopefully, by tomorrow i will have a deeper understanding.

thank you in advance for any help you can provide!!!!


josh in tampa
 
Josh,

You've come a long way!

Time to learn about event macros.

Right click on the worksheet tab.
Select view code. (look familiar?)
Now there are 2 dropdowns at the top of the code panel:
Left should be change to 'Worksheet'
Change the right to 'Change' - a code will start
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
* your code goes here *
End Sub

You may want to restrict this to see only changes in column A, so
If Target.Column = 1 then
* code to pull last name and put it into column B *
Cells(Target.Row, 2) = ????
End If

Now this should fire every time you make a change in column A (ONLY).
Be prepared for errors - such as incomplete entries in column A, or ????

But you can always go back and re-enter in column A.

See if this gets you started...
 
The 1st Sub below, for a regular module will do the 1st part.

The 2nd Sub below, for the worksheet's module, will do the 2nd part.

HTH,
Merjet

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


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(0, 1).FormulaR1C1 = "=getlastname(RC[-1])"
End If
End Sub
 
Sub1 revised

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob Phillips said:
Sub1 revised

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

--

HTH

Bob Phillips
Click to expand...

Perhaps I could just add my 2p-worth as I've been there, got the T-shirt
etc.

The trouble with the macro recorder is that it's a bit dumb. You'll find in
your recorded macro code something like:

Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B8")

The macro recorder has filled in B8 (or B98 for all I know) because that was
the last row of data you had in the spreadsheet at the time. The macro
recorder has hard-wired in that row number, rather than what you wanted
which was something that changes as rows are added. That's what Bob has
given you above.

Incidentally, if you want to stick with your recorded macro code, what you
want is probably just to change where it says Range("B1:B8") to:

Range("B1:B" & Range("B1").End(xlDown).Row)

Geoff
 
truthfully, i'm not all that interested in using the macro
recorder......not initially anyway. i'm much more
interested in actually learning how to use the code. in
my last job before this one, i learned, from scratch, how
to code ASP pages. it was a great experience learning how
to use vbscript and javascript and html and sql, etc,
etc.....instead of just using something like frontpage or
dreamweaver.

thanks for all of your help, everyone. take care until
next time!
-----Original Message-----

Sub1 revised

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

--

HTH

Bob Phillips
Click to expand...

Perhaps I could just add my 2p-worth as I've been there, got the T-shirt
etc.

The trouble with the macro recorder is that it's a bit dumb. You'll find in
your recorded macro code something like:

Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B8")

The macro recorder has filled in B8 (or B98 for all I know) because that was
the last row of data you had in the spreadsheet at the time. The macro
recorder has hard-wired in that row number, rather than what you wanted
which was something that changes as rows are added. That's what Bob has
given you above.

Incidentally, if you want to stick with your recorded macro code, what you
want is probably just to change where it says Range ("B1:B8") to:

Range("B1:B" & Range("B1").End(xlDown).Row)

Geoff


.
Click to expand...
 
Don't downplay the recorder.

Sometimes coding can be tricky, or you just don't remember a bit of code.
The recorder ends up showing the code (plus mundane stuff) and helps you
learn what to do.

When ever I run into a spot the recorder is my first choice before running
to
Google or the MVP's...

--
sb
truthfully, i'm not all that interested in using the macro
recorder......not initially anyway. i'm much more
interested in actually learning how to use the code. in
my last job before this one, i learned, from scratch, how
to code ASP pages. it was a great experience learning how
to use vbscript and javascript and html and sql, etc,
etc.....instead of just using something like frontpage or
dreamweaver.

thanks for all of your help, everyone. take care until
next time!
-----Original Message-----

Sub1 revised

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

--

HTH

Bob Phillips
Click to expand...

Perhaps I could just add my 2p-worth as I've been there, got the T-shirt
etc.

The trouble with the macro recorder is that it's a bit dumb. You'll find in
your recorded macro code something like:

Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B8")

The macro recorder has filled in B8 (or B98 for all I know) because that was
the last row of data you had in the spreadsheet at the time. The macro
recorder has hard-wired in that row number, rather than what you wanted
which was something that changes as rows are added. That's what Bob has
given you above.

Incidentally, if you want to stick with your recorded macro code, what you
want is probably just to change where it says Range ("B1:B8") to:

Range("B1:B" & Range("B1").End(xlDown).Row)

Geoff


.
Click to expand...
Click to expand...
 
Back
Top