I am trying to keep my checkbook in excel and want to make a spreadsheet that inserts a row every time I hit enter. That way my most reason transaction and balance is always at the top rather than the bottom of the spreadsheet... The inserted row should have the same formulas as the row below.
View 7 RepliesHow can a formula perform the equivalent of the keyboard alt enter in a cell?
ie Make =A1&A2 where A1 and A2 are cells containing text become
a1text
a2text
in the new cell.
How do I get a cell to automatically add the numbers as I enter them,for example. I want to keep track of my gas bills for income tax. So if I go in each day to add that days gas amount I want that cell to auto add each entry for me, rather that constantly add my bills and then re-enter every time I enter new amounts.
I would like to place a value of like say 100 dollars if the cell next to it is populated with any text for example: customer then 100.00 so if I type anything the cell, the next cell populates a specified value
View 5 Replies View RelatedI have recently written some programming in a spreadsheet (1) which, on opening, opens up another spreadheet(2), ammends this spreadsheet(2), and then closes it before the user can start using the original one(1). This is done to monitor the usage. The information added into the atomatically opened spreadsheet(2) includes date and time of opening and the users id as well as adding 1 to counter cells. Both .xls files are located on the same server and I dont want people to catch on to the monitoring and if they do I dont want them to then go into this file(2) and remove their information.
If I password protect this file(2) it then promts to enter a password when it runs at the startup of the initial spreadsheet(1) which is no good as it would defeat the purpose if the user knew the password. Is it possible to add a few lines into the code (see below) after it initiates the opening of the usage file?
Private Sub workbook_open()
Application. ScreenUpdating = False
Workbooks.Open Filename:= _
"S:Newman CommonCATEGORY MANAGEMENT - RangesEPoS Usage.xls"
Dim Counter As Integer
Counter = Cells(2, 7)
Counter = Counter + 1
Cells(2, 7) = Counter
Cells(Rows.Count, "BV").End(xlUp).Offset(1, 0) = Now
Cells(Rows.Count, "BW").End(xlUp).Offset(1, 0) = Application.UserName
ActiveWorkbook.Save
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub
I was wondering how I could automatically enter a value next in a sequence in a cell when an adjacent cell has data in it and continue doing so indefinitely (or until the 65536th row). I could do this with autofil, but The sequence starts in the middle of the worksheet (there is a title and document data in the top few rows). The layout is as follows:
Cell A17 has "Part 1" in it (and always will before the form gets filled out), and all cells in colum A beneath that will be blank. Column B is where the user enters a dimension. So, at the start, entering a value into cell B17 won't do anything. However, if they enter a value into cell B18, I would like cell A18 to automatically be filled with the next part number in the sequence (in this case, "Part 2" in cell A18). The user will never skip rows when they enter data so the sequence will always be +1 to the previous cell in the column.
I'm doing this so they can just print out the completed sheet and not have to edit out the empty "Part *" cells that have no corrosponding dimensions.
Is there a simple way to tailor autofill to do this or would I need a macro?
I have a command button that opens 5 windows when it is selected. Each window asks us to enter the username, password and logon to in order to sign on...thus, we have to do this a total of 5x manually.
If possible, I'd like for the aforementioned fields to be entered using VBA code, so when I click the command button, all 5 windows open with the login infomration already entered.
For examples, sake, lets say the usernmae is ABC1234, the password is Password and the Logon To field is Alliance. If this is possible, how would I code this?
Is there a way that Excel can automatically enter to next row once the typing has reach the end.
Example: I have 5 columns (A to E) and I am typing at column A. While typing, the text will go along to cell B, C, D and E. The problem is, if I don't manually go to next row and continue typing, the text will go to column F, G and so on. Is there a way where excel can automatically jump to next row if the text has reach column E?
Another problem is, say I have 3 rows full of text (column A to E). If I edit one of the row to exter some new texts, the whole sentence will go along to column F, G and so on. What I can do now is, re-edit all the rows to adjust them back.
I'm trying to write a macro that automatically italicize the a range of cells when you select a cell, and de-italicize it if you click it again. In this example, when select B12, the macro will automatically select B12:H12 and italize them. And if you select B12 again, it will automatically select B12:H12 and de-italize it.
This is what I have so far:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyAddress = ActiveCell.Row
If MyAddress > 11 And MyAddress < 159 Then
If Not Intersect(Columns(2), Target) Is Nothing Then
Intersect(Columns(2), Target).Resize(, 7).Select
End If
Select Case Target.Font.Italic
Case "True"
Target.Font.Italic = False
Case "False"
Target.Font.Italic = True
End Select
End If
End Sub
The code above does not work correctly.
How to set a column up to show it as the time of day but unless I enter PM after I put the time in the column it always comes up as AM.
Is there some way to just enter 9:09 in column d and or f and have it show up as pm? When I enter 8:10 in column b it automatically adds the AM to it but if I do the same in the column for the night and I do not type in pm it will automatically enter it as am.
I have attached the workbook and did delete the macro - which was just to clear all of my entries but when I click to open it again I am still getting the message about the macro.
In the workbook when I enter the time in column B I can just type in 7:14 and it will automatically add the AM to it. However, when I get to column D or F if I just type in 9:09 or whatever time it is in the evening it will automatically add AM to it unless I type 9:09 pm.
Is there some way to set it up so that I can just enter the time and it will automatically enter pm for everything in that column?
2013 sugar logs.xls‎
In column B I will be entering a number and I would like it to automatically enter gallons in column c when i do so. So if i enter 356 in b2 i want it to say gallons in c2.
View 1 Replies View RelatedSee attached sample sheet for more detail. I used code for show date and user id in cell “H” and “I”, but my problem is when ever I write some thing between cells “A” to “G” and enter the pointer automatically jumped to cell “H”, I want when I enter the pointer should be stay next cell.
View 2 Replies View RelatedI am making a template for my company to automatically calculate the amount of sheet metal needed for a specific job. The spreadsheet could get very long depending on how much duct is needed. Is there any way I can automatically insert rows to the end of the sheet by pressing enter after filling the last row with data, which would then move the totals down. Also, the formatting of the rows I wish to add need to be copies of the ones above.
View 3 Replies View RelatedI am looking for a code to insert alt-enter into a cell or a column with a defined comma count per line, saying 5
is
C1,C2,C3,C4,C5,C6,C7
C8,C9,C10,C11
want to change the cell(s) as
C1,C2,C3,C4,C5,
C6,C7,C8,C9,C10,
C11
I am working on blood pressures. I want cell C1 to place either, "Prehypertension","Stage I Hypertension" or, "Stage II Hypertension" depending on the values of cells A1 or B1 ....
View 7 Replies View RelatedI am trying to use visual basic editor in excel. I have all ready set up my user form where information can be entered, but I ave having trouble getting the information that is entered in the user form into the correct cells in excel.
I am wanting my information to enter the tables and then automatically move into the next available cells below.
Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("details of cars in stock")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
If Trim(Me.txtreg.Value) = "" Then
Me.txtreg.SetFocus
MsgBox "please enter a registraion number"
Exit Sub
End If
ws.Cells(iRow, 1).Value = Me.txtreg.Value
Me.txtreg.Value = ""
I have some VBA code which hides columns based on a cell value. The cell value changes according to which option button is selected. The code works but not when the button is selected and the cell value changes. It is necessary to click elsewhere in the sheet or press Enter to get the columns to hide. I want it to do it automatically as a user wouldn't know to click elsewhere.
The working code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N5").Value = 2 Then
Columns("O:R").EntireColumn.Hidden = True
Else
Columns("O:R").EntireColumn.Hidden = False
End If
End Sub
N5 is the linked cell for the option button.
I am needing a custom format for the following. I need a cell entry to be 1 letter, three numbers, a dash, 5 numbers, a dash, two numbers, a dash, and then two number. Example: A109-54785-13-00. The first letter will almost always be an "A."
To further complicate the matter, the entry is copied from an email and pasted without the dashes. In the above example, the number in the email would read, "A109547851300." I copy it from the email and paste it in the proper cell in the worksheet. I would like the custom format to automatically enter the dashes at the appropriate spot.
If it was all numbers, this would be easy. I created a custom format for the numbers, but when the A is included, Excel no longer treats it as a number and the custom format did not work.
Is this possible in Excel (without VBA).
I could use the custom number format I created and then later go back and add the "A" at the beginning, but that is as much work as manually adding the dashes.
problem with Excel and I have no idea how to solve it. Hmm, how do I explain this? Someone is giving me data pulled from a server in an Excel format. I have a column that has a series of entries.
For example:
Blank
Entry 1
Entry 2
Entry 3
Entry 4
Entry 5
However, sometimes there's an entry or two missing. So it'll look like this:
Blank
Entry 1
Entry 2
Entry 4
Entry 5
Is there anyway to tell Excel to say "Hey there's an entry missing here," and have it insert a row with the missing entry value? I do not work in Excel ever but people around here do, in case someone has some advice. I already asked the guy if he could just server me the data formatted correctly, and -- of course -- he can't/won't.
I created a form in which I can enter data that is totaled at the end. Is there a possibility that a new row is inserted at the end of the form automatically every time I enter data in the last field?
View 9 Replies View RelatedI have two separate tables, one above the other, and need a way for it to automatically shift the second table down or a row between the two tables any time another row is added to the top table. Is there any way to do that?
View 2 Replies View RelatedThe spreadsheet is used to keep track of shares that are purchased / sold
It will make all tax calculations as well as provide a single statement showing all transactions rather than having to sort through dozens of individual statements.
I seem to have most of it working but could use some help with the following.
I would like to be able to have a spot (maybe on a separate worksheet) where you could enter the four items from which everything else is derived, share price, date, number of shares and whether it was a purchase or a sale, and have it automatically:
1. add a new row (complete with formulas and formatting info) to display the new information
2. sort the new data to fit into the right spot in the spreadsheet by date.
I'm trying to insert pictures automatically on Excel 2000 but i still with problems to find the better solution, please see the file with a better explanation.
View 6 Replies View RelatedI have an expenses sheet where I would like to add something I saw on a template...
press "tab" in the last populated cell of a table to insert a blank row. (below or above, it doesn't matter) So I can add the next expense.
Any way to have the worksheet name on a tab inserted automatically in a cell on the spreadsheet? I have a file used to record orders and I have to keep inserting new worksheets for each order. I want the name on the tab of each worksheet to show in cell A1 so when I copy in a new worksheet A1 will show Sheet 1, for example. However, when I rename the worksheet I want "Sheet 1" in A1 to change to the new name.
View 2 Replies View RelatedI have number of column.Say 10 columns. in Column say J5 I have word daily varience. I want a macro to check word daily varience and go 2 steps to the left and insert the column.
View 9 Replies View RelatedI'm unable to find VBA code to insert a blank row when the value in Column L changes. For example if cell L2 = 400 and cell L3=500 I need to insert a blank row between L2 and L3. I need the macro to search the entire sheet which will have variable numbers of rows but Column L will always have data.
View 3 Replies View RelatedIs there a way you can copy formulas to the next line when a new entry is automatically added to a spreadsheet via a form?
View 1 Replies View RelatedAfter importing a data set into the worksheet entitled "SPACE", I am trying to find the column entitled "Variability Cpu" and then for every cell in that column that has a value in it, I would like to insert a new row beneath that cell. I thought my code made sense, but I get the "Run-time error '424': Object required" error on the line in red below so apparently I'm missing something. why I'm getting this error (doesn't the object variable shtS refer to the worksheet object "SPACE"? Why is it saying object required?). Here's what I've got so far:
[Code] .....
I have a column called "Unit Size," which contains data such as 200 g, 0.32 g, 15 mL, 2 mg.
I need a macro to automatically insert a space between the numbers and the letters, for example, if someone types 10g it automatically converts it to 10 g.
The strings may be of different lengths, i.e. contain different numbers of decimal places or signficant figures.