I have a formula that doesn't quite seem to work. First I tried to make a formula to match the text in row 4 and then input the text from another box in a row below that. Does it matter if it is merged?
here is the code so far:
Please Login or Register to view this content.
Last edited by Templemind; 09-13-2014 at 04:12 AM.
Hi, Templemind,
avoid merged cells not if possible but at all!
Why are both If-Statements referring to Column A insteasd of the column you picked up in res? Why write to Column C?
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
I will take out merged cells. However, doing that did not change anything.
Woops I didn't see that. To tell you the truth I tried to copy the formula from somewhere else and change it to look in row 4 for the Roomnum.Value, but it does not do that. Perhaps I am confused to what the numbers are for.
I can get the text to enter in the correct column now, but I cannot get the match to work. Is there something wrong with it?
Here is the code now.
Please Login or Register to view this content.
Hi, Templemind,
Res ia a Variable while number 5 is a constant, itīs simple as that. I would suggest not to work with a Textbox to let the user type items (I prefer a Listbox or Combobox where the items are listed or may be added if needed).
I donīt see your workbook so it may be a typo or the searched value may just be part of the string in the cell or there may be formulas...Please Login or Register to view this content.
Ciao,
Holger
The reason why I have it as text and not as a listbox is because there are 39 entries and I think that is a bit long.
The match does not seem to work still. I have attached the workbook so you can see what I am working with. It is not too difficult, but I am just not getting it. I want it to find a match in row 4 from A to CA with some of the rows being blank.
Hi, Templemind,
you are looking for text in a row where just numbers are listed.
You could use your variable to make sure that the entries start from a certsain row on, and maybe have to adjust the column to which to write (this is the first of the two merged cells).Please Login or Register to view this content.
Ciao,
Holger
Thank you that works. I have just one more question if you can answer it. That code enters a number in a cell. That cell is part of a formula in another cell, but what happens is that formula does not recognize the number that is placed into the cell by the userform. Is there a way to fix this or am I out of luck? Do I have to make a macro for it?
It seems to work when I make a change to the cell, maybe further tinkering can fix it....?
Last edited by Templemind; 09-13-2014 at 03:41 AM.
Sorry for bugging you, but I just want the text to replace that cell only; I don't want it to go to the next row below it. So if you enter a number again it just replaces that one.
*Fixed thank you. I just looked at the formula and fixed the offset
Last edited by Templemind; 09-13-2014 at 03:36 AM.
Hi, Templemind,
thatīs should not be part of the code (if the cells are formatted as General as VBA would recognize numbers and enter them accordingly) but of the formula you used in the cell. You might try convertiung the Textbox value by using CDbl to convert the value to a numeric value. I looked at your formula in Row 1 and it just returned NA for me on a german system, you could enter the same information via VBA.
Ciao,
HOlger
If I changed it to a numerical value, then the formula in row one would work? Where would I put that into vba? Would I put that under workbook?
I'm looking it up on how to do so right now.
Last edited by Templemind; 09-13-2014 at 04:09 AM.
Hi, Templemind,
Iīm suggesting you should use groupings for c and nc as well as for the paymment (I didnīt look at that too closely).
Please find the workbook attached and have a look at it. I took out some of your formulas in Sheet2, and made the sheet hidden via the VBE.
Code for AddForm:
I didnīt place the text in Sheet1, maybe thatīs a task you may work on a bit, hereīs my code for UserFormPlease Login or Register to view this content.
Ciao,Please Login or Register to view this content.
Holger
That works great, thank you for helping me out with that. This will work good for my organization and make life a little easier for them. I can make the changes on Sheet1 accordingly.
There is only one thing that needs to be added though, and I cannot seem to figure it out. The code for UserForm works well for nc and c but it doesn't add in the other ones. I know I didn't inform you to put those in, I thought it would be self-explanatory for me, but isn't. As you have noticed there are 3 other radio buttons, and while they do not do anything for the calculations, I would like them added beside the date for tracking purposes. They are "p", "h", and "d". Would I need to add in more arguments? More variables? It seems to me it is just looking at true or false right now, which is
only a dual argument and cannot handle more. I am not sure how to change that in the code without messing it up.
Hi, Templemind,
you should have a good look at the way you name your variables and group or work with them.
Due to the fact that no grouping for the OptionButtons is availabe (I would suggest optMeat or optNoMeat and txtRoomNr or txtDate as names here) you will only have one choice out of the 5:
Ciao,Please Login or Register to view this content.
Holger
That totally makes sense. What I really need to do is read up on this more. I understand what variables are, I just kind of fumble around and get things right, which is not really learning.
Is it possible to have the date and the Drb, Prb, and Hrb in two different columns? So to put the date on one column and then the Drb, Prb, or Hrb in the other right beside it? Like you did with the nc and c?
It works wonderful and I thank you for doing this. I hope this is a cakewalk for you so you do not have to take much time out of your day.
Last edited by Templemind; 09-14-2014 at 04:31 AM.
Hi, Templemind,
You would need to add another column to all rooms in order to do so.Is it possible to have the date and the Drb, Prb, and Hrb in two different columns? So to put the date on one column and then the Drb, Prb, or Hrb in the other right beside it?
Ciao,
Holger
So the code can't put d where c would be if Drb was chosen?
Hi, Templemind,
and how will you distinguish the difference between Meat and NoMeat as well as the payment in one cell? Sorry Iīm not able to follow you on that as I would always have a different cell to store that information but I can only look at that problem from a distance without knowing the exact requirements.
Ciao,
Holger
As long as the formula for the credits left only calculates nc (No meat) and c (Meat) as subtracting, then it doesn't matter. They purchase credits and add them using the Addform. That goes into the cell and the Credits Left gets update. The Credits Lefts gets minused only if Meat (c) or NoMeat (nc) is selected with the radio buttons on the UserForm. If they select donate, then just a d shows up where a c or nc does, but it has no effect. Later I want to be able to tell how many times a person donated, used a Meat credit, etc. just by using the CounIFS function or whatever by looking on one row only.
Did I explain it well enough? There is no real payment as this form just keeps track of the amount of credits one has. Meat credits are worth one whole credit and NoMeat is worth half a credit. The rest of them are other ways they can get a meal, but do not change the amount of credits they have.
So basically I want it to look like this in the end:
meal.jpg
Hi, Templemind,
maybe
Ciao,Please Login or Register to view this content.
Holger
It works wonderful. You are such a great man. Keep up the good work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks