+ Reply to Thread
Results 1 to 5 of 5

Array Coding

  1. #1
    Hazel
    Guest

    Array Coding

    I receive the following information as an attachment to an Email. The
    attachment opens in Notepad.

    4100,WM7886,255,1404
    4100,WM5589,255,1473
    4100,WM6458,253,1230
    4100,WM6467,254,966
    4100,WM13163,255,1371
    4100,WM4241,255,1371
    4100,WM10256,255,1323
    4100,WM1696,255,1578
    4100,WM1695,255,1467
    4100,WM6909,255,1435
    4100,WM6332,255,993
    4100,WM2899,255,1018
    4100,WM1349,243,1712
    Always ignore the 1st part of the array e.g.4100, this is the code of the
    site. The 2nd part of the array is always in column "A" and it could be on
    any row within 25 sheets all with different Tab Names. The maximum number of
    rows used on each sheet is 35. The final 2 parts of the array need to enter
    the 1st and 2nd empty columns on the row that is selected by the 2nd part of
    the array is it possible to code into Excel. At the moment I am doing Text to
    Columns then cut and pasting after finding the correct row in column "A" that
    matches the second part of the array. I hope I have explained the above
    properly -- I am using XP Office.

    --
    Many thanks

    hazel

  2. #2
    Patrick Molloy
    Guest

    RE: Array Coding

    this assumes you have a table in a sheet named "main"
    The table starts in A1 with WM7886 with A13 having WM1349 and C13 having 1712

    The looks at each sheet in turn, skipping over 'main'
    the for each of the items, we do a simple match() to see if the WM* code is
    present and if it is, copy over the data

    NOTE: this is not optimised in any way...but for smallish applications lie
    this, it snot a particularly lengthy process.

    My workbook is available

    Option Explicit
    Sub PopData()
    Dim source As Range
    Dim ws As Worksheet
    Dim cell As Range
    Dim index As Long
    For Each ws In Worksheets
    If ws.Name <> "main" Then
    For Each cell In Range(Range("A1"), Range("A1").End(xlDown))
    index = matched(cell.Value, ws)
    If index > 0 Then
    ws.Cells(index, 200).End(xlToLeft).Offset(0, 1) =
    cell.Offset(, 1)
    ws.Cells(index, 200).End(xlToLeft).Offset(0, 1) =
    cell.Offset(, 2)
    End If
    Next 'item
    End If
    Next 'sheet
    End Sub
    Function matched(item As String, ws As Worksheet) As Long
    On Error Resume Next
    matched = WorksheetFunction.Match(item, ws.Columns(1), False)
    On Error GoTo 0
    End Function

    note that some worksheet functions like Match() and Vlookup() actually raise
    errors in VBA. So by "wrapping" them in a safe function, we can avoid
    horrible code issues later when it comes to debugging longer and more complex
    code





    "Hazel" wrote:

    > I receive the following information as an attachment to an Email. The
    > attachment opens in Notepad.
    >
    > 4100,WM7886,255,1404
    > 4100,WM5589,255,1473
    > 4100,WM6458,253,1230
    > 4100,WM6467,254,966
    > 4100,WM13163,255,1371
    > 4100,WM4241,255,1371
    > 4100,WM10256,255,1323
    > 4100,WM1696,255,1578
    > 4100,WM1695,255,1467
    > 4100,WM6909,255,1435
    > 4100,WM6332,255,993
    > 4100,WM2899,255,1018
    > 4100,WM1349,243,1712
    > Always ignore the 1st part of the array e.g.4100, this is the code of the
    > site. The 2nd part of the array is always in column "A" and it could be on
    > any row within 25 sheets all with different Tab Names. The maximum number of
    > rows used on each sheet is 35. The final 2 parts of the array need to enter
    > the 1st and 2nd empty columns on the row that is selected by the 2nd part of
    > the array is it possible to code into Excel. At the moment I am doing Text to
    > Columns then cut and pasting after finding the correct row in column "A" that
    > matches the second part of the array. I hope I have explained the above
    > properly -- I am using XP Office.
    >
    > --
    > Many thanks
    >
    > hazel


  3. #3
    Hazel
    Guest

    RE: Array Coding

    Hi Patrick

    Thanks for the help -- struggling a little bit -- if the "main" table starts
    in Cell A1 with WM7786 -- B1=255 --- C1=1404 because I have a heading row on
    all the sheets I changed the Range to "A2" however on the first sheet only
    the values from "B1" & "C1" are missing all the other info enters correctly
    on sheet1 and all the other sheets even though having a header row all enter
    correctly --- been having a go at altering ranges etc all to no avail --- any
    ideas???
    Once again thanks for your help
    --
    Many thanks

    hazel


    "Patrick Molloy" wrote:

    > this assumes you have a table in a sheet named "main"
    > The table starts in A1 with WM7886 with A13 having WM1349 and C13 having 1712
    >
    > The looks at each sheet in turn, skipping over 'main'
    > the for each of the items, we do a simple match() to see if the WM* code is
    > present and if it is, copy over the data
    >
    > NOTE: this is not optimised in any way...but for smallish applications lie
    > this, it snot a particularly lengthy process.
    >
    > My workbook is available
    >
    > Option Explicit
    > Sub PopData()
    > Dim source As Range
    > Dim ws As Worksheet
    > Dim cell As Range
    > Dim index As Long
    > For Each ws In Worksheets
    > If ws.Name <> "main" Then
    > For Each cell In Range(Range("A1"), Range("A1").End(xlDown))
    > index = matched(cell.Value, ws)
    > If index > 0 Then
    > ws.Cells(index, 200).End(xlToLeft).Offset(0, 1) =
    > cell.Offset(, 1)
    > ws.Cells(index, 200).End(xlToLeft).Offset(0, 1) =
    > cell.Offset(, 2)
    > End If
    > Next 'item
    > End If
    > Next 'sheet
    > End Sub
    > Function matched(item As String, ws As Worksheet) As Long
    > On Error Resume Next
    > matched = WorksheetFunction.Match(item, ws.Columns(1), False)
    > On Error GoTo 0
    > End Function
    >
    > note that some worksheet functions like Match() and Vlookup() actually raise
    > errors in VBA. So by "wrapping" them in a safe function, we can avoid
    > horrible code issues later when it comes to debugging longer and more complex
    > code
    >
    >
    >
    >
    >
    > "Hazel" wrote:
    >
    > > I receive the following information as an attachment to an Email. The
    > > attachment opens in Notepad.
    > >
    > > 4100,WM7886,255,1404
    > > 4100,WM5589,255,1473
    > > 4100,WM6458,253,1230
    > > 4100,WM6467,254,966
    > > 4100,WM13163,255,1371
    > > 4100,WM4241,255,1371
    > > 4100,WM10256,255,1323
    > > 4100,WM1696,255,1578
    > > 4100,WM1695,255,1467
    > > 4100,WM6909,255,1435
    > > 4100,WM6332,255,993
    > > 4100,WM2899,255,1018
    > > 4100,WM1349,243,1712
    > > Always ignore the 1st part of the array e.g.4100, this is the code of the
    > > site. The 2nd part of the array is always in column "A" and it could be on
    > > any row within 25 sheets all with different Tab Names. The maximum number of
    > > rows used on each sheet is 35. The final 2 parts of the array need to enter
    > > the 1st and 2nd empty columns on the row that is selected by the 2nd part of
    > > the array is it possible to code into Excel. At the moment I am doing Text to
    > > Columns then cut and pasting after finding the correct row in column "A" that
    > > matches the second part of the array. I hope I have explained the above
    > > properly -- I am using XP Office.
    > >
    > > --
    > > Many thanks
    > >
    > > hazel


  4. #4
    Patrick Molloy
    Guest

    RE: Array Coding

    if your table starts in A2 then I assume all the data is still in rows. So in
    my code all you do is change the two instances of A1 to A2

    the code simply loops through each worksheet. for each of these, it simply
    loops through each item in column A of your table. if does a MATCH against
    column A of the particular worksheet - if thei tem is there, then it copies
    over

    http://www.xl-expert.com/Files/Array_coding.xls



    "Hazel" wrote:

    > Hi Patrick
    >
    > Thanks for the help -- struggling a little bit -- if the "main" table starts
    > in Cell A1 with WM7786 -- B1=255 --- C1=1404 because I have a heading row on
    > all the sheets I changed the Range to "A2" however on the first sheet only
    > the values from "B1" & "C1" are missing all the other info enters correctly
    > on sheet1 and all the other sheets even though having a header row all enter
    > correctly --- been having a go at altering ranges etc all to no avail --- any
    > ideas???
    > Once again thanks for your help
    > --
    > Many thanks
    >
    > hazel
    >
    >
    > "Patrick Molloy" wrote:
    >
    > > this assumes you have a table in a sheet named "main"
    > > The table starts in A1 with WM7886 with A13 having WM1349 and C13 having 1712
    > >
    > > The looks at each sheet in turn, skipping over 'main'
    > > the for each of the items, we do a simple match() to see if the WM* code is
    > > present and if it is, copy over the data
    > >
    > > NOTE: this is not optimised in any way...but for smallish applications lie
    > > this, it snot a particularly lengthy process.
    > >
    > > My workbook is available
    > >
    > > Option Explicit
    > > Sub PopData()
    > > Dim source As Range
    > > Dim ws As Worksheet
    > > Dim cell As Range
    > > Dim index As Long
    > > For Each ws In Worksheets
    > > If ws.Name <> "main" Then
    > > For Each cell In Range(Range("A1"), Range("A1").End(xlDown))
    > > index = matched(cell.Value, ws)
    > > If index > 0 Then
    > > ws.Cells(index, 200).End(xlToLeft).Offset(0, 1) =
    > > cell.Offset(, 1)
    > > ws.Cells(index, 200).End(xlToLeft).Offset(0, 1) =
    > > cell.Offset(, 2)
    > > End If
    > > Next 'item
    > > End If
    > > Next 'sheet
    > > End Sub
    > > Function matched(item As String, ws As Worksheet) As Long
    > > On Error Resume Next
    > > matched = WorksheetFunction.Match(item, ws.Columns(1), False)
    > > On Error GoTo 0
    > > End Function
    > >
    > > note that some worksheet functions like Match() and Vlookup() actually raise
    > > errors in VBA. So by "wrapping" them in a safe function, we can avoid
    > > horrible code issues later when it comes to debugging longer and more complex
    > > code
    > >
    > >
    > >
    > >
    > >
    > > "Hazel" wrote:
    > >
    > > > I receive the following information as an attachment to an Email. The
    > > > attachment opens in Notepad.
    > > >
    > > > 4100,WM7886,255,1404
    > > > 4100,WM5589,255,1473
    > > > 4100,WM6458,253,1230
    > > > 4100,WM6467,254,966
    > > > 4100,WM13163,255,1371
    > > > 4100,WM4241,255,1371
    > > > 4100,WM10256,255,1323
    > > > 4100,WM1696,255,1578
    > > > 4100,WM1695,255,1467
    > > > 4100,WM6909,255,1435
    > > > 4100,WM6332,255,993
    > > > 4100,WM2899,255,1018
    > > > 4100,WM1349,243,1712
    > > > Always ignore the 1st part of the array e.g.4100, this is the code of the
    > > > site. The 2nd part of the array is always in column "A" and it could be on
    > > > any row within 25 sheets all with different Tab Names. The maximum number of
    > > > rows used on each sheet is 35. The final 2 parts of the array need to enter
    > > > the 1st and 2nd empty columns on the row that is selected by the 2nd part of
    > > > the array is it possible to code into Excel. At the moment I am doing Text to
    > > > Columns then cut and pasting after finding the correct row in column "A" that
    > > > matches the second part of the array. I hope I have explained the above
    > > > properly -- I am using XP Office.
    > > >
    > > > --
    > > > Many thanks
    > > >
    > > > hazel


  5. #5
    Hazel
    Guest

    RE: Array Coding

    Thanks works OK -- will now carry on and enter the details of the 400 or so
    codes that arrive on a weekly basis your - code will save many hours of cut &
    paste.
    --
    Many thanks

    hazel


    "Patrick Molloy" wrote:

    > if your table starts in A2 then I assume all the data is still in rows. So in
    > my code all you do is change the two instances of A1 to A2
    >
    > the code simply loops through each worksheet. for each of these, it simply
    > loops through each item in column A of your table. if does a MATCH against
    > column A of the particular worksheet - if thei tem is there, then it copies
    > over
    >
    > http://www.xl-expert.com/Files/Array_coding.xls
    >
    >
    >
    > "Hazel" wrote:
    >
    > > Hi Patrick
    > >
    > > Thanks for the help -- struggling a little bit -- if the "main" table starts
    > > in Cell A1 with WM7786 -- B1=255 --- C1=1404 because I have a heading row on
    > > all the sheets I changed the Range to "A2" however on the first sheet only
    > > the values from "B1" & "C1" are missing all the other info enters correctly
    > > on sheet1 and all the other sheets even though having a header row all enter
    > > correctly --- been having a go at altering ranges etc all to no avail --- any
    > > ideas???
    > > Once again thanks for your help
    > > --
    > > Many thanks
    > >
    > > hazel
    > >
    > >
    > > "Patrick Molloy" wrote:
    > >
    > > > this assumes you have a table in a sheet named "main"
    > > > The table starts in A1 with WM7886 with A13 having WM1349 and C13 having 1712
    > > >
    > > > The looks at each sheet in turn, skipping over 'main'
    > > > the for each of the items, we do a simple match() to see if the WM* code is
    > > > present and if it is, copy over the data
    > > >
    > > > NOTE: this is not optimised in any way...but for smallish applications lie
    > > > this, it snot a particularly lengthy process.
    > > >
    > > > My workbook is available
    > > >
    > > > Option Explicit
    > > > Sub PopData()
    > > > Dim source As Range
    > > > Dim ws As Worksheet
    > > > Dim cell As Range
    > > > Dim index As Long
    > > > For Each ws In Worksheets
    > > > If ws.Name <> "main" Then
    > > > For Each cell In Range(Range("A1"), Range("A1").End(xlDown))
    > > > index = matched(cell.Value, ws)
    > > > If index > 0 Then
    > > > ws.Cells(index, 200).End(xlToLeft).Offset(0, 1) =
    > > > cell.Offset(, 1)
    > > > ws.Cells(index, 200).End(xlToLeft).Offset(0, 1) =
    > > > cell.Offset(, 2)
    > > > End If
    > > > Next 'item
    > > > End If
    > > > Next 'sheet
    > > > End Sub
    > > > Function matched(item As String, ws As Worksheet) As Long
    > > > On Error Resume Next
    > > > matched = WorksheetFunction.Match(item, ws.Columns(1), False)
    > > > On Error GoTo 0
    > > > End Function
    > > >
    > > > note that some worksheet functions like Match() and Vlookup() actually raise
    > > > errors in VBA. So by "wrapping" them in a safe function, we can avoid
    > > > horrible code issues later when it comes to debugging longer and more complex
    > > > code
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > "Hazel" wrote:
    > > >
    > > > > I receive the following information as an attachment to an Email. The
    > > > > attachment opens in Notepad.
    > > > >
    > > > > 4100,WM7886,255,1404
    > > > > 4100,WM5589,255,1473
    > > > > 4100,WM6458,253,1230
    > > > > 4100,WM6467,254,966
    > > > > 4100,WM13163,255,1371
    > > > > 4100,WM4241,255,1371
    > > > > 4100,WM10256,255,1323
    > > > > 4100,WM1696,255,1578
    > > > > 4100,WM1695,255,1467
    > > > > 4100,WM6909,255,1435
    > > > > 4100,WM6332,255,993
    > > > > 4100,WM2899,255,1018
    > > > > 4100,WM1349,243,1712
    > > > > Always ignore the 1st part of the array e.g.4100, this is the code of the
    > > > > site. The 2nd part of the array is always in column "A" and it could be on
    > > > > any row within 25 sheets all with different Tab Names. The maximum number of
    > > > > rows used on each sheet is 35. The final 2 parts of the array need to enter
    > > > > the 1st and 2nd empty columns on the row that is selected by the 2nd part of
    > > > > the array is it possible to code into Excel. At the moment I am doing Text to
    > > > > Columns then cut and pasting after finding the correct row in column "A" that
    > > > > matches the second part of the array. I hope I have explained the above
    > > > > properly -- I am using XP Office.
    > > > >
    > > > > --
    > > > > Many thanks
    > > > >
    > > > > hazel


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1