Convert numbers using specific key with VBA

[missy22]

Dear all,
I have a VBA formula (shown below) which calculates numbers which are multiple of 10 strings using a specific key (can be found in the attachment of the file in sheet2).

Price test.xlsm

I would like to embed a new key into the formula below, which only calculates numbers which are NOT multiple of 10s (i.e 45s, 78 etc).

"IF a number is multiple of 10s THEN use the code function below, Else IF, the number is not multiple of 10 (79) THEN use the new key to convert the number."

This post references my other post on other forum and would appreciate any help as I am finding it difficult to embed the two keys together.

http://www.mrexcel.com/forum/excel-q...ml#post3247187

Please Login or Register  to view this content.

Any help would be much appreciated.
Thank you for your time and response.

[Andrew-R]

Just to help me understand...

The first code in your sheet is M90s. Because 90 is a multiple of 10 and your key states that Mx have a value of x5 you want to multiply the 90 by 5 to get an answer of 450, is that correct?

[missy22]

Dear Andrew-R,

I am extremely sorry for the confusion, as sheet 2 formatting did not read the correct variables of the key.

The numbers in column E uses the key to form addition arithmetic.

Example:

Multiple of 10s keys

VLx +1
Lx +2
LMx +3.5
Mx +5
MHx +7
Hx +8
VHx +9
xA X

M60s --> 65 because ( 60 + 5) = 65.

______________________________________________

NOT multiple of 10 key:

VLx + 0.1
Lx + 0.2
LMx + 0.35
Mx + 0.5
MHx + 0.7
Hx + 0.8
VHx + 0.9

M55s --> 60

I hope this clarifies your confusion and sorry for the misunderstanding.

Also the code should still carry out its original functionality which is already codded in the original VBA function.

The file below, shows sheet 1 with the output and shows not multiple of 10 numbers in yellow highlight.
Price test.xlsm

Thank you so much for your time and help.

[Andrew-R]

I think this code does what you want, but there are a couple of entries in your sample sheet that don't appear to match any of the given keys. At the moment this function returns an error, but it should be fairly obvious how to expand the list of codes.

Please Login or Register  to view this content.

[missy22]

Dear Andrew.

Thank you so much fro your help and spending your time responding to my problem.

However, how can I change the above function into sub, so when i press the button it will automatically calculate the numbers.

Thank you for your time and and help. I really appreciate your time and help.

[jindon]

Not sure if I understand your problem correctly.

Here's my take.

[Andrew-R]

Probably easiest just to add a Sub that calls the function:

Please Login or Register  to view this content.

[missy22]

Dear Jindon,

Thats does extacly what I want thank you so much.

However, the code is password protected as I want to move the code into my master file with the rest of full data. How can i resolve this probelm. Thank you for your time and help.

I am very grateful to your help and time.

Please, if your could help me resolve the password issue, that would be a great help.

Thank you so much.

[jindon]

Working?

Here's the code.

Please Login or Register  to view this content.

[missy22]

Dear all,

Both of your code works excellent and I am very grateful to your time and response.

I have very large quantity of data, with many string variables. My original code tackles these variables and my original dilemma was, if it is possible to combine your code with my original code below:

Please Login or Register  to view this content.

To run this addition arithmetic function there are many clauses and I am finding it difficult to embed this in your codes. If you could show me a way, how can i do this, i would be very grateful.

I am not sure, if its even possible to emerge the above code with mine???

Thank you for your time and response Jindon & Andrew.

I very grateful and appreciate your time and help.

---------- Post added at 08:18 AM ---------- Previous post was at 08:15 AM ----------

Working?

Here's the code.

Please Login or Register  to view this content.

Dear Jindon,

Your code works great and I very much appreciate your time and help. Apology to sound ungratful, but is it possible to merge my code with yours, as mine have many conditions which is essential to the data I am using, if possible to merge.

If not, thank you for trying.

Best wishes

[missy22]

Probably easiest just to add a Sub that calls the function:

Please Login or Register  to view this content.

Dear Andrew,

Thank you for helping me out with my code and problem, Apology to sound ungrateful, but would it possible to merge mine code with yours as I my original data has many conditions and parameters, if possible. I really finding it difficult to merge the two.

If not possible, thank you for trying.

Best wishes

[jindon]

I think it is much better to post a sample workbook with the result that you want.

[missy22]

Thank you Jindon for your response.

Its merely impossible to create a sample worksheet with all the possible variables because they keep arising and the original code i had above, allowed me to manipulate the code to various variables in column E.

for example,

any variables with VHteens or teens string in column E, would execute the following code below:

Please Login or Register  to view this content.

there are many differet kind of VH(x) numbers in column E and the following code below executes these types of Vs (VH30s) VL90s):

Please Login or Register  to view this content.

if column E, has string such as "34a" or 45h, then the following code is used to execute this problem:

Please Login or Register  to view this content.

If a column E has a number only, then extract that number into column F only.

Please Login or Register  to view this content.

Only run this code function if the row = CMBS, as the following code executes the following below:

Please Login or Register  to view this content.

there many different kind of L(x) as shown by the code below:

Please Login or Register  to view this content.

I would like the output to be:

If any numbers in column E is not a multiple of 10 use your code else if they are multiple of 10, use my code.

If this is possible to execute.

I can try to upload a file a with all the variables as possible, if you want a clear explanation and output.

Thank you for your help and time.

[missy22]

Anyone please could assist me with problem in post 13, i would really appreciate any help.

Thank you for your time and help

[Andrew-R]

Missy,

Sorry, but it's really not clear to me what you're trying to do - your initial problem seemed straightforward enough, but you're trying to integrate a discrete function into a big block of code that is doing other things that you haven't explained.

If it's just that you have codes in X99X, or XX99, or 99X, or various other formats (where X's represent letters and 9's are numbers) then the solutions that have already been posted can be extended to any reasonable number of such codes. If there's more to the process than that then you're really going to have to explain what's happening. It's not simply a case of pasting the code that Jindon or I have provided into your existing macro.

Sorry if this isn't a very helpful answer, I'm doing the best I can with the information I've got.

[yogananda.muthaiah]

kindly share the sample workbook to work around for your expectation