Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vba for replacement of cell vulue in fraction at suffix

0

         I wish to have a vba like : In an Excel sheet,

Step1. Click on a cell containing Alphanumeric  values like A1 or AS1/2 or BS1/3 or C1/4D3/4 etc.

( note that our concern is with the end part of  fractions i.e.,1,1/2, 1/3 and 3/4  in the examples as above). 

Step2. Now,the code  is to Horizontaly  lookup and COUNT for number of occurrences of 'z' till it encounters with  other  alphabet/character in the row.

Step3.

If the occurrence of 'z' is once, then the cell value A1 changes to A2; [i.e.,1 for z +1 for suffix numeric of A1]

If the occurrence of 'z' is twice, then the cell value A1 changes to A3; [i.e., 2 for z +1 for suffix numeric of A1]

If the occurrence of 'z' is thrice, then the cell value A1 changes to A4; [i.e.,3 for z +1 for suffix numeric of A1]

Similarly for AS1/2,

If the occurrence of 'z' is once, then the cell value AS1/2 changes to AS3/2;[ i.e.,1 for z +1/2 for suffix fraction of AS1/2]

If the occurrence of 'z' is twice, then the cell value AS1/2 changes to AS5/2; [ i.e., 2 for z +1/2 for suffix fraction of AS1/2]

If the occurrence of 'z' is thrice, then the cell value AS1/2 changes to AS7/2; [  i.e., 3 for z +1/2 for suffix fraction of AS1/2]

Similarly,

If the occurrence of 'z' is once, then the cell value BS1/3 changes to BS4/3;[ i.e.,1 for z +1/3 for suffix fraction of BS1/3]

If the occurrence of 'z' is twice, then the cell value BS1/3 changes to BS7/3; [ i.e., 2 for z +1/3 for suffix fraction of BS1/3]

If the occurrence of 'z' is thrice, then the cell value BS1/3 changes to BS10/3; [  i.e., 3 for z +1/3 for suffix fraction of BS1/3]

Similarly for C1/4D3/4  ,

If the occurrence of 'z' is once, then the cell value C1/4D3/4  changes to C1/4D7/4 ;[ i.e.,1+3/4]

If the occurrence of 'z' is twice, then the cell value C1/4D3/4  changes to C1/4D11/4 ; [ i.e., 2+3/4]

If the occurrence of 'z' is thrice, then the cell value C1/4D3/4  changes to C1/4D15/4; [  i.e., 3+3/4]

Please note that only the last fraction i.e., 3/4  will change.

And so on.

Please note that the replaced fractions will be in whole fractions.

An excel sheet is appended to express my Question and desired result.

Answer
Discuss

Discussion

Does this need to work only on the specific cells that were mentioned and in their specific location on the worksheet or does the code need to be more dynamic and work regardless of where the fractions are located in the worksheet. (This is probably more of a paid-consulting type of situation, but I can try to help as best I can.)
don (rep: 1989) Apr 3, '17 at 11:24 am
No specific location of cellis is needed.It should work regardless of where the fractions are located; only to work in a Row-wise manner; that is after completion/replacement of first occurence of fraction, it would work on Clicking for another occurence of fraction(s) in the same Row if any, and after replacement , it would go on clicking the next Row(s) etc.
Omilan Apr 3, '17 at 1:45 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login