Loop inside a Loop
 

I am novice in vba so I will try to explain what I need and show yo
what I have.

I have about 200 rows in Column A of Sheet 1 (all number format, al
unique)

I want to have the code select the first number in the column (A2)

Then

Go to sheet 15 Column A to see if the number is there. If it isn'
there then put it in the next available row in Column A.

I want this to loop through all the 200 rows in Column A of Sheet 1 an
do the same thing.

Here is what I have so far, I know it is probably very tedious. M
problem is I don't know how to store the first activecell to referenc
to:

If Sheet1.Visible = xlSheetHidden Then
Sheet1.Visible = xlSheetVisible
End If
Sheet1.Activate
Range("A2").Select
If ActiveCell.Offset(0, 5).Value < "" Then
If Sheet15.Visible = xlSheetHidden Then
Sheet15.Visible = xlSheetVisible
End If
Sheet15.Activate
Range("A2").Select
Do
If ActiveCell.Value < FirstActiveCell.Value Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = FirstActiveCell.Value O
ActiveCell.Row = 1000
If ActiveCell.Row = 1000 Then
Range("A2").Select
Do
If IsEmpty(activcell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = FirstActiveCell.Valu

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

Loop inside a Loop
 

Hi jhahes,

You might want to try something like this ...

Sub AddUniquesToSheet()
Dim wsLook As Worksheet, wsFind As Worksheet
Dim rngFind As Range
Dim LastRow As Long, i As Long, SheetLastRow
Set wsLook = Sheets("Sheet1")
Set wsFind = Sheets("Sheet15")
LastRow = wsLook.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
Set rngFind = wsFind.Range("A:A").Find(wsLook.Range("A" & i),
lookat:=xlWhole, MatchCase:=True)
If rngFind Is Nothing Then
SheetLastRow = wsFind.Cells(Rows.Count, "A").End(xlUp).Row
wsFind.Cells(SheetLastRow + 1, "A").Value = wsLook.Range("A" &
i).Value
End If
Set rngFind = Nothing
Next i
End Sub

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"jhahes" wrote in
message ...

I am novice in vba so I will try to explain what I need and show you
what I have.

I have about 200 rows in Column A of Sheet 1 (all number format, all
unique)

I want to have the code select the first number in the column (A2)

Then

Go to sheet 15 Column A to see if the number is there. If it isn't
there then put it in the next available row in Column A.

I want this to loop through all the 200 rows in Column A of Sheet 1 and
do the same thing.

Here is what I have so far, I know it is probably very tedious. My
problem is I don't know how to store the first activecell to reference
to:

If Sheet1.Visible = xlSheetHidden Then
Sheet1.Visible = xlSheetVisible
End If
Sheet1.Activate
Range("A2").Select
If ActiveCell.Offset(0, 5).Value < "" Then
If Sheet15.Visible = xlSheetHidden Then
Sheet15.Visible = xlSheetVisible
End If
Sheet15.Activate
Range("A2").Select
Do
If ActiveCell.Value < FirstActiveCell.Value Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = FirstActiveCell.Value Or
ActiveCell.Row = 1000
If ActiveCell.Row = 1000 Then
Range("A2").Select
Do
If IsEmpty(activcell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = FirstActiveCell.Value


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

Loop inside a Loop
 

Hi Zack,

Your code worked excellent. Thank you for your help. Could I ask you
to please show me how to add a parameter within the code.

How do I get the code to do the exact same thing but only put the
entries from Sheet1 to Sheet15 where Sheet1 column F = YES

maybe something like this
if activecell.offset(0,5).value = "YES" then
put activecell.value in Sheet 15

Next

Thank you for all your help


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

Loop inside a Loop
 

Great, glad it works.

To do what you're asking, just after the loop (For i = ...) you can use
something like this ...

If wsLook.Range("F" & i).Value = "YES" Then

Be sure to add the ending "End If" statement though. That should pretty
much do it. You can add as many checks as you would like.

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"jhahes" wrote in
message ...

Hi Zack,

Your code worked excellent. Thank you for your help. Could I ask you
to please show me how to add a parameter within the code.

How do I get the code to do the exact same thing but only put the
entries from Sheet1 to Sheet15 where Sheet1 column F = YES

maybe something like this
if activecell.offset(0,5).value = "YES" then
put activecell.value in Sheet 15

Next

Thank you for all your help


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

Loop inside a Loop
 

Hi Zack,

the code works great. Could you please help me do one thing.

I want the code to do the exact same thing as it is currently doing.

However

can I have this condition

after the code executes or puts the new numbers in the new range if
there are any new numbers I want it to do this

If the column K on Sheet1 = "YES" then
put that value in Sheet15 Column D of the same item number


Thanks


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

Loop inside a Loop
 

Hi jhales,

I'm not understanding you fully here. Can you give a couple of examples?
If need be, you can email it to me.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM



"jhahes" wrote in
message ...

Hi Zack,

the code works great. Could you please help me do one thing.

I want the code to do the exact same thing as it is currently doing.

However

can I have this condition

after the code executes or puts the new numbers in the new range if
there are any new numbers I want it to do this

If the column K on Sheet1 = "YES" then
put that value in Sheet15 Column D of the same item number


Thanks


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

Loop inside a Loop
 

Right now,

the code does this

I have about 200 rows of data - item numbers in Sheet1 - Column a
(A2...)

The code you helped me with does the following

It runs through these rows of data in Sheet1 - Column A to the
end.xldown

Then it opens Sheet15 columnA (A2) and if it finds the number it does
nothing, if it doesn't find the number it adds the number to the next
open row.

This code loops through all the item number on Sheet1

The only condition i put in was this

If wsLook.Range("F" & i).Value = "YES" Then

rest of code.


I want to do the following.

After the code puts the new entries in the sheet if there are any. Is
there anyway to

1. Look through Sheet1 Column j (j2...last row with data....)

2. If the cell contains the word YES then go to Sheet15 and find the
corresponding item number - Column A (A2....last row with data) and put
the word YES in Sheet15 Column D of the item number that it matched

I hope this helps

Thanks


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