Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple solution needed

    I want to automate a process that looks for a value in column 1 and then writes a corrosponding number to another cell in the same row

    example-
    1-Bob_Smith-x-x-x-x-10

    The number is in columnA the name columnB and the new number written would go in ColumnG
    Select case seems to me the natural way to go, since the numbers are predetermined.

    Any help would be greatly appreciated.
    Please see my message regarding online tutorials for Excel programming.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Simple solution needed

    Hi,
    Is there a reason for not using a lookup for this?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple solution needed

    How is that applied in Excel?
    I saw in the insert menu Insert-Vlookup.

    I don't have ODBC or MS Query capabilities here, are those required to do lookups?

    I'll read up on it at that site you suggested...

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Simple solution needed

    Hi,
    You don't need ODBC for this or MSQuery but you do need to have the value lists in your workbook. For example, if you have a list of values in two columns (where column 1 represents the values you might have in column A on your main sheet and Column 2 represents the corresponding values to be put into column G) you can name this range (e.g. myvalues) and then in column G on your main sheet you would enter =vlookup(A1,myvalues,2,false) to return the correct value depending on what is in cell A1.
    I don't know if that's clear, if not, let me know and I'll attach a simple spreadsheet showing what I mean.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple solution needed

    Very cool,
    Thanks alot Rory
    I need to learn this range stuff more.
    While I'm here.. would this same type of system work for a type of validation function?
    For instance an If statement?
    Can you use IF statements in Excel?

    I guess I better read those VBATUTOR files I downloaded...;-)
    Thanks again.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple solution needed

    Hi,

    Yes, you could use this for a validation. For validation, have a look at "Data, Validation"- it's pretty cool. You can have custom formula in the validation, which I would guess (in the "Custom" selection under "Allow") ciould use lookups.

    If statements? Yes:

    =if(A1=1,"It's ONE!","It's something else")

    (put this in any cell except A1)
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple solution needed

    Hey Jazzman:
    Mind sharing where you geot the VBATUTOR file from. If you could send me a download address, I would greatly appreciate it.
    Stephen
    sstollma@juno.com

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple solution needed

    Here's the link <A target="_blank" HREF=http://www.vbatutor.com/vbatutor.htm>to the vbatutor site</A>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple solution needed

    Not sure how to do this but someone else here had suggested this site for a VBA Tutorial ... I downloaded it and it's one of the best explained tutorials I've seen on the subject. Here is the link

    http://www.appdev.com/newstuff/default.asp...ge=vbastuff.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •