| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array Manipulation with VBA coding | Excel Discussion (Misc queries) | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| coding | Excel Discussion (Misc queries) | |||
| "=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
| Implant macro coding into ASP coding | Excel Programming | |||
Linear Mode
