Redford/MI United States
Member #3,396
January 18, 2004
4,867 Posts
Offline
Sub NoLoop()
Cells.Find(What:="Find Me", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate End Sub
Redford/MI United States
Member #3,396
January 18, 2004
4,867 Posts
Offline
Sub UniqueList()
Dim rListPaste As Range
Dim iReply As Integer
On Error Resume Next
Set rListPaste = Application.InputBox _
(Prompt:="Please select the destination cell", Type:=8)
If rListPaste Is Nothing Then
iReply = MsgBox("No range nominated," _
" terminate", vbYesNo + vbQuestion)
If iReply = vbYes Then Exit Sub
End If
Range("A1", Range("A65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=rListPaste.Cells(1, 1), Unique:=True
End Sub
Redford/MI United States
Member #3,396
January 18, 2004
4,867 Posts
Offline
And once I figure out what I am suppose to type, is there a way to get into the macro section or where ever I am suppose to put these formulas besides hitting the record macro & then stopping it & then editing it?
United States
Member #17,833
June 28, 2005
2,087 Posts
Offline
Sub UniqueList()
Dim rListPaste As Range
Dim iReply As Integer
On Error Resume Next
Set rListPaste = Application.InputBox _
(Prompt:="Please select the destination cell", Type:=8)
If rListPaste Is Nothing Then
iReply = MsgBox("No range nominated," _
" terminate", vbYesNo + vbQuestion)
If iReply = vbYes Then Exit Sub
End If
Range("A1", Range("A65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=rListPaste.Cells(1, 1), Unique:=True
End Sub
Lottaloot,
This is a VB script...I'm pretty sure of it.
This is a very complicated script at least for me.. If I'm decyphering correctly then this will pop a box up and ask you to choose a cell.
IF no range is nominated then the script terminates. I think. The Range("A1", Range("A65536").End(xlUp)).AdvancedFilter (not sure what advnaced filter is for...???
This part I'm not sure of fully but belive it's refers back to this line:
If rListPaste Is Nothing Then
iReply = MsgBox("No range nominated," _
" terminate", vbYesNo + vbQuestion)
If iReply = vbYes Then Exit Sub
So you would put your range of your information here so when you select the cell for destination it will have a reference where to look.
This one:
Sub NoLoop()
Cells.Find(What:="Find Me", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate End Sub
I think is a macro vb script....leaning more towards a macro since it has loop in it.
You can go to tools then macro then record macro and you will get a box that pops up.
Find the macro you want to use and just edit it. If you want the module box to pop up so you can write your own or edit that way then just hit....alt + F11 then choose insert then module.
I'm not fully sure what the differences are between macro,MS script editor and VB editor but, maybe someone else will know that answer.
Not sure if that will help...hope it does.
Hope fully someone else will be able to explain more
Redford/MI United States
Member #3,396
January 18, 2004
4,867 Posts
Offline
I did it!
I just copied it exactly like it said below & ran it. (no sense in being afraid to play around with it--It's not like I am going to break it)
Type "Find Me"in cell IV65336
Sub NoLoop()
Cells.Find(What:="Find Me", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub