huaya
11-08-2007, 10:41 AM
Hello,
I am encountering a loop through problem in a big data set in Excel. I am trying to loop through a column in Sheet1, as long as the column contain a specified word which defined in a column in Sheet2, the next column same row in sheet2 will appear in the same row of next column in Sheet1.
The simplified example as follows:
Table 1 in Sheet1:
Column A Column B
1 SP 100000
2 PO 100000
3 IO 100000
4 INV 100000
A big data in Sheet2:
Column A Column B
1 FIX 1000
2 FIX 1000
3 FIX/IO 1000
4 INV/IO 1000
5 FLT 1000
6 FLT 1000
7 FLT/DLY/SP 1000
8 PO 1000
9 FIX 1000
10 FIX 1000
11 FIX/Z 1000
12 INV 1000
I want to match the word in column A of sheet1 with column A in sheet2. If it matches, the same row of Column B in sheet2 will be same as column B in sheet1.
For example, "A1"in sheet1 is "SP", "A7" in column A of sheet2 also contains "SP", so "B7" in sheet2 will be 100000. The revised data set should be same as below. Can anyone help me with to create a VBA code to perform it. I have tried compare string method, but since the word in sheet1 column A is in the middle cell of of column A of sheet2 , I do not know how to create it.
Column A Column B
1 FIX 1000
2 FIX 1000
3 FIX/IO 100000
4 INV/IO 100000
5 FLT 1000
6 FLT 1000
7 FLT/DLY/SP 100000
8 PO 100000
9 FIX 1000
10 FIX 1000
11 FIX/Z 1000
12 INV 100000
I am encountering a loop through problem in a big data set in Excel. I am trying to loop through a column in Sheet1, as long as the column contain a specified word which defined in a column in Sheet2, the next column same row in sheet2 will appear in the same row of next column in Sheet1.
The simplified example as follows:
Table 1 in Sheet1:
Column A Column B
1 SP 100000
2 PO 100000
3 IO 100000
4 INV 100000
A big data in Sheet2:
Column A Column B
1 FIX 1000
2 FIX 1000
3 FIX/IO 1000
4 INV/IO 1000
5 FLT 1000
6 FLT 1000
7 FLT/DLY/SP 1000
8 PO 1000
9 FIX 1000
10 FIX 1000
11 FIX/Z 1000
12 INV 1000
I want to match the word in column A of sheet1 with column A in sheet2. If it matches, the same row of Column B in sheet2 will be same as column B in sheet1.
For example, "A1"in sheet1 is "SP", "A7" in column A of sheet2 also contains "SP", so "B7" in sheet2 will be 100000. The revised data set should be same as below. Can anyone help me with to create a VBA code to perform it. I have tried compare string method, but since the word in sheet1 column A is in the middle cell of of column A of sheet2 , I do not know how to create it.
Column A Column B
1 FIX 1000
2 FIX 1000
3 FIX/IO 100000
4 INV/IO 100000
5 FLT 1000
6 FLT 1000
7 FLT/DLY/SP 100000
8 PO 100000
9 FIX 1000
10 FIX 1000
11 FIX/Z 1000
12 INV 100000