Maybe I am not saying what I meanPrev TopicNext Topic

• lottaloot

  

  Redford/MI
  United States
  Member #3,396
  January 18, 2004
  4,867 Posts
  Offline
  Dec 16, 2005, 1:57 am

  ▲▼

  Sub NoLoop()
  Cells.Find(What:="Find Me", After:=[A1], LookIn:=xlFormulas, _
  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False).Activate
  End Sub

   

   

   

   

   

  L ttaL   T
• lottaloot

  

  Redford/MI
  United States
  Member #3,396
  January 18, 2004
  4,867 Posts
  Offline
  Dec 16, 2005, 2:06 am

  ▲▼

  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
  

  L ttaL   T
• lottaloot

  

  Redford/MI
  United States
  Member #3,396
  January 18, 2004
  4,867 Posts
  Offline
  Dec 16, 2005, 2:10 am

  ▲▼

  I do not know exactly what the above is called but I think it is writing your own formulas for excel. 

  I would like to know when they state "range" are they wanting the exact cell ranges put in here or is this the language that VB uses??

  L ttaL   T
• lottaloot

  

  Redford/MI
  United States
  Member #3,396
  January 18, 2004
  4,867 Posts
  Offline
  Dec 16, 2005, 2:16 am

  ▲▼

  It's things like this that confuse me with writing or reading these things. 

  Sub Demo()      Dim MyRange As Range     Set MyRange = Application.InputBox _      (Prompt:="Select any range", Title:="Demo", Type:=8)      MyRange.SelectEnd Sub
  

  Are they instructing me to "set my range"
  or is that actually what I am suppose to type??
  and excel will understand it.
  

  L ttaL   T
• lottaloot

  

  Redford/MI
  United States
  Member #3,396
  January 18, 2004
  4,867 Posts
  Offline
  Dec 16, 2005, 2:20 am

  ▲▼

  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?  

  L ttaL   T
• powerplayer

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  Dec 16, 2005, 2:38 am

  ▲▼
  Quote: Originally posted by lottaloot on December 16, 2005

  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

  Powerplayer

  Good luck to everyone!!!
• pacattack05

  United States
  Member #17,554
  June 22, 2005
  5,582 Posts
  Offline
  Dec 16, 2005, 2:51 am

  ▲▼

  Lotta and Power, learn english, you're in America now.....LOL
• powerplayer

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  Dec 16, 2005, 2:56 am

  ▲▼

  Hey Pac,

  I wish VB was in a english that I could understand  ............................... my fingers type faster then I can think LOL

  Good luck to everyone!!!
• lottaloot

  

  Redford/MI
  United States
  Member #3,396
  January 18, 2004
  4,867 Posts
  Offline
  Dec 16, 2005, 3:05 am

  ▲▼

  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

  And it worked! 

  
  

  L ttaL   T
• powerplayer

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  Dec 16, 2005, 3:09 am

  ▲▼

  Good job lottaloot...!!

  Good luck to everyone!!!
• pacattack05

  United States
  Member #17,554
  June 22, 2005
  5,582 Posts
  Offline
  Dec 16, 2005, 3:16 am

  ▲▼
  Quote: Originally posted by powerplayer on December 16, 2005

  Hey Pac,

  I wish VB was in a english that I could understand  ............................... my fingers type faster then I can think LOL

  You two are the BOMB!!

  The only program I ever wrote ( typed ) was about 1979. It was a game program for a Commodore 128. I copied it from a computer magazine. LOL......

  If x=20 then go to X,:39.277, then next,

  If 25 next,

  Go to- then next, if sprite = pixel, then go to next.....LOL ( this last line is just joking.)
• powerplayer

  

  United States
  Member #17,833
  June 28, 2005
  2,087 Posts
  Offline
  Dec 16, 2005, 3:21 am

  ▲▼

  Pac,

  "The only program I ever wrote ( typed ) was about 1979. It was a game program for a Commodore 128."

  These were the good old days !!!

  Powerplayer

  Good luck to everyone!!!
• pacattack05

  United States
  Member #17,554
  June 22, 2005
  5,582 Posts
  Offline
  Dec 16, 2005, 3:41 am

  ▲▼

  Boy the way Glen Miller played.

  Songs that made the hit parade. Guys like us, we had it made. Those were the days.

  And you knew where you were then. Girls were girls and men were men.

  Mister we could use a man like Herbert Hoover again.

  Didn't need no welfare state. Everybody pulled his weight.

  Gee our old Lasalle ran great, those were the days.
• lottaloot

  

  Redford/MI
  United States
  Member #3,396
  January 18, 2004
  4,867 Posts
  Offline
  Dec 16, 2005, 11:05 am

  ▲▼

  Does anyone know how to add the find next to the code below?

  I do not want it to loop.

  Sub NoLoop()
      Dim SearchString
     
      SearchString = 123
      Cells.Find(What:=SearchString, After:=[A1], LookIn:=xlFormulas, _
      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False).Activate
  End Sub

  L ttaL   T
• Sandy K

  

  Clarkston, Michigan
  United States
  Member #76
  January 6, 2002
  8,528 Posts
  Offline
  Dec 16, 2005, 11:16 am

  ▲▼

  wow lottaloot, you have come a long way in Excel

  Sandy