Help needed guys!!!!, Excel macro

[eakin]

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?

 

[Tom Ogilvy]

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.