Finding all the values and storing them

I am novice in VBA.

Can someone please help me do the following.

I have 2 sheets

Sheet1 - contains all the information
Sheet2 - where I want the code to put some of the information

I would like the code to start in Sheet1 - A2:A100. If the cell = 61538 then
put the corresponding B column value {offset (0,1)} in Sheet2 A2.

Go through all 100 rows and do the same....

So if it finds the first occurence of 61538 in Sheet1 - A7 it will put the offset(o,1).value in Sheet2 - A2.

Then if the next occurence is Sheet1 - A55 it will put the offset(0,1).value in Sheet2 - A3 *** the next open cell***

Thank you for any help on this matter.....

Josh

try


Sub a()
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Worksheets("Sheet1").Range("A2")
Set rng1 = Worksheets("Sheet2").Range("A2")

Do
If rng1 = 61538 Then
rng2 = rng1.Offset(, 1)
Set rng2 = rng2.Offset(1)
End If

Set rng1 = rng1.Offset(1)
Loop Until rng1.Row = 100
MsgBox "done"
End Sub


cheers

the code isn't putting anything on Sheet2

There was a typo in tony's code (set rng1 twice instead of set rng2)

HTH
--
AP

'--------------------
Sub a()
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Worksheets("Sheet1").Range("A2")
Set rng2 = Worksheets("Sheet2").Range("A2")

Do
If rng1 = 61538 Then
rng2 = rng1.Offset(, 1)
Set rng2 = rng2.Offset(1)
End If

Set rng1 = rng1.Offset(1)
Loop Until rng1.Row = 100
MsgBox "done"
End Sub
'-------------------------
"jhahes" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> the code isn't putting anything on Sheet2
>
>
> --
> jhahes
> ------------------------------------------------------------------------
> jhahes's Profile:
> http://www.excelforum.com/member.php...o&userid=23596
> View this thread: http://www.excelforum.com/showthread...hreadid=544439
>

Thanks for sorting out the typo. I was in a bit of a rush last night and didn't have time to test it.

Between a rock and a hard place