bony_tony
12/12/2006 10:45:00 PM
Thanks for that. It's not what I'm looking for though.
I'm not sure exactly what you were trying to achieve there (as the
macro failed on line - Set rngFound =
rngToSearch.Find(What:=wks.Range("$IV$66000"),LookAt:=xlPart,
MatchCase:=False)) - ) ,but it looks like you were trying to sort a
range for me.
I have a list full of clients (whose names are in B3:Bxxx) with various
information about them - each client has 1 row to themselves. They
also have a file saved somewhere with more info on them.
I want to record a macro that lets the user input some more data about
a specific client on the list.
For them to select which client they want to update, I would like a
combobox to pop up giving them an option to select which client they
are updating (my understanding is that I need to add a userform?) Once
they have selected the client they want - and pressed 'OK', I would
like to store the selection in a string variant, for me to use later
(to open the clients saved file (among other things)). I would also
like the contents of the combobox listed in alphabetical order.
A step by step guide on how to add the userform/combobox would be
appreciated.
Thanks
Tony
ufo_pilot wrote:
> Sub AddNames()
> Dim myFind As Integer
> Dim rng As Range
> Dim rngToSearch As Range
> Dim rngFound As Range
> Set wks = ActiveSheet
> Set rngToSearch = Worksheets("InputSheet").Range("A:A")
> Set rngFound = rngToSearch.Find(What:=wks.Range("$IV$66000"), _
> LookAt:=xlPart, MatchCase:=False)
> If Not rngFound Is Nothing Then
> Worksheets("SheetToGetInfo").Range("CellOfInfo").Copy
> rngFound.PasteSpecial xlValues
> Range("D:D").Select
> Application.CutCopyMode = False
> Selection.Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Else
> MsgBox myFind & " was not found"
> End If
> End Sub
> ___________________________
> __________________________
>
> this is what it does:
>
> Set rngToSearch = Worksheets("InputSheet").Range("A:A")
> - this will search in column A of the "InputSheet"
> - rename this to what your sheet is called,
> and change the column to which column you need the input to copy to...
>
> ("$IV$66000"), _
> - this is what it is looking for ( here it is an empty cell ), that way the
> new data every day is pasted under the last filled cell in the column you
> specifed.
>
> Range("A:A").Select
> (- It selects the column that the names were copied to)
> Application.CutCopyMode = False
> Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> (- The first row in the column to be alphbetized is A1)
>
> You will need to edit the columns and cells to your needs.
> HTH
>
>
> "bony_tony" wrote:
>
> > Hi, I'm new to VBA
> >
> > I've got a couple of macros that edit/inputs customers into a
> > spreadsheet
> >
> > I would like one macro to give the user a drop-down combobox for them
> > to select a specific client (whose names will be in cells B3:Bxxx) I
> > would then like to store the selected client in a string variant for me
> > to use later on. Also, I would like the list of clients to be stored
> > alphabetically.
> >
> > I've browsed loads of other similar topics, but I can't get any of the
> > answers to work. :-(
> >
> > If someone could give me step-by-step instructions of how to get this
> > part of my macro working, it would be appreciated.
> >
> > Thanks
> > Tony
> > AMP
> >
> >