Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed guys!!!!, Excel macro
hey guys I need help with a problem in excel Below is an example of my spreadsheet ignore the -----, they are just there to keep the allgning for thi thread A---------------B-------------C----------D----------E 1 XXX--------product:------XXXX 2 XXX--------id:-------------YYYY 3 XXX 4 XXX 5 start------other data--- 6 XXX 7 XXX 8 XXX-------product:-------MMMM 9 XXX-------id:--------------NNNN 10 XXX 11 XXX 12 XXX 13 XXX 14 XXX 15 start-------other data--- _what_i_need_to_happen_ Value XXXX needs to be placed in D5 Value YYYY needs to be placed in E5 Value MMMM needs to be placed in D15 Value NNNN needs to be placed in E15 My suggested method of doing this. _Aim_ I need to get the value from the cell C1 and C2 and add them to D5 and E5. And again I need to take the different values from C8 and C9 and add them to D15 and E15. The spreadsheet is very large and has this same pattern all the wa down, The difference in rows between the cell contianing the keywor start and the cells containing the values of product and id are no constant throughout the spreadsheet. I need a For loop to check all of Row B to check for the valu "product:" i.e B1. when it finds this it needs to store the value realting to the produc i.e C1 Also it will need to check for "ID:" and store the value relating t this ie. C2. These two values need to be stored in variables. As each new value o product and ID is reached as the for loop progresses down th spreadsheet the current values in the variables will be overwritten. At the same time Column A will have to be check for the word "start" i a cell is found with the keyword start then the values in two variable will need to be added to the corresponding row in column D and E. Can someone please give me the code that will do this? -- eaki ----------------------------------------------------------------------- eakin's Profile: http://www.excelforum.com/member.php...fo&userid=1597 View this thread: http://www.excelforum.com/showthread.php?threadid=27445 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed guys!!!!, Excel macro
Sub WriteValues()
Dim rng as Range, ar as Range Dim v1 as Variant, v2 as Variant set rng = Columns(2).specialCells(xlConstants) for each ar in rng.Areas if instr(1,ar(1).Value,"product",vbTextCompare) then v1 = ar(1) v2 = ar(2) else if instr(1,ar(1,0).Value,"start",vbTextCompare) then ar(1).offset(-1,2).Value = v1 ar(1).offset(-1,3).Value = v2 end if end if Next End sub Test it on a copy of your data. -- Regards, Tom Ogilvy "eakin" wrote in message ... hey guys I need help with a problem in excel Below is an example of my spreadsheet ignore the -----, they are just there to keep the allgning for this thread A---------------B-------------C----------D----------E 1 XXX--------product:------XXXX 2 XXX--------id:-------------YYYY 3 XXX 4 XXX 5 start------other data--- 6 XXX 7 XXX 8 XXX-------product:-------MMMM 9 XXX-------id:--------------NNNN 10 XXX 11 XXX 12 XXX 13 XXX 14 XXX 15 start-------other data--- _what_i_need_to_happen_ Value XXXX needs to be placed in D5 Value YYYY needs to be placed in E5 Value MMMM needs to be placed in D15 Value NNNN needs to be placed in E15 My suggested method of doing this. _Aim_ I need to get the value from the cell C1 and C2 and add them to D5 and E5. And again I need to take the different values from C8 and C9 and add them to D15 and E15. The spreadsheet is very large and has this same pattern all the way down, The difference in rows between the cell contianing the keyword start and the cells containing the values of product and id are not constant throughout the spreadsheet. I need a For loop to check all of Row B to check for the value "product:" i.e B1. when it finds this it needs to store the value realting to the product i.e C1 Also it will need to check for "ID:" and store the value relating to this ie. C2. These two values need to be stored in variables. As each new value of product and ID is reached as the for loop progresses down the spreadsheet the current values in the variables will be overwritten. At the same time Column A will have to be check for the word "start" if a cell is found with the keyword start then the values in two variables will need to be added to the corresponding row in column D and E. Can someone please give me the code that will do this?? -- eakin ------------------------------------------------------------------------ eakin's Profile: http://www.excelforum.com/member.php...o&userid=15976 View this thread: http://www.excelforum.com/showthread...hreadid=274459 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA guys needed | Excel Worksheet Functions | |||
Excel Macro Needed | Excel Discussion (Misc queries) | |||
another excel question, thanks guys! | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
help needed with excel macro | Excel Programming |