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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=544439

Finding all the values and storing them
 

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


cheer

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=54443

Finding all the values and storing them
 

the code isn't putting anything on Sheet

--
jhahe
-----------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...fo&userid=2359
View this thread: http://www.excelforum.com/showthread.php?threadid=54443

Finding all the values and storing them
 

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" a écrit dans
le message de news: ...

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

Finding all the values and storing them
 

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

Between a rock and a hard plac

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=54443