Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Select Case Function
COLUMN A COLUMN B
Apple 1 Orange 2 Kiwi 3 Apple 1 etc. What should be the VBA code to auto fill the column B given the values of column A. A couple of years ago I solved this problem but can not remember how I did that, all I remember is that I created a function (that included the select case because of the number of entries) and accessed it from the worksheet. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Select Case Function
Couldn't you just use VLOOKUP?
"Catalin" wrote: COLUMN A COLUMN B Apple 1 Orange 2 Kiwi 3 Apple 1 etc. What should be the VBA code to auto fill the column B given the values of column A. A couple of years ago I solved this problem but can not remember how I did that, all I remember is that I created a function (that included the select case because of the number of entries) and accessed it from the worksheet. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Select Case Function
You're right, this could be a solution, but remembering the fact that I
already did this once using VBA I'd like to get some advice on how to solve this using VBA. Many thanks for your answer. "Martin" wrote: Couldn't you just use VLOOKUP? "Catalin" wrote: COLUMN A COLUMN B Apple 1 Orange 2 Kiwi 3 Apple 1 etc. What should be the VBA code to auto fill the column B given the values of column A. A couple of years ago I solved this problem but can not remember how I did that, all I remember is that I created a function (that included the select case because of the number of entries) and accessed it from the worksheet. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Select Case Function
What I managed to do is to get the value from the worksheet into the VBA
function, processed the information but I still have difficulties in sending it back to the worksheet in a corresponding cell. Your help is much appreciated. "Martin" wrote: Couldn't you just use VLOOKUP? "Catalin" wrote: COLUMN A COLUMN B Apple 1 Orange 2 Kiwi 3 Apple 1 etc. What should be the VBA code to auto fill the column B given the values of column A. A couple of years ago I solved this problem but can not remember how I did that, all I remember is that I created a function (that included the select case because of the number of entries) and accessed it from the worksheet. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Select Case Function
Public Function ConvertData(rng as Range)
Dim n as Long set rng1 = rng(1) select Case lcase(rng1.value) Case "apple" : n=1 Case "orange" : n=2 Case "kiwi" : n= 3 Case Else n = 0 End Select ConvertData = n End Function The code must be placed in a general module (insert=Module in the VBE), NOT in a sheet module or the thisworkbook module. -- Regards, Tom Ogilvy "Catalin" wrote: What I managed to do is to get the value from the worksheet into the VBA function, processed the information but I still have difficulties in sending it back to the worksheet in a corresponding cell. Your help is much appreciated. "Martin" wrote: Couldn't you just use VLOOKUP? "Catalin" wrote: COLUMN A COLUMN B Apple 1 Orange 2 Kiwi 3 Apple 1 etc. What should be the VBA code to auto fill the column B given the values of column A. A couple of years ago I solved this problem but can not remember how I did that, all I remember is that I created a function (that included the select case because of the number of entries) and accessed it from the worksheet. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Select Case Function
Tom many thanks for your answer.
But I still can not get this code to do the job I'm expecting. in the worksheet I wrote "=convertdata(E2)", is this correct? In the mentioned cell as an answer I get always 0. "Tom Ogilvy" wrote: Public Function ConvertData(rng as Range) Dim n as Long set rng1 = rng(1) select Case lcase(rng1.value) Case "apple" : n=1 Case "orange" : n=2 Case "kiwi" : n= 3 Case Else n = 0 End Select ConvertData = n End Function The code must be placed in a general module (insert=Module in the VBE), NOT in a sheet module or the thisworkbook module. -- Regards, Tom Ogilvy "Catalin" wrote: What I managed to do is to get the value from the worksheet into the VBA function, processed the information but I still have difficulties in sending it back to the worksheet in a corresponding cell. Your help is much appreciated. "Martin" wrote: Couldn't you just use VLOOKUP? "Catalin" wrote: COLUMN A COLUMN B Apple 1 Orange 2 Kiwi 3 Apple 1 etc. What should be the VBA code to auto fill the column B given the values of column A. A couple of years ago I solved this problem but can not remember how I did that, all I remember is that I created a function (that included the select case because of the number of entries) and accessed it from the worksheet. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Select Case Function
Many Thanks to All. I just found the asnwer.
"Catalin" wrote: COLUMN A COLUMN B Apple 1 Orange 2 Kiwi 3 Apple 1 etc. What should be the VBA code to auto fill the column B given the values of column A. A couple of years ago I solved this problem but can not remember how I did that, all I remember is that I created a function (that included the select case because of the number of entries) and accessed it from the worksheet. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif function: how to distinguish case/make case sensitive | Excel Worksheet Functions | |||
Select Case | Excel Discussion (Misc queries) | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Excel Custom Function with Select Case | Excel Programming |