Clarification of question of VBA macro for Excel...part number convers

[ScottNeth]

OK- I'm going to clarify this post a little bit because you're actually being helpful and my question was too vague. I have 280 workbooks of spreadsheets. On them, in column L I have (or am suppose to have) part numbers. The part numbers are 3EC15195, 3EC15200, 3EC16124, 3EC15240, 3EC15199, and 3EC15202. Now, a little bit tricky, the first 8 numbers of these part numbers determine a HECI code (I mistakenly said serial number, but it really doesn't make a difference)...I say first eight because the part numbers have date codes added to them. The first 8 numbers determine the HECI codes which are as follows in order VACTEHWBAA, VACTFKNBAA, VACTHNNBAA, VACTDG8BAA, BACEGR5DAA, and VACTG20BAA. The field reps who wrote down the codes generally got either the part number OR the HECI code, rarely both and occasionally neither, and I need both entered.
The following module worked perfectly when I used it along with =(LEFT(L7,8))as a function Function SelectType(CellRef

As String) As String
On Error GoTo Err_SelectType
Select Case CellRef
Case "3EC15195"
SelectType = "VACTEHWBAA"
Case "3EC15200"
SelectType = "VACTFKNBAA"
Case "3EC16124"
SelectType = "VACTHNNBAA"
Case "3EC15240"
SelectType = "VACTDG8BAA"
Case "3EC15199"
SelectType = "VACEGR5DAA"
Case "3EC15202"
SelectType = "VACTG20BAA"
Case Else
SelectType = "No reference"
End Select


Exit_SelectType:
Exit Function

Err_SelectType:
SelectType = "Data error"
Resume Exit_SelectType
End Function

The only problem was I had to put it in every worksheet and it didn't work the other way around (obviously), i.e, if I had the HECI it wouldn't give the part number. Another consideration is my project manager doesn't have a clue what a macro is and freaks out when he pulls up the spreadsheet and it gives the little message that macros are turned off because security level isn't high enough.

 

[pbrodsky]

You can put the VBA code in a seperate workbook as a subroutine rather than a function. Have it open each of the workbooks you need to fill in, enter the data, and close it again. The details would depend on how you identify the files you need to work on and how you determine the range of data you need to fill in.
e.g. if all of the files are in a particular directory.
sub fillin
fn=dir("c:\dir\*.exe&quot
do while fn <> &quot;&quot;
workbooks.open fn
lastrow = Cells.SpecialCells(xlCellTypeLastCell)
for i=1 to lastrow
if not isempty(cells(i,&quot;L&quot then
select case left(cells(i,&quot;L&quot,8)
Case &quot;3EC15195&quot;
cells(i,&quot;M&quot = &quot;VACTEHWBAA&quot;
Case &quot;3EC15200&quot;
cells(i,&quot;M&quot = &quot;VACTFKNBAA&quot;
etc.
end select
next i
activewindow.close
fn=dir
loop
end sub
------------------
something along these lines should work for you.

 

[ScottNeth]

THIS SUBROUTINE IS GIVING ME A &quot;NEXT WITHOUT FOR&quot; ERROR...LOOKS LIKE FOR IS THERE TO ME...>SCRATCHING HIS HEAD IN CONFUSION<...IF I TAKE THAT OUT IT GIVES ME &quot;LOOP WITHOUT DO&quot; AND FINALLY, LAST BUT NOT LEAST...&quot;BLOCK IF WITHOUT END IF.&quot; I'M BEGINNING TO THINK I SHOULD HAVE WENT TO PHARMACY SCHOOL....

 

[dsi]

missing and End If near the end
etc.
end select
END IF
next i

 

[ScottNeth]

any idea why it gives me an error of file not being found? It picks the first one in the directory and says that that specific one can't be found so its obviously finding it.

 

[pbrodsky]

Sorry - I responded quickly at work and didn't have a chance to examine it closely.
I think it probably needs a 'Chdir(&quot;C:\dir&quot;' before you start opening the files.