• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

replace value via VBA

Afarag

Member
Dears please i have a request need some support from you,
in recap, i have a workbook have a "Data" for employees daily for a year, these "data" is related to "Deduction points" and "Rewarded Points".
the 1st worksheet is an interface for userform access, we will use the first that called "Agent login", this form have multiple fields need some inserted inputs and 2 button, called "Deductions" and "Rewards"; as per the filled criteria, will move to a popup sheet called "output" contain the desired data. the other sheets are very hidden
for my request we will use these criteria to have a clear inputs to use
1: at "interface" sheet, hit the icon that called "Agent login" to popup window with 3 field and 2 button
2: at 1st filed insert: sara.shaheen
at 2nd insert: 84166
will not choose a month yet
3: then hit "Rewards" button
now we have the sara's rewards points in a popup sheet with ribbon disabled and a user form with some inputs and some controls
0

these form export the both data "Rewards" as per 1st userform inserted criteria & "Deduction" as per the used "Username" - Sara.shaheen- and the built-in control for selecting month to retrieve "Deduction" table.
4: there is "Total rewards": count of Sara's total reward points
5: "redemption validation": is each employee can reward his deduction points by own rewards, but there is a condition: every 3 reward points remove only ONE deduction point, this rectangle show how valid points the employee have to use for redemption.

my question is: from this form i have the rewards and deduction points, can the employee redeem his points by hitting in "Redeem" button.
with some rules:
>> Every 3 reward points remove one deduction
>> the rest after redemption still
>> the reward points that redeemed will removed from the source sheet "Rewards", Column "E": called "Total"
>> the deduction points that rewarded will remove from the source sheet "Deduction", Column "Y": called "Total Deduction"
>> at "Deduction" sheet column "AD": "Comments#" beside the rewarded cell a comment will be inserted with eg:"3 point are rewarded" as a reference then
>> a MsgBox before a redemption process with Yes/No option, and also if the process will be once or point by point, it's means: the all reward points will be redeemed at once with one click or 3 points then 3 points etc...
>> with hitting the "Redeem" button to execute this operation need an email will be send from Outlook to:Ahmed.mabdelkader@tedata.net & CC:WFM-follow@tedata.net & Subject: rewarded points & Body: "kindly be informed that the -used user- made a redemption by "#" of his reward points"

note: some modification are made for the above code related in sorting columns, you can use the attached full workbook
the sheet is uploaded to Dropbox as its size overpass 10 m
https://www.dropbox.com/s/he93wh4nfh...ed%29.rar?dl=0


Gratefully,
 
Hi,

After reading your post i feel that i just crossed some milestone. :(
How , you people are writing such long stupid post & large attachment. :mad:
Are you thinking that we are getting oil well in UAE by helping people. :rolleyes:

Always explain the query in a nice gesture with a sample of attachment having about <100 rows of data.:awesome:

I think you are looking below things.
1. Check & redeem the reward as per set of rules.
2. Update the same to the sheet with specific remarks.
3. Inbox the confirmation for the same.


Looking for a sound form you. o_O
 
@Deepak ,

more than one month ago for posting the Question, i didn't expect ever the above answer, any way i get my issue solved, i can upload the solved sheet perhaps some one need to benefit or to share the knowledge between us.
what about of the post length is for clarification, because i know is some calculation is based on anthers, may providing incomplete inputs makes who will follow with me repeat his code building from beginning as per the lost inputs i don't clarified before, what about of attachment - in this case- you will need to test your code for the entier data to make sure that the code do the job as well, if i post a little sample who will follow with the post will ask to upload the whole workbook for a complete testing.
i tested the attachement for more than one time and reviewed the thread also before posting to make sure that every one will understand the post without any fuzzy case or bored Repetitions.
i'm not Anaclitic, and not ask to create or solve all the points in the post, you can direct me to a slimier worksheet and i'll adapt, or a link can help me if the following up will be long while i saw mr. Luke m before replied to a post for more than 390 reply alone
http://chandoo.org/forum/threads/compile-data-and-produce-a-dashboard.15000/

and shared his knowledge with the others.

thanks a lot,
 
@Deepak @Afarag

How , you people are writing such long stupid post & large attachment.

A bit sharp with Afarag, yes it is a longish post but he was just trying to explain his problem the best way he could and trying to give as much information as possible so that those who may try to help will not have to ask for more information. We are all born stupid...but we learn by living.


Are you thinking that we are getting oil well in UAE by helping people.


No...we do it just to help the best we can...we do not have to respond if we are unable or do not wish to..


.
 
@Deepak ,

more than one month ago for posting the Question, i didn't expect ever the above answer, any way i get my issue solved, i can upload the solved sheet perhaps some one need to benefit or to share the knowledge between us.
.........

Why u are waiting for .

if the following up will be long while i saw mr. Luke m before replied to a post for more than 390 reply alone
http://chandoo.org/forum/threads/compile-data-and-produce-a-dashboard.15000/

and shared his knowledge with the others.

We all are helping others & you might remember that i have did a lot's of thing in past for the same workbook.

Where the size matters....
Just save the your file in .xlsb & it will reduced to 9MB.

With your file..
Deduction sheet houses the around 10MB.

Total New Size is 5.37 MB of without any data loss in entire WB.
Zipping it will reduced the size to <5MB.
File link https://www.dropbox.com/s/o3x0yque54q74jw/Deduction (Updated)_2.xlsb?dl=0
 
Why u are waiting for .

i'm not Obtrusive, if someone finds him self free, could offers the help,
what about of sizing you are right about this issue,
i ask from you to not indulged into this sheet as i get it solved, thanks any way,
 
Back
Top