Conversion en masse

  • Thread starter Thread starter maykinit
  • Start date Start date
M

maykinit

Please forgive the length of the enquire. Hope this is relatively
easy. I suspect it is. Being a newbie I seek the help of the gurus
here. I would like to be able to do the following. I am parsing a
series of alphanumeric characters. Lets call that PartX. Then doing a
series of vlookups to transcribe the different fields. Then I do a
concatenate of the resulting information from the vlookups thus doing a
conversion. An example of this would be:
Part A:
AA042AB2LWXX
The resulting vlookups and concatenate give me PartB:
351009SD02L100#
This works fine for doing this one part at a time. I would like to do
this for a bunch of parts at a time. I envision this being done by
populating column A1 through A~whatever with the PartA's that require
conversion. Then some logic behind the scenes would populate ColumnB1
through B~whatever with the resulting output from the vlookup and
concatenate. I wonder how difficult this would be and if someone here
might supply the keys(that translates code)to help me do this? The
simpler the explanation the better, PLEASE. I would very much
appreciate your help.
 
Hi

if you'ld like to let us know the "series of vlookups to transcribe the
different fields" that you're using & the concatenation function - we will
be able to help you better.

Cheers
JulieD
 
Unfortunately the last work I did resides at home. Won't be able to
get to it until this evening. I was kind of hoping for some guidance
prior to getting there tonight. Sounds like you are going a very VB
route. I was thinking a Loop Do Until kind of logic. Does that make
sense? I know I am letting my newbie hang out all over the place here.
Thank you so very much for the quick response.
 
Hi

well i could provide really general code like

sub changestuff()
for each c in Range("A1:A100")
if c.value <> "" then
c.Offset(0, 1).Value = Application.WorksheetFunction. _
VLookup(Left(c.Value, 4),
Sheets("Sheet2").Range("A1:B200"), 2, 0)
c.Offset(0, 1).Value = c.Offset(0,1).value &
Application.WorksheetFunction. _
VLookup(mid(c.Value,5,2),
Sheets("Sheet3").Range("A1:B200"), 2, 0)
end if
next
end sub

but i'm not sure how much this will help
alternatively, if you have your current formulas in B2, C2, D2 etc then you
could simply copy the down the list of part numbers using the autofill
handle when you bring the new part numbers in and then you wouldn't have to
worry about using VBA.

Cheers
JulieD
 
Thank-You so very much JulieD! I already solved it in a different
fashion. Rudimentary by comparison I might add. I will be hanging on
to the suggested code you provided for reference. I can already see it
being a tool in the arsenal. Thanks again.
 
you're welcome - sorry i didn't get the answer to you quicker, but my access
to the internet "died" for some reason last night and didn't come back on
line until late this afternoon.

Cheers
JulieD
 
Back
Top