Welcome Guest
Log In | Register )
You last visited December 3, 2016, 8:36 am
All times shown are
Eastern Time (GMT-5:00)

Maybe I am not saying what I mean

Topic closed. 18 replies. Last post 11 years ago by Sandy K.

Page 1 of 2
PrintE-mailLink
lottaloot's avatar - AvatarZ56
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
Posted: December 16, 2005, 1:57 am - IP Logged

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's avatar - AvatarZ56
    Redford/MI
    United States
    Member #3396
    January 18, 2004
    4867 Posts
    Offline
    Posted: December 16, 2005, 2:06 am - IP Logged

    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's avatar - AvatarZ56
      Redford/MI
      United States
      Member #3396
      January 18, 2004
      4867 Posts
      Offline
      Posted: December 16, 2005, 2:10 am - IP Logged

      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's avatar - AvatarZ56
        Redford/MI
        United States
        Member #3396
        January 18, 2004
        4867 Posts
        Offline
        Posted: December 16, 2005, 2:16 am - IP Logged

        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's avatar - AvatarZ56
          Redford/MI
          United States
          Member #3396
          January 18, 2004
          4867 Posts
          Offline
          Posted: December 16, 2005, 2:20 am - IP Logged

          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's avatar - Lottery-022.jpg

            United States
            Member #17834
            June 28, 2005
            2083 Posts
            Offline
            Posted: December 16, 2005, 2:38 am - IP Logged

            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!!!


              United States
              Member #17555
              June 22, 2005
              5582 Posts
              Offline
              Posted: December 16, 2005, 2:51 am - IP Logged

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

                powerplayer's avatar - Lottery-022.jpg

                United States
                Member #17834
                June 28, 2005
                2083 Posts
                Offline
                Posted: December 16, 2005, 2:56 am - IP Logged

                Hey Pac,

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

                Good luck to everyone!!!

                  lottaloot's avatar - AvatarZ56
                  Redford/MI
                  United States
                  Member #3396
                  January 18, 2004
                  4867 Posts
                  Offline
                  Posted: December 16, 2005, 3:05 am - IP Logged

                  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) Big Smile

                  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's avatar - Lottery-022.jpg

                    United States
                    Member #17834
                    June 28, 2005
                    2083 Posts
                    Offline
                    Posted: December 16, 2005, 3:09 am - IP Logged

                    Good job lottaloot...!!

                    Good luck to everyone!!!


                      United States
                      Member #17555
                      June 22, 2005
                      5582 Posts
                      Offline
                      Posted: December 16, 2005, 3:16 am - IP Logged

                      Hey Pac,

                      I wish VB was in a english that I could understand  ...............................Confused 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's avatar - Lottery-022.jpg

                        United States
                        Member #17834
                        June 28, 2005
                        2083 Posts
                        Offline
                        Posted: December 16, 2005, 3:21 am - IP Logged

                        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!!!


                          United States
                          Member #17555
                          June 22, 2005
                          5582 Posts
                          Offline
                          Posted: December 16, 2005, 3:41 am - IP Logged

                          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's avatar - AvatarZ56
                            Redford/MI
                            United States
                            Member #3396
                            January 18, 2004
                            4867 Posts
                            Offline
                            Posted: December 16, 2005, 11:05 am - IP Logged

                            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's avatar - graphic pub.jpg
                              Clarkston, Michigan
                              United States
                              Member #76
                              January 6, 2002
                              8528 Posts
                              Offline
                              Posted: December 16, 2005, 11:16 am - IP Logged

                              wow lottaloot, you have come a long way in Excel

                              Sandy