I have a paragraph that i am trying to change a number in the paragraph. example
John has 3 kids he sells one kid he has 2 kids left. Later John's wife figures out and kills John 4 times.
I am trying to replace the "4" to an imputed on an user fourm imput box can i do this?
I have nearly finished my project, but need to make the last real bit of code, then just some general tidying up.
What I want to do
- I currently have two tabs in a userform - one called 'main' and one called 'extra'.
- I want Main to always be shown to the user.
- I want Extra to be hidden (is it possible to make the tabs invisible?) and for this form to be shown only under a specific condition
I have a question on my userform (using Option Buttons):
"Did the customer ask about an extra product today?"
If the answer to this question is no - When the user hits the command button (after filling out the rest of the form), do nothing special - just return all the values to the worksheet
If the answer is yes - When the user hits the command button, I want them to be taken to the 'extra' tab. They will fill out some additional check-boxes, then hit another command box, which will return the info from both the 'main' and 'extra' sheets into the same row of the worksheet.
In brief: I want my 'extra' tab to be hidden, only to appear when 'yes' is selected, and for the results all to go back to the worksheet.
I have been using an IF,ISNA,VLOOKUP formula as follows which I am sure you are all familiar with :
=IF(ISNA(VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0)),"",VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0))
This formula works correctly, displaying the lookup value for K7. My query is between the"" I can place text to display when K7 is blank and this works correctly too. However I would like to place a formula in here. The formula is VLOOKUP(I7,Orig!A7:B35,COLUMNS(B7:B35)+1,0 i.e. the lookup value is now I7 and not K7 when K7 is blank.
I have tried the following and variations based on what I know but they return errors.
=IF(ISNA(VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0)),(""& VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0),VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0))
Any better way of using I7 as the lookup value when K7 is blank.
Some handy code that I can put in a VBA module that will convert all text within a Spreadsheet to Proper or Sentence like this ---> Hello Everyone, Hope You Are All Happy.
View 7 Replies View Relatedi have list in A2:A4 (description) and B2:B4 is the Group of.
now i want B2:B4 fill using E2:G2 (group list which is Animal, transportation and fruit) if one of the key word in E3:G5 found in A2:A4
A2: people like to eat apple
A3: car is very expensive
A4 : dog is human best friend
E2: Animal, F2 : transportation, G2: Fruit
E3:E5 = dog, cat, horse
F3:F5 = train, ship, car
G3:G5 = apple, banana, watermelon
result i want is :
B2 :Fruit,
B3 : transportation
and B4 is animal
I was having trouble on getting a text string within a sentence..
Example:
In column A1:
1 - AMERICA 85 - 90 2 - CHINA
So I want to get only the 85 - 90 and it will shows on column A3..
Could you tell me how I can find a specific sentence within a cell that contains many sentences.
for example
I want to find, "I am new." within a text that contains, "Hello I am Bob. I am new. I live in england."
I am currently using =+FIND(AB$1,$V2) where AB1 contains the sentence I am looking for and V2 contains the cell full of sentences. However this returns #VALUE! when the sentence is not found. I want it to return null.
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
I'll try to keep this brief. Our part #'s look like this. XP014567. Where the first pair of characters stand for a value(say XP=Bolt), the second pair stand for a value(01= Grade 2), and so on. What I would like to be able to do is type in the part numer and have the description automatically populate. Is there an easy way to do this? I'm running Excel 2003.
View 4 Replies View RelatedI have a formula that combines the the text of various cells.
Is there a way to automatically make part of the cell Bold - so in this case I want the Cell B13 to appear in bold.
See formula below:
VBA code to use input box result in a Concatenate function.
Sub test()
Dim DistName
DistName = Application.InputBox("Enter District Name")
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2]C[1],"" Diabetes Dist"")"
End Sub
I want to replace R[-2]C[1] with the result from the input box.
I want to track winning football teams by highlighting the victor. So lets say I have a cell that contains
Raiders @ Niners
and another cell containing "home" or "away" indicating the winner.
Based on the home/away cell, I want to either highlight Niners/Raiders.
Perhaps a bit cryptic but here is where I'm looking for.
Cell A1 gets the value YES (or NO) assignment by the user.
Cell B1 has some calculated value let's say 4 based on the formula If(A1="YES";2+2)
Now If the user assigns "NO" to A1 the value of Cell B1 becomes FALSE (return value of second part of the If statement)
I do not want the value of B1 changed if A1=NO and to stay the same (4)
I have one excel file and i want to see the VBA code without open excel file. because when i open that excel file automatic delete userform and save then open that file. i have a second copy of that file in that file there is a one other option to see and learn from it. 1.my computer security is High but when i open the file one userform is come and ask me about enable or desable the macro when i check the box that file is open and run properly.
and after that never ask for Enable or desable the macro, every time that file is open in high security without ask..security. for your information many vba code in that file. i have only one question how see the code without open that file ?
I want to highlight part of a row ie I25-P25 if a specific cell in that row is 0 (O25) Cant figure out a formula to do this.
View 1 Replies View RelatedI have code that retrieves the body of an email. I need to parse out certain parts of the text. For example, the text will look like the following;
LastName: John Doe
Email: johndoe@aol.com
Cell#: 555-555-5555
FileRequested: xxxxx.xlsx
I have the code to find where the specific item, ie LastName, starts in the whole text. I need to retrieve everything to the right of the : before the CRLF. That's what Im having trouble with.
I'm looking for an IF function formulae.Currently, the IF function of my cell is =IF($A8=B$1,1,0), value if true=1 , if false= 0.
But how do I make the cell A8 ( MRT , Taxi ) show a true value of 1 ?
Becos MRT , Taxi is also under MRT, but I don't know how to make it a true value of 1.
If I have a date showing 1-Feb, how can I make it show the day of the week?
View 1 Replies View RelatedIn a worksheet if, in any cell a number is entered to show a UserForm.
Example: If anything greater than 11 is entered show UserForm5
If anything less than 11 is entered show UserForm6
Also, If you can help with this below I dont know how to implement it.
If in any row if two cells = "Loss" show UserForm7 The word LOSS This is input thru a work sheet calculation.
If in any row if the cells contain the most WINS in that row. show UserForm8
My table is the equation of time (EoT) (sundialist's jargon). It shows each day of the year if the sun is running fast or slow according to the clock. I need to make (on another page of that file) where I input the month and date. It will then till me the time it is fast or slow
View 5 Replies View RelatedI want a macro. when i run this macro pop up a input box and ask for copy of master copy and asked for rename the file...
E.g. I have a master file in "z:42766decmasterfile.xlsm
When I click macro then macro do a copy of masterfile and rename it according to choice...
I have a project coming up where I will receive tables in a certain format. I want to be able to create a macro that well create a chart based on a single inputted cell (start of data). The chart will have 4 series and five values per series. See the link below for sample data! The first data point is not in the same place in the sheet, but the table will have the rest of the data relative to that!
[URL] .......
I am trying to do, is make a drop down list show the picture above it. I have tried to change everything, and I am sure I am just a hair away from getting it right. I got the spreadsheet to where it will pull up the "Picture #" in the appropriate box, and even had it delete/hide all my pictures (I say delete/hide because I couldn't get them back after I removed the coding I was working on).
Another nice function I would like would be a button, around cell B74, to erase all the picks and reset the sheet for the next season... it would have to have a prompt to verify the user was absolutely sure they wanted to do that though! But I don't know where to begin on that button issue. Can someone point me in the right direction?
The spreadsheet is too big to attch, so it's hosted at the link below. The sheets involved are NFL Survivor & PicTable. On sheet PicTable, the Names & Pic #'s have already been named PicTable for ease of coding.
In the attached file (xlsx) under 'Database' Tab poeple have indicated their preferences (multiple choices) for different food items "specialties". The specialties are grouped under broader buckets called "groups". The specialties are bucketed into groups in a way that people end up more than once in each specialties and groups due to their muliple selections. When we create a pivot by specialties (Pivot Specialties tab), each person appears only once for each specialty...it's great. But, when we create a "group" pivot (each group has multiple specialties), now, people appear more than once for each group. Is there a way, each person can show up only once under each group so the group count does not appear to be misleading?
View 3 Replies View RelatedI have a problem with 2 "IF formulas"
A B C
23:30 00:30 1:00 - =IF(B2>B2,A2-A2,B2+1-A2)
14:00 13:00 On Time -=IF(B3<A3,"On Time","Delayed")
My question is: - how to make the first line to show me Delayed. It is a problem with the time before and after midnight.
Essentially what I want can be shown in outline form:
I. Stats (workbook)
A. 2009 (worksheet)
1. Jan (worksheet that appears only when 2009 worksheet is clicked)
2. Feb (worksheet that appears only when 2009 worksheet is clicked)
B. 2010 (worksheet)
1. Jan (worksheet that appears only when 2010 worksheet is clicked)
2. Feb (worksheet that appears only when 2010 worksheet is clicked)
I was hoping there was a way to have one workbook that displays only the years (2009, 2010) as worksheet tabs but once clicked, would reveal 12 nested worksheets (one for each month). Once a different year worksheet was clicked, the month worksheets currently displayed would hide again.
I want to make a Word Clock. Instead of showing the time as "12:30" I want it to show the words "It is half past twelve"
To Do this I've created a Square of Letters on an excel sheet (one letter per cell!) with black letters and black back grounds, when it is 12.30 I want the relevant letters to (cells) to change the text coulor to white so it shows up.
The cell I'm using are from D8 to P19 and the time is shown in A1
So lets say at 12.30 Cells D9, F12, H14 and M16 need to change from Black Text Colour to White Coulour,
I need a code or some VB that says "If A1 = 12.30 the Cells D9,F12,H14,M16 = White Text Colour, if not Black Text Colour".
I need to create a form to update employee details.
The one I created has input boxes for all the details in the form. eg. Name, ID, DOJ, Address, etc. So once you enter all the details and press enter, the details gets updated in the database.
Now, what I want is, my form should first only show input box for EmpID. Once I give the input for EmpID and press enter, the form details should now change to Name. After updating the Name, it should show input box for DOJ.
When you create a userform in the vba editor, then go to another module, then return to the userform, you see the userform object by default and in order to show the code you have to right-click on the userform and select "view code".
How can I make the "view code" view the default view for the form in the VBA editor? (i.e. so that when the form is selected, I am presented with the code, not the object).
how to make a date automatically show up in the current year.
For example, if A1 = 6/2/2005, and I want B1 to equal that same month and day, but 2011... How can I do that?
I know =6/2/2005 + 365*6 would work, but the date in A1 will vary... so it has to be a way of showing ANY date with ANY year with the same month/day but current year.