VBA Help Needed (Difficulty 10/10)

[william1990]

I have an excel spreadsheet with the following data
Cell Reference
C2 D2 E2 F2 G2 H2
Data
12x2 16x8 20x2 22x2 30x2 35x8


Cell Reference
C3 D3 E3 F3
Data
16x5 22x10 30x3 35x7


I have also got the following headings
Cell Reference
V1 W1 X1 Y1 Z1 ZA1
12 15 20 22 30 35

I need a command button that will look up the data in each row (row2 if its easier), extract the number after the x and place it in under the corresponding number based on the number before the x.

For example for row 2

2 will go under heading 12, 8 will go under 16, 2 will go under 20, 2 will go under 22, 2 will go under 30 and 9 will go under 35.

For row 3;

5 will go under heading 16, 10 will go under 22, 3 will go under 30 and 7 will go under 35.

The number before the x is the size of the product and the number after the x is the quantity.

I have a spreadsheet fully populated with this type of data (320+ rows), I did start to do it myself but I realised I was looking in each cell, which would take to long, it needs to look in each row for the number before the x.

Below is the code that I had started;

Code

Do While i < 350
Clip = Range("C" & i)

Do While t < 19
a3 = Len(Clip)
a4 = InStr(Clip, "x")

Select Case t
Case Is = "1"
var7 = Left(Clip, a4 - 1)
If var7 = 12 Then
Range("V" & i) = Right(Clip, a4 - 2)
End If
End Select

/Code

Doing it this way would have taken to long, as I would have had many cases in the select case, is there another way for me to look across the row instead of each cell, if this is the case would you be able to provide me with the vba, would be much appreciated.

 

[william1990]

Now i see that this would not be the best option, I think I need to find the '12' in the range first, this is the code that I have used;

code
Range("C2:U2").Select
Selection.Find(What:="12", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _False, SearchFormat:=False).Activate
/code

I now need a piece of code that does the following;

if 12 is present in the range, select the cell, and copy the number after the x and place in V2.

any suggestions will be taken on board and be much appreciated,

after this has been done, i should be able to write the whole code.