Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 16th 06, 08:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 3
Default Replace Old Part Numbers with New Part Numbers in a Macro.

How can it be done? I have a list of old, busted numbers, I want to replace
them with new, shiny numbers. Not all lists that will be subject to the
macro will have all part numbers.

Sometimes, I'm saying, the macro will be searching for numbers that aren't
there. They are gennerally of a ABC12345 format, a 3 letter prefix and a 5
digit part number such as UNV51001.

I'll be loading a customers pricing, running the macro, and uploading it to
a new system. The new system doesn't like certain numbers, so we must
convert them.
--
Thanks,
Jeffery Keown

  #3   Report Post  
Old October 16th 06, 10:02 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 3
Default Replace Old Part Numbers with New Part Numbers in a Macro.

Thanks for the speedy reply. My thoughts run thus: Will I have to add these
vlookup formulae each time? Or is there a way to simply replace the bad
numbers, keep the good ones and move on?

Maybe I'm thinking vlookup can only be used in a formula and not in a macro.
Perhaps I don't understand macros well enough.
--
Thanks,
Jeffery Keown


"Gary L Brown" wrote:

Create a Conversion worksheet.
Col A has the old #, Col B has the new #.
Now you can use the vLookup function to get the new # for all your lists.
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jeffery Keown" wrote:

How can it be done? I have a list of old, busted numbers, I want to replace
them with new, shiny numbers. Not all lists that will be subject to the
macro will have all part numbers.

Sometimes, I'm saying, the macro will be searching for numbers that aren't
there. They are gennerally of a ABC12345 format, a 3 letter prefix and a 5
digit part number such as UNV51001.

I'll be loading a customers pricing, running the macro, and uploading it to
a new system. The new system doesn't like certain numbers, so we must
convert them.
--
Thanks,
Jeffery Keown

  #4   Report Post  
Old October 16th 06, 10:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 219
Default Replace Old Part Numbers with New Part Numbers in a Macro.

You can craete a macro to do the same thing as one of Excel's worksheet
functions but ... WHY?
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jeffery Keown" wrote:

Thanks for the speedy reply. My thoughts run thus: Will I have to add these
vlookup formulae each time? Or is there a way to simply replace the bad
numbers, keep the good ones and move on?

Maybe I'm thinking vlookup can only be used in a formula and not in a macro.
Perhaps I don't understand macros well enough.
--
Thanks,
Jeffery Keown


"Gary L Brown" wrote:

Create a Conversion worksheet.
Col A has the old #, Col B has the new #.
Now you can use the vLookup function to get the new # for all your lists.
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jeffery Keown" wrote:

How can it be done? I have a list of old, busted numbers, I want to replace
them with new, shiny numbers. Not all lists that will be subject to the
macro will have all part numbers.

Sometimes, I'm saying, the macro will be searching for numbers that aren't
there. They are gennerally of a ABC12345 format, a 3 letter prefix and a 5
digit part number such as UNV51001.

I'll be loading a customers pricing, running the macro, and uploading it to
a new system. The new system doesn't like certain numbers, so we must
convert them.
--
Thanks,
Jeffery Keown

  #5   Report Post  
Old October 17th 06, 01:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 3
Default Replace Old Part Numbers with New Part Numbers in a Macro.

Well... maybe I wasn't so clear. I'm bring old CSV files from a primitive
system to a newer one that really likes Excel Files. So I'll be running the
macro each time I load the CSV files. Excel will have to look at the CSV
file, replace the part numbers in one column (leaving prices unchanged in
another).

Now, if I can have a worksheet loaded that will perform that function on a
separate file each time I load a new one (I have over 2000 of these customer
part lists), I'd be thrilled! If you can point me to such information, that
would be great, as you've already answered me twice, and I don't want to be a
pest.
--
Thanks,
Jeffery Keown


"Gary L Brown" wrote:

You can craete a macro to do the same thing as one of Excel's worksheet
functions but ... WHY?
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jeffery Keown" wrote:

Thanks for the speedy reply. My thoughts run thus: Will I have to add these
vlookup formulae each time? Or is there a way to simply replace the bad
numbers, keep the good ones and move on?

Maybe I'm thinking vlookup can only be used in a formula and not in a macro.
Perhaps I don't understand macros well enough.
--
Thanks,
Jeffery Keown


"Gary L Brown" wrote:

Create a Conversion worksheet.
Col A has the old #, Col B has the new #.
Now you can use the vLookup function to get the new # for all your lists.
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jeffery Keown" wrote:

How can it be done? I have a list of old, busted numbers, I want to replace
them with new, shiny numbers. Not all lists that will be subject to the
macro will have all part numbers.

Sometimes, I'm saying, the macro will be searching for numbers that aren't
there. They are gennerally of a ABC12345 format, a 3 letter prefix and a 5
digit part number such as UNV51001.

I'll be loading a customers pricing, running the macro, and uploading it to
a new system. The new system doesn't like certain numbers, so we must
convert them.
--
Thanks,
Jeffery Keown



  #6   Report Post  
Old October 17th 06, 03:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 219
Default Replace Old Part Numbers with New Part Numbers in a Macro.

1) put the code below (ChangePartNumbers) in the workbook with the Conversion
worksheet in it.
2) make any needed changes to the macro as described in the comments that
are in the macro.
3) make sure the workbook with the Conversion worksheet in it is open.
4) pull up a .csv file
5) run the macro 'ChangePartNumbers'

