Solved

Changing working code for excel vba

Posted on 2016-09-27
15
19 Views
Last Modified: 2016-10-03
Hello guys, i have tried fixing it my self with no luck, i would really appreciate if someone may help me fix this. Again thanks for your time and help.

Priority.png
Present code
Sub RectangleBeveled6_Click()
Select Case UCase(Cells(ActiveCell.Row, "H"))
    Case ""
        Cells(ActiveCell.Row, "H") = "1"
    Case "1"
        Cells(ActiveCell.Row, "H") = "2"
    Case "2"
        Cells(ActiveCell.Row, "H") = "L"
    Case "L"
        Cells(ActiveCell.Row, "H") = "Z"
    Case "Z"
        Cells(ActiveCell.Row, "H") = ""
End Select

End Sub


****Description. *****
On column "H" there is four option that will be shown here. if i click on the small button on the file.

I would like to replace these option as follow.

1 with First P
2 with Second P
3 with Last P
Z with Z Done
0
Comment
Question by:Omar Hernandez
  • 7
  • 7
15 Comments
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
Sub RectangleBeveled6_Click()
 Select Case UCase(Cells(ActiveCell.Row, "H"))
     Case ""
         Cells(ActiveCell.Row, "H") = "First P"
     Case "1"
         Cells(ActiveCell.Row, "H") = "Second P"
     Case "2"
         Cells(ActiveCell.Row, "H") = "Last P"
     Case "L"
         Cells(ActiveCell.Row, "H") = "Z Done"
     Case "Z"
         Cells(ActiveCell.Row, "H") = ""
 End Select

 End Sub
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hello there Xtermie,thanks for your response. I have tried this, with no luck...
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
In the code offered by xtermie, Tty to replace the following line...
Select Case UCase(Cells(ActiveCell.Row, "H"))

WITH THIS to see if that works as I guess there might be some spaces in the end.
Select Case UCase(LEFT(Cells(ActiveCell.Row, "H"),1))
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Thanks for your response Neeraj, i have tried your suggestion, no luck yet friend.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
Omar!
As per the code, the code will only work for the Active Cell's Row and it will replace the content of column H in that particular row only.
Try selecting any cell on Row32 and see if J32 gets changed as per the code.
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hey Neeraj i have tried to change even the row that is on the 32 number, no luck
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
Then why not upload a sample workbook to see why the code doesn't work for you?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Omar Hernandez
Comment Utility
Forgot about that part :/ , Here you go Neeraj
BK-TO-DO-LIST.xlsm
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
I misread the code.
The code works on column H not on column J.
If you select a cell in Row86 and while H86 is 1, the code will change it's content to "Second P" as per the code.
Isn't it what you trying to achieve?
BK-TO-DO-LIST.xlsm
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hey there Neeraj, no luck. If you click on the button shown on the pic on top, it works with no prob, but when it has to do with changing the one letter or number to a whole word it gives me an inconvenience. ***The actual code works with any row
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
Sorry not getting you here.
Won't it be better if you list your steps when the code doesn't work for you?
0
 

Accepted Solution

by:
Omar Hernandez earned 0 total points
Comment Utility
Hello there Neeraj, thanks again for your help, on my way creating a video i tried to add an additional letter to the code it worked, then i tried to keep all of them with ether one style capital letter or small letter and it worked. The code that was this.

Sub RectangleBeveled6_Click()
 Select Case UCase(Cells(ActiveCell.Row, "H"))
     Case ""
         Cells(ActiveCell.Row, "H") = "FIRST P"
     Case "FIRST P"
         Cells(ActiveCell.Row, "H") = "SECOND P"
     Case "SECOND P"
         Cells(ActiveCell.Row, "H") = "LAST P"
     Case "LAST P"
         Cells(ActiveCell.Row, "H") = "ZDONE"
     Case "ZDONE"
         Cells(ActiveCell.Row, "H") = ""
 End Select

 End Sub
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
So all resolved?
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
Omar!
I must say that you poorly described your requirement but anyways glad your issue has been resolved.
0
 

Author Closing Comment

by:Omar Hernandez
Comment Utility
I tried a different approach.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now