I am using an ODBC database connection to input data into an excel file. When I input the data excel stores the numeric data as text and puts a ' in front of the number. There is an exclamation mark beside the cell which allows me to convert the data back to numeric format. I need excel to do this conversion for me automatically when new data is added to the file.
I want to set up a table that automatically adds rows, ideally to the top... The way I was guessing to do it would be to alter rows in the table to have a variable... My variable is 'acctstotal' anyway I figured there would be a way to edit the rows to acctstotal+ 1 or somthing along those lines but I havent been able to figure it out
btw acctstotal is a variable that simply counts the number of nonblank lines that are in the first column of the table which tells me the total number of accounts
I have no experience programming although I'm vaguely familiar with Excel offering ways to automate many processes. My current situation involves me having to insert a new line whenever a there's a change of term in a row after a long sequence of repeated terms in a specific column. So, for instance, we have something like this:
I can arrange for a new row to be automatically added to a spreadsheet after I have sorted the currencies in order?
So basically I am trying to record a macro that sorts the column into currency order then after the currencies have been sorted I would like an extra row added so that I can enter a total field in between....
In cell b5 I have =today() which automatically updates the date on a daily basis. What I would like to happen is for each day there is a new line is automatically inserted with the date so:
Sat26/04/2008gone off sickFri25/04/2008Thu24/04/2008Wed23/04/2008Tue22/04/2008Mon21/04/2008Sun20/04/2008Sat19/04/2008Fri18/04/2008days holidayThu17/04/2008Wed16/04/2008Tue15/04/2008
I would also like any information on each of the line to move down when the new line is inserted. I am not really that good with macros or VBA but feel that is the only option to solving this problem. Also would everything still update even if the file wasn't opened for lets say the weekend. So it is constantly rolling.
I'm building a spreadsheet which includes a few columns which have activex check boxes which are linked to cells. I want to allow the users of the spreadsheet to insert new rows if necessary. Ideally, when these new rows are inserted, the check boxes would also automatically appear in the corresponding columns of the new row (and be linked to the relevant cells), just the same way that pull-down menus and formulas automatically copy into the new row. Is there a way to do this?
i want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet
i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.
Numbers with Dr as suffix denote positive numbers and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.
I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.
There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.
NAME, PHONE, STATE Bleh, 555-555-5555, =ChkState(B2)
I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field
Function ChkState(pVal As String) As Long
Dim AreaCode As String Dim StateAbrv As String
AreaCode = Left(pVal, 3)
If AreaCode = "201" Then StateAbrv = "Test201" ElseIf AreaCode = "203" Then StateAbrv = "Test203" ElseIf AreaCode = "555" Then StateAbrv = "Test555" Else StateAbrv = "0" End If MsgBox StateAbrv End Function
I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.
I have pulled a report from a website. The website only allows a certain number of characters. For instance it might go to john.smith@, dave.bird@, tom.jones@... this has been pulled into an excel sheet. I want to add the domain at the end of the email address so it would become
I have data (from a very large spreadsheet) that has data as shown below in various columns. The data should be as in col A but there is a lot of it in different formats as in B. Is there any way to change the data from B into a new column say E where I can get the data into a leading number of 3 digits, the text with 1 or 2 letters and the remainder into 4 digits with the final text subscript if it is there?
008D001308D13 008D000608D6 008D009208D92 008EJ00028 EJ 2 008EJ000308EJ3 008EJ0004A8EJ04A 008EJ0004B08-EJ-4B 095D006195D61
im on a spreadsheet that im plugging into falconview, a map viewing program-on the spreadsheet i have coordinates, but not directions (N,W,S,E)-how can i enter in say a "W" before each coord without manually plugging it in- i would greatly appreciate it-its for OTJ military work
If I have a cell value (formatted general) of say ... OneOne .... somewhere in row 2, can i insert a formula between the two One's that inserts the row number. The intention is to have the cell display .... One2One. Conceptually the cell would read One=row()One. If you know what I mean.
I am trying to write a macro that will allow me to copy the text of a cell, and then insert text around that previously copied string. I am having a problem adding the copied text into my formula. Here is my code below:
Sub Bid() ' Selection.Copy ActiveCell.Value = "inventory levels are " & ActiveCell.PasteSpecial(xlPasteValues) & " and oversold by" ActiveCell.Offset(1, 0).Select End Sub
What I need it for is an automated report that should be used for several different projects. Each projekt have events on different dates, and I only want the report to show a specific date if there is some text (i.e. event) on that date. As it is now, it's a looong report with several blanks with only a date showing. So it should insert a row (the tricky part) + the date + the text if condition is met, and do nothing if they are not. The script should add and remove rows and text depending on what project the information is taken from.
The data it should grab is in this form (many more data inputs though..): Date Date Date TEXT
I am working on an online archive database and I need to insert "<!--More-->" (without the quotes) after 80 words in a number of cells populated with 500+ word stories. I browsed here for a while to no avail.