'/===============================================/
' Sub Purpose: lookup new parts #s and replace old parts #s
' by deleting the old parts #s column
'
' Method- Inserts a column to the right of the column with
' the old part#s in it. Puts a vlookup formula in
' the column to the right of the column with the
' old part#s in it. If the vlookup can not find a
' new parts #, a blank is inserted.
' Copies that formula to the
' bottom of the worksheet and makes it a value.
' Deletes the old part#s column.
'
' *** indicates areas you might want to change the macro
'
Public Sub ChangePartNumbers()
Dim dblLastRow As Double
Dim strAddress As String

On Error GoTo err_Sub

'find last row in worksheet
dblLastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
' save location of current cell
strAddress = ActiveCell.Address
'- - - - - - - - - - - - - - -
'*** insert a column to the left of the column with the
' part#s in it. Change <<<("C:C") to whatever column has
' the part#s in it.
Columns("C:C").Offset(0, 1).Insert Shift:=xlToRight
'- - - - - - - - - - - - - - -
'*** change <<<("D1") to the column to the left of the column
' that has the part#s in it.
Range("D1").Select
ActiveCell.Formula = "NEW PART #S"
'- - - - - - - - - - - - - - -
'*** change <<<("D2") to the column to the left of the column
' that has the part#s in it.
Range("D2").Select
'- - - - - - - - - - - - - - -
'*** change <<<VLOOKUP(C2 to whatever column has
' the part#s in it.
'*** change <<<[Book1.xls] to the workbook with the Conversion
' worksheet in it
'*** change <<<Sheet1 to the worksheet name of the Conversion
' worksheet
'*** change <<<$B:$C to the columns in the Conversion worksheet
' where the Old part#s / New part#s are located
'*** assuming that the New part#s column is located immediately
' to the right of the Old part#s column, therefore the
' <<<2 in <<<$B:$C,2,FALSE does not need to be changed
ActiveCell.Formula = "=IF(ISNA(VLOOKUP(C2," & _
"[Book1.xls]Sheet1!$B:$C,2,FALSE)),"""",VLOOKUP(C2," & _
"[Book1.xls]Sheet1!$B:$C,2,FALSE))"
'- - - - - - - - - - - - - - -
Selection.Copy
'- - - - - - - - - - - - - - -
'*** change <<<"D2" to the column to the left of the column
' that has the part#s in it.
Range("D2" & dblLastRow).Select
'- - - - - - - - - - - - - - -
ActiveSheet.Paste
Application.CutCopyMode = False
'- - - - - - - - - - - - - - -
'*** change <<<("D") to the column to the left of the column
' that has the part#s in it.
Columns("D").Select
'- - - - - - - - - - - - - - -
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'- - - - - - - - - - - - - - -
'*** change <<<("C:C") to whatever column has the part#s in it.
Columns("C:C").Select
'- - - - - - - - - - - - - - -
Application.CutCopyMode = False
'- - - - - - - - - - - - - - -
'*** delete the line of code below if you do NOT want to delete the
' old part#s column
Selection.Delete Shift:=xlToLeft
'- - - - - - - - - - - - - - -
Range(strAddress).Select

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: ChangePartNumbers - " & Now()
Resume exit_Sub
End Sub
'/===============================================/

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jeffery Keown" wrote:

Well... maybe I wasn't so clear. I'm bring old CSV files from a primitive
system to a newer one that really likes Excel Files. So I'll be running the
macro each time I load the CSV files. Excel will have to look at the CSV
file, replace the part numbers in one column (leaving prices unchanged in
another).

Now, if I can have a worksheet loaded that will perform that function on a
separate file each time I load a new one (I have over 2000 of these customer
part lists), I'd be thrilled! If you can point me to such information, that
would be great, as you've already answered me twice, and I don't want to be a
pest.
--
Thanks,
Jeffery Keown


"Gary L Brown" wrote:

You can craete a macro to do the same thing as one of Excel's worksheet
functions but ... WHY?
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jeffery Keown" wrote:

Thanks for the speedy reply. My thoughts run thus: Will I have to add these
vlookup formulae each time? Or is there a way to simply replace the bad
numbers, keep the good ones and move on?

Maybe I'm thinking vlookup can only be used in a formula and not in a macro.
Perhaps I don't understand macros well enough.
--
Thanks,
Jeffery Keown


"Gary L Brown" wrote:

Create a Conversion worksheet.
Col A has the old #, Col B has the new #.
Now you can use the vLookup function to get the new # for all your lists.
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Jeffery Keown" wrote:

How can it be done? I have a list of old, busted numbers, I want to replace
them with new, shiny numbers. Not all lists that will be subject to the
macro will have all part numbers.

Sometimes, I'm saying, the macro will be searching for numbers that aren't
there. They are gennerally of a ABC12345 format, a 3 letter prefix and a 5
digit part number such as UNV51001.

I'll be loading a customers pricing, running the macro, and uploading it to
a new system. The new system doesn't like certain numbers, so we must
convert them.
--
Thanks,
Jeffery Keown



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Text to Numbers in a Macro denise Excel Worksheet Functions 3 September 13th 06 03:25 PM
Preserve negative numbers in following macro Slashman Excel Worksheet Functions 2 September 12th 06 12:57 AM
error when running cut & paste macro Otto Moehrbach Excel Worksheet Functions 4 August 9th 06 01:49 PM
Convertin Text to numbers within a Macro?? Pedros Excel Discussion (Misc queries) 1 October 20th 05 06:40 AM
Looping Macro That adds a blank row between different part #'s fiero84 Excel Discussion (Misc queries) 5 March 25th 05 07:59 AM


All times are GMT +1. The time now is 04:36 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017