Cells.Find with a variable

[El Bee]

Reposting since I can't find the previous question.

I have a spread sheet that contains 2 columns and multiple rows:

WORKSHEET1
ProgamName Level
Prog1 29
Prog2 27
Prog3 25
Name1 95
Name2 97
Name3 98


I want to look for all "Prog1" values in another worksheet2 (same file) and
check the cell to the right if the value is equal to "29" from value shown
above. If the 2 values match then I want to copy the entire row from
workshee2 and paste them into worksheet3 using the transpose option.
Each row would be transposed to the next empty column in worksheet3

I want to repeat this process for each row in workshee1

Is this possible ?

Thanks,

El Bee

 

[PCLIVE]

You'll need VBA for this.

Though there are probably other ways, here is one way.

Sub test()
Sheets(2).Activate
For Each cell In Range("A1:A100")
If cell.Value = "Prog1" _
Then
If cell.Offset(0, 1).Value = "29" _
Then
r = cell.Row
c = cell.Column
Rows(cell.Row & ":" & cell.Row).Copy
Sheets(3).Activate
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Sheets(2).Activate
Else
End If
Else
End If
Next cell

End Sub




HTH,
Paul

 

[PCLIVE]

I had some unnecessary code that I used for testing in the last one.
I've removed it in the below code.

Sub test()
Sheets(2).Activate
For Each cell In Range("A1:A100")
If cell.Value = "Prog1" _
Then
If cell.Offset(0, 1).Value = "29" _
Then
Rows(cell.Row & ":" & cell.Row).Copy
Sheets(3).Activate
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets(2).Activate
Else
End If

Else
End If

Next cell

End Sub

--

 

[El Bee]

Paul,

First thanks for the help!

I tried your macro and I get an error, "subscript out of range" as I step
through it. The error appears with the first statement,
"Sheets(2).Activate". I changed the (2) to the worksheets name (Ecomsec)
also the other worksheet names in your example.
I'm stumped; I would expect this error with the "For Each Cell" statement;
thinking the range was incorrect but I changed that and the error still
appears when come to the Sheets(Ecomsec) statement.

Any ideas?

Lab

 

[El Bee]

Paul,

You can ignore my previous post. I forgot the quote marks around the
worksheet name. doh!

El Bee