Help in writing a VBA code

Joined
May 12, 2011
Messages
20
Reaction score
0
Hi,
I am writing a VBA code for searching for a partcular text in a row, and if some cell in the row matches with the text, i want to copy the values from the next 2 rows of the same column of that cell to a column in another worksheet..

Can someone help me in writing a code for that???

Please
 
Joined
May 11, 2011
Messages
14
Reaction score
0
Try using this code. Text that you seek enroll in cell A1 on Sheet1, a row in which the text should look for a 2. on sheet1. The results are copied to Sheet2


Dim texttest As String
texttest = Sheets("Sheet1").Cells(1, 1).Value
For j = 1 To 100
If Sheets("Sheet1").Cells(2, j).Value = texttest Then
Sheets("Sheet2").Cells(1, 1) = Sheets("Sheet1").Cells(3, j)
Sheets("sheet2").Cells(2,1) = Sheets("sheet1").Cells(4, j)
End If
Next j
 
Joined
May 12, 2011
Messages
20
Reaction score
0
Actually the situation is bit more comple... let me try to put it in a way I can..
first row in sheet 1 has the names of machines for a particular part
its second and third rows have set up time and process time for corresponding machines for that particular part
the 4th row has the name of the machines for the next part..the set up time and process time in the next 2 rows and it goes on like that....
there are more than 100 parts...
in sheet 2 i have the name of a machine...i have to check if that machine is present for each part and if it is there the corresponding set up and process time have to be pasted in sheet 2 against the part name...
i hope i have made the scenario clear
please help in this....i have benn stuck up and am unable to clear it..
thanks a lot for the reply.. :bow:
 
Joined
May 11, 2011
Messages
14
Reaction score
0
Just to see i have good understand (i don't speak english good, so i'm sorry)

example:
Sheet1.
row1: part1 part2 part3 part4
row2: 60 120 50 80
row3: 20 90 110 100
row4: part4 part3 end part2

Sheet2
you tipe
part2

and than you want what? setup time= 170 and process time = 90 ???
 
Joined
May 12, 2011
Messages
20
Reaction score
0
row
1 part1 mach1 mach2 mach 3....
2 set up 1 set up 2 set up 3......
3 proce1 proce2 proce3....
4 part 2 mach 1 mach2 mach 3
5 setup1 setup2 setup 3
6 proce1 proce2 proce3....
7
it goes on like this... i have to find the set up time and process time for a particular machine in all the parts and paste it in another worksheet
 
Joined
May 11, 2011
Messages
14
Reaction score
0
So, when you enroll a machine name, the second sheet will print lists of parts and their time?

i can't attach excel file, so i put in Word document.

i hope that is this solution for your problem.

:confused:
 

Attachments

  • example.doc
    45 KB · Views: 211
Joined
May 12, 2011
Messages
20
Reaction score
0
thanks a lot for your help...
now im stuck up somewhere else:confused:
i have to copy first 7 letters of all the cells in column A to the corresponding cells in columns B..
Can you please help in that???

I am new to VBA... :blush:
 
Joined
May 11, 2011
Messages
14
Reaction score
0
for it does not need a program - it is easier this way:

in cell B1 type

=left(A1,7)

and copy in entyre row
 
Joined
May 12, 2011
Messages
20
Reaction score
0
I know that function...but its a part of a very big VBA code... so there is no other option other than putting it in the code... as the entire code will be executed at a time
 
Joined
May 11, 2011
Messages
14
Reaction score
0
sorry, i entered before i finshed the post...

Dim i As Integer
Dim text As String

For i = 1 To 1000
text = Sheets("Sheet1").Cells(i, 1).Value
Sheets("Sheet1").Cells(i, 2).Value = Left(text, 3)
Next i
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top