Excel Macro help please!

Maccy

Maccy

Maccy

Commissario
Joined
23 Nov 2004
Posts
42,833
Location
Herts
I'm trying to produce a macro in Excel that auto populates a cell depending on the value of another cell.

For example, if A1=John Smith, then I want B2=Man, as an example.

A1 is currently a dropdown list of about 20 names, so I can't do a long if statement in the formula bar. Macros are new to me so struggling a little bit!

At the moment, I've put 3 names into a macro like this -

Code: 
Function role_name(roleVar As String) As Long
   If roleVar = "John" Then
      role = C
      
   ElseIf roleVar = "James" Then
      role = CT
      
   ElseIf roleVar = "Matthew" Then
      role = C    
  
   ElseIf roleVar = "Victor" Then
      role = SC    
  
   Else
      role = NA
   End If
   
End Function

In the cell, I'm putting "=role_name(A1)" but getting back a REF error. Can anyone help?!
 
Wouldn't a vlookup work for something this simple?

On your data validation sheet (where you sort your dropdown picklist) next to it create the values that wish to populate the next cell with on your main sheet.

then use the vlookup fomula in the next cell so that when ever john smith is selected in the dropdown box the value man appears next to it.

Something like this:

https://dl.dropbox.com/u/908850/Wannabe.xlsx
 
I can't access it here either at work :( (poxy lockout from dropbox's)

what I mean tho is on your dropdown list config sheet (data validation) set it up like this,

A B
John Man
Peter Smith
Leroy Peters
Steve Long

then on your main sheet next to your dropdown pick list in in col B type the correct version of this to match your data sheet:

@VLOOKUP(A1,picklist!A:B,2,false)

that will then pull back the rest of the data that is assgined to option selected.

Email in trust and I will email the file to you when I get home, I created it for Wannabedammed to use as an example.
 
VLookup sounds good for this, it is going to be nice and easy to make changes to the database for somebody without having to edit the VBA function.


Edit: also - the file opens fine here? - it's a good example. What version of excel are you using?
 
m3csl2004 said:
VLookup sounds good for this, it is going to be nice and easy to make changes to the database for somebody without having to edit the VBA function.


Edit: also - the file opens fine here? - it's a good example. What version of excel are you using?
Click to expand...

Cheers fella :) I have emailed that file to Maccy now, hopefully it makes sense.
 
Another vote for VLOOKUP, will work fine for this and its easy to change the table without dabbling with code.

The other thing I found essential when trying to get this working for me on a slightly different application was using absolute cell values so when I copied and pasted the VLOOKUP command into lots of cells it still looked up the same tables and positions instead of shifting the numbers like excell's normal way.
Here's mine, it looks at column A on sheet 2 for data and puts the adjacent answer from column B
Code: 
=VLOOKUP(A2,Sheet2!$A$2:$B$45,2)
 
Code: 
Function role_name(roleVar As String) As Long
   If roleVar = "John" Then
      role = C
      
   ElseIf roleVar = "James" Then
      role = CT
      
   ElseIf roleVar = "Matthew" Then
      role = C    
  
   ElseIf roleVar = "Victor" Then
      role = SC    
  
   Else
      role = NA
   End If
   
End Function

There are three issues with that code.

1) you are trying to assign a string to a function that wants to return a long. The first line should read:
Function role_name(roleVar As String) As String

2) What is/are C/CT/SC ? are they variables or are they the pieces of text you want the function to return? if it's the later, then you need to enclose them in quotes otherwise the function will just return a 0 (because you haven't given C a value in the function for example)

role = C means set role to whatever C equals
role = "C" means set role to equal "C"

however...

3) What is 'role' ? As it is, you are assigning a string to 'role' and then discarding it as the function exits. if you want a function to return a variable then you must assign a variable to that function so role = "C" needs to be changed to Role_name = "C"

so, this will work:

Code: 
Function role_name(roleVar As String) As String
   If roleVar = "John" Then
      role_name = "C"
      
   ElseIf roleVar = "James" Then
      role_name = "CT"
      
   ElseIf roleVar = "Matthew" Then
      role_name = "C"
  
   ElseIf roleVar = "Victor" Then
      role_name = "SC"
  
   Else
      role_name = "NA"
   End If
   
End Function



One piece of advice though, lots of if..then..elseif's make for messy code as you are seeing; use Select...Case instead. SO much easier.

Code: 
Function role_name(roleVar As String) As String
    Select Case roleVar
        Case "John"
            role_name = "C"
        Case "James"
            role_name = "CT"
        Case "Mathew"
            role_name = "C"
        Case "Victor"
            role_name = "SC"
        Case Else
            role_name = "NA"
    End Select
End Function

Much neater :) Hope this helps!


peige said:
Another vote for VLOOKUP, will work fine for this and its easy to change the table without dabbling with code.

The other thing I found essential when trying to get this working for me on a slightly different application was using absolute cell values so when I copied and pasted the VLOOKUP command into lots of cells it still looked up the same tables and positions instead of shifting the numbers like excell's normal way.
Here's mine, it looks at column A on sheet 2 for data and puts the adjacent answer from column B
Code: 
=VLOOKUP(A2,Sheet2!$A$2:$B$45,2)
Click to expand...

Try naming the range of the lookup table, named ranges make things easier. VLOOKUP(A2,Table_Name,2) for example. You can put this in every cell and not have to worry about absolute addressing :)
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom