I have a column ful with numbers and would like to add an h (hour) after every number. I found that it is possible for valuta (dollar sign). Is there a way for random symbols that are not in the valuta list?
View 3 Repliesi have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345.
what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed
i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet
The cell content is to be 0-7 characters in length.
Alpha characters only, i.e., no numeric.
First character must be uppercase.
Remaining characters may be lowercase or blank.
Is that possible to do in MS Excel 2007?
What I have is an output of about 30 worksheets of individual stock data in a workbook, all with the same column headings, laid out like the following (so that the Column Headings are stock data as recorded on the Date in Column A):
<Stock Name>
<Date> <Price> <Price to Book> <etc.>
with the stock titles also as the names of the worksheet.
What I want to do is create a macro that outputs to a single worksheet where I can input the stock name and a certain date for the stock, and then have it insert the appropriate row from the stock's worksheet underneath.
So, if I say I want the data for Stock A on March 24, 2007, I can input those two and have it output the appropriate rows from the worksheet.
Or, more generally, I can create a list of stock names and dates, and have the macro output that data underneath.
I have a spinbutton on a userform. Here is the section of code for SpinUp:
[Code]....
Normally, I wouldn't hard code an address in like this, but I was given a last minute project to present on Monday, so it's down,dirty, and quick. Anyways, the adjacent cell "O7" has a formula referencing another page. When I spin the spinner it activates the correct cell (and continues to do so as long as I press the button), but here's the wierdness: cell "07" formula disappears, leaving the existing value in it. It's like it copy pastespecial values.
I've stepped through the code and it's not referencing any other procedure. It happens on this line:
[Code] ....
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
Need a formula if there is one to extract from a cell only that data which is to the Right of the symbol > and to the left of the symbol (
View 3 Replies View RelatedI need to take cells such as the following from A1 down:
Baker, Helen
Brown, Joe
Smith, John
Roberts
Thompson, Karen
White, Daniel
Wright, Lisa
I need to move this to the next column but where there is a cell such as the 4th without a comma it needs to add ", N/A".
Basically it should make "Roberts" from A4 become "Roberts, N/A". I think it needs something to search for a comma and if it's not present to add that to the end.
Is there a way to define the end of a cell in a find/replace? I.e. I want to find characters like this:
;;[space character][end of cell]
and replace with
[end of cell]
(that is just delete the ;;[space character] bit when it is followed by end of cell)
I am trying to put a (C) within a cell but it keeps converting to the copyright symbol. How do I amend this so I can just have a C in brackets?
View 2 Replies View RelatedI simply cannot insert a one eighth symbol in my spreadsheet.. I used to be able to see this in the Insert Symbols grib but now all I get are lots of question market.. if I use
selection.value = ChrW(8569)
the symbol does appear until I select the cell and press return, after which point it turns into a question mark.. I'm sure its something to do with my settings but I can't figure out what.. this is really important for me.
having a problem with a vlookup when the cell value contains ~
ie. John~Smith
it returns an #NA even when my lookup table has John~Smith in it.
I would like to embedd a symbol into a cell. I dont want it to be done by using Fonts. By symbol I mean Harvey Balls (Plan, Do, Check, Act) symbol embedded the same as the "and" (&) symbol. I have seen it done once on a spreadsheet but did not get a copy and I would like to recreate it as a way of visually showing the status of an action item.
View 14 Replies View RelatedI am trying to add a * at the end of a value in a cell, used to signify a note at the bottom of the page. However, as the cell contains a formula, excel thinks I am adding a multiplication sign instead.
Is there anyway to place a * at the end of the value, to show up in the cell as text? I would prefer not to have to paste the value of the cell and then add the *...
Cells in Columns A & B (57 rows deep) have a real time data feed and can have values of blank, "H", or "R". The H and R can remain in the cell for any length of time. I would like to have an audible ding go off whenever a new H or R appears in columns A or B. Is there a cell formula that can do this? I think a macro solution would not work, because the workbook has a macro that activates every 10 seconds, so I assume a macro solution would interfere if both macros happen to activate at the same time.
View 9 Replies View RelatedI need a code for filtering the cell's content.
What i want to filter and delete is all/any text found following after the symbol ">" .
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I'm trying to make a formula but are failing. This is the data:
Cells:
a1= ant-gel
a2= ant - gel
a3 = size 1 - 6
a4 = size 1-6
a5 = book - 5
a6 = book-5
a7 = good book
I want a formula that displays "True" if cell contains a "-" symbol AND the dash symbol is surround on both sides by a number (a space can go between dash and number)
I want the results to go in column B
For example, if a1 contains a dash and dashes are surrounded on both sides by a number, then "TRUE", otherwise "FALSE.
therefore:
b1 = FALSE
b2 = FALSE
b3 = TRUE
b4 = TRUE
b5 = FALSE
b6 = FALSE
b7 = FALSE
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
I have a very large workbook, too big to cut down and upload . In it I have some code which selects a graph and sets the data on the graph to an arbitary number, Unfortunately, I get a "Method 'Xvalues' of object 'Series' failed" error when I run it (although not every time). The relevant part of the code is below. Prior to the code below, different sheets are selected and variables moved around, then this part of the code hides the other sheets leaving the calculation page visible.
The graph is on a log-log scale, but I don't think that is particularly important. The data series are named as various things (e.g. baseline, reference, measurement) though but there are only ever two on the graph (I just rename them later on and change the data).
VB:
'open calculation page and hide survey results and archive pages
With Worksheets("Calculation Page")
.Visible = True: .Select
[Code]....
I need to modify the following formula,
=IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,
IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)
[Code].....
Column (B) contains two different units, either (ug L-1) or (mg L-1). I have this formula, =IF(B2="(ug L-1)",A2/1000,A2), which copies the value in column A to column C, so for cells that are in (mg L-1) units, it's a direct copy, but for cells that are in (ug L-1) units, it divides the value in A by 1000. However, I can't remember what the formula should be if the cell contains a "<" symbol AND is also in (ug L-1) units.
e.g.
< 0.004(ug L-1)
How can I change that to <0.000004?
I have come across another stumbling block with VBA (new on VBA and slowly taking baby steps). I have a macro that reads and deletes all duplicates on the files i work on. However I need this to read and do the job on 2 sheets at the sametime (sheet 2 and sheet 3). Below is the macro which does work fine on a different workbook. How would I integrate this into my new sheet to do 2 jobs at once?
VB:
Sub delduprows()
Dim m As Integer, j As Integer
Dim n As Long, i As Long
[Code]....
In cell AD17, I have a formula that returns the number of days or draws(Skips), since the number in cell AD2 has been drawn. The following cells AD18.......... returns the next skip and continues untill all the hits( AD3), are accounted for. My question, Can the formulas be altered to read a "
View 9 Replies View Relatedhow does one alter a variable without referring to it directly? Eg. One has a combo box (CboPNum) with values from 1 to 20 and textboxes (TxtN1 to TxtN20). Upon selecting a value from the CboPNum dropdown, the background of the corresponding TextBox should be changed. I’ve tried to create something that joins the string “TxtN” and the CboPNum value:
ThisTextBox = “TxtN” & CboPNum.Value
ThisTextBox.Background = Black
But this quite correctly gives the error “Object required”… What do you guys recommend?
I am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
View 9 Replies View RelatedI have two tables of information. The first is a matrix with some distances. The second is a table of distribution. Ive been trying this for a long time now with limited success using a truth table but i've realised the only way to do this is in code. Ive got limited experience with this so please point me in the right direction.
Ive written some steps explaining what each table does here -
Step 1Check for lowest value B4:F4 in Table 1 (in example is 10)
Step 2Check corresponding column destination available capacity in table 2 (Example 500)
Step 3Distribute as much as possible from source in table 2 (500)
Step 4Reduce value in capacity line by value taken from source
Step 5If some source remains move back to table 1 and find next nearest column Dest
Step 6Repeat step 2 until all source is gone in row
Step 7 Check for lowest value B5:F5 in Table 1
if run out of capacity at all sites stop code
etc
until Table 1 column B is empty
Ive posted a spreadsheet with some before and after tables in it aswell. Its very small and formatted o its easy to see whats happening.