Grant A
New Member
Our Excel function has the following request;
We have a worksheet that lists all past, current and upcoming work projects (work sheet “Report”). There is columns with dates that we can filter for the work status.
Within the report a key Work Order number is generated for all scheduled tasks
We generate another raw data table of 17 columns and anything up to 50,000 rows that display completed tasks (work sheet “Data”). All data is completed work orders
If the work order number in column 1 of the “data” sheet matches a corresponding work order number on designated column on the ”Report” sheet then we wish to have 5 cells from the "Data" sheet put the selected values within their row, updated to the corresponding row / column on the "report" sheet that align with the same work order number. This provides our information as check that the work orders have been completed.
Our coding request is to run the key work order check within an array and if true the row data from the “data” sheet would update cell values from five columns into the corresponding “report” sheet with matched work order number, this provides completed work data.
The remaining Blank rows on the “report” page would indicate open work orders.
This is being done with Vlookup formula but would request some assistance to have this generated with VBA array.
The columns for the "data" sheet and "report" sheet remain fixed. The work order number is unique for each job.
Our Vlookup formula for the key number check is as follows;
=IF(ISERROR(VLOOKUP(K2,Data!$1:$50000,1,0)),"",VLOOKUP(K2,Data!$1:$50000,1,0))
this transposes the data from "Data" sheet to "report" sheet when true
The other four columns have variation of this formula with the change in column lookup from the "data" sheet to the "report" sheet.
I watch the Chandoo videos, read the books and online help but arrays is just one function within VBA i just currently can't fully grasp and understand. i can see the concepts but only become lost in the detail.
would very much appreciate any assistance
regards
Array Tragic
We have a worksheet that lists all past, current and upcoming work projects (work sheet “Report”). There is columns with dates that we can filter for the work status.
Within the report a key Work Order number is generated for all scheduled tasks
We generate another raw data table of 17 columns and anything up to 50,000 rows that display completed tasks (work sheet “Data”). All data is completed work orders
If the work order number in column 1 of the “data” sheet matches a corresponding work order number on designated column on the ”Report” sheet then we wish to have 5 cells from the "Data" sheet put the selected values within their row, updated to the corresponding row / column on the "report" sheet that align with the same work order number. This provides our information as check that the work orders have been completed.
Our coding request is to run the key work order check within an array and if true the row data from the “data” sheet would update cell values from five columns into the corresponding “report” sheet with matched work order number, this provides completed work data.
The remaining Blank rows on the “report” page would indicate open work orders.
This is being done with Vlookup formula but would request some assistance to have this generated with VBA array.
The columns for the "data" sheet and "report" sheet remain fixed. The work order number is unique for each job.
Our Vlookup formula for the key number check is as follows;
=IF(ISERROR(VLOOKUP(K2,Data!$1:$50000,1,0)),"",VLOOKUP(K2,Data!$1:$50000,1,0))
this transposes the data from "Data" sheet to "report" sheet when true
The other four columns have variation of this formula with the change in column lookup from the "data" sheet to the "report" sheet.
I watch the Chandoo videos, read the books and online help but arrays is just one function within VBA i just currently can't fully grasp and understand. i can see the concepts but only become lost in the detail.
would very much appreciate any assistance
regards
Array Tragic