Code To Enter Blank Rows
Apr 8, 2014I need a code to enter 2 blank rows at each change in data in column B with totals entered at the end of columns E-J.
I have attached a sample with a before and after tab.
ADVERTISEMENT
Code To Remove Blank Rows
Dec 15, 2012share a code to remove BLANK ROWS.
I have data on ROW#1, #5, #10...etc,etc.. I want to remove rows in btwn (which is blank) Instead of doing manually
Code Won't Delete All Blank Rows
Sep 25, 2009When I tried this code on excel at home it worked, but now i'm at work and it dosen't delete any rows at all!
Here's the ....
Getting #VALUE Error When Deleting Blank Rows With VBA Code
Jul 13, 2009I have this code that I have had for a while.. it works okay on my computer and does as intended... it deleted all empty rows in the selected range after checking to see if any cells contain anything that makes it look blank but isn't (it cleans those cells).
So on my computer it works... on a coworkers computer it converts every used cell in the usedrange to #VALUE...
VBA- Code That Will Delete Blank Rows Between Data
Apr 1, 2009a code that will delete blank rows between data, below is sample of my data, the header row is 8
From this ....
VB Code Bringing New Data In Onto The Wrong Blank Rows
Jan 19, 2009Im having some problems with the following VB code
It does import all my data but if I have 4 files for it to import to the 3 different tabs the following happens:
on tab A the first 3 files all come in fine but the 4th file comes in on row 103 instead of the next blank row which would be 22
on tab B the first 2 files come in fine but the 3rd and 4th come in starting on row 94 instead of first blank row which would be 13
on tab C the first 1 file comes in fine but the 2nd 3rd and 4th files come in starting on row 88 instead of 7.
I'd be more then happy to email my spreadsheet to someone as its quite hard to explain whats going on!
I know its probabally a daft mistake I've made in the code if someone could just point out what I've done wrong so I can try to resolve it.
Private Sub ImportData_Click()
'Imports and processes the raw FILE data into the Workbook
'Define a as the current FILE file reference cell,
Dim a As Range
'Define minTime and maxTime as the min and max task periodicity should be processed.
Dim minTime As Long, maxTime As Long
'Define a store for the address of the period totals...
Enter Text Into Blank Cells
Jun 4, 2007As simple as this sounds, I can't seem to make this work. I am looking for code for the following:
I would like to enter the word "Empty" into a cell if the cell is blank. The
range can be variable. The only columns that contain an empty cell are
columns B or C which can have any number of rows.
If Cell In Range Is Blank Then Enter 99999
Mar 16, 2009In the attached sheet, there is a range with lots of entered data (a small section of the total) where I would like to enter into all of the blank cells the value 99999.
I have been selecting the range as follows:
Enter Blank Row Every Time 2ND Character Of Field Changes
Oct 31, 2011I am trying to enter a blank row everytime the 2ND character of a field changes...
sample cells (ALWAYS COLUMN B)(last row needs to be calculated as it changes daily):
2T2W3D3L4H4N4N4N6C6C
when done:
2T2W3D3L4H4N4N4N6C6C
was trying this:
Code:
Dim chkConfirmRw, LastNameRow As Integer
For chkConfirmRw = LastNameRow To 1 Step -1
'Compare the current cell to the one below it
'If they don't match, insert a row row below the current Row
If Range("B" & chkConfirmRw) Range("B" & chkConfirmRw + 1) Then
Range("B" & chkConfirmRw + 1).EntireRow.Insert Shift:=xlDown
End If
'Decrement the counter and do it again
Next
but it does not work.
Macro To Search For Blanks In A Column And Enter Static Value For Each Blank?
Jun 18, 2013So this is what I am trying to do, I have a column in my data that is for telephone numbers. When I receive the file some of the fields are blank in that column. I need to add to my current macro a part that evaluates the column for blanks and adds a static telephone number in the blanks (up to the last row of data in the file). I have been able to accomplish this with the following:
Range("N2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "1112223333"
However, when the file that I receive has a telephone number in all the fields the code breaks at this point. I have tried On Error Resume Next, but that just replaces all the remaining cells in the column with the static 1112223333..
Userforms: Run Same Code When Enter Any Textbox
Dec 16, 2009i have 15 textboxes in my userform (all with tags).
instead of having 15 seperate sub routines that call on one macro is there a way i can write some code that, on entering any texbox within a form, a specific routine would be called
i tried using the Userform_click sub like so, but with no joy:
Vba Code To Get Workbook Name And Enter Into Macro
Mar 9, 2009I need vba code to retrieve the workbook name and place it into macros. the workbook name does not change in the macro if the name of the workbook changes. so i need to assign the name to a variable then use the variable name as the workbook name............
View 9 Replies View RelatedCount Blank Cells Within A Range Not Including Fully Blank Rows
Jul 15, 2008I can count the blank cells withiin a range using
=COUNTBLANK(C6:AD2506)
But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.
It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.
Too Many To Update: Code For CTRL+SHIFT+ENTER
Apr 10, 2008I have a ton of cells that contain formulas with arrays. How can I update these without:
clicking the cell
clicking the formula bar
then hitting CTRL+SHIFT+ENTER
VBA Code - Exit Combobox By Hitting Tab And Enter
Aug 10, 2013I have a problem here regarding the combo box. The whole Column A has combobox. It would be easier if by just hitting the TAB or Enter it would go to the next cell just like what is usual in excel.
TAB = next cell to the right
Enter = Next cell below
VBA - Search Unique Code And Enter Data In Respective Row
May 18, 2013I have a problem to write a VBA code for the following task:
My worksheet has a column that has a unique code in column C (Ex: HR1) which is not repeated throughout column C, and I need to insert the amount of money spent on this specific code.
I would run a userform that has two text boxes, one that I shall enter the unique code and the other text box shall be the amount of money spent. I would like to know of a vba code that can search the unique code of the value entered in the first text box of the userform, then to enter the amount of the second text box into column D in relation to the respective row which the unique code is located.
I would like that the amount entered in column D regarding the unique code could be added automatically by entering any new entries in relation to the unique code selected, if this is possible. If not possible, to go inserting any new date into the next column regarding the same row of the unique code.
Ex:
A
B
C
D
[Code].....
Enter Only Part Of A Zip Code And Have The Rest Auto Fill-in
Mar 3, 2009i have an excel sheet where i enter company information including Zip code. the zip codes around here all start with 797 and end with numbers from 01 to 12
i am looking for a way to enter just the last 2 numbers and have it automatically insert the full zip
example:
line one i would enter 01 and 79701 would show up in the box.
I know there are ways to do this, just can't remember its been so long since I've had to use excel.
To Create A Userform With A Textbox To Enter A Product Code
Jun 11, 2009i would like to create a userform with a textbox to enter a product code , a command button to run VBA and a big text box to show output of VBA from Textbox 1 which i will connect to DB and pull back information relevant to that product code , is this possible i can create the userform no problem and already have code to pull back from DB into Excel ,
View 9 Replies View RelatedEnter Array Formulas To Range Macro Code
Apr 23, 2008I'm trying to enter a series of formulas referencing the first cell of each row.
With Range("A40")
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).FormulaR1C1 = "= COUNTIF(Details!R2C2:R65536C2,RC1)"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 2).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC[-2])*('Details'!R2C11:R65536C11=RC1))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 4).FormulaArray = "=SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C4:R65536C4>TODAY()-7))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 5).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C11:R65536C11=RC1)*(Details!R2C4:R65536C4>TODAY()-7))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 7).FormulaArray = "=SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C4:R65536C4>TODAY()-30))"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 8).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C11:R65536C11=RC1)*(Details!R2C4:R65536C4>TODAY()-30))"
End With
While this code works for the first formula, the following 4 are arrays, and for some reason, will only reference the first A40 cell.
VBA Code To Find Date In Column A And Enter Data In Cells On Same Row
Dec 26, 2013I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.
VB:
Private Sub CBSecure_Click()
Dim my_name As String
Dim r As Variant
Dim l As Long
sFind = DockDoorCal.Value
If Trim(sFind) = "" Then Exit Sub
[Code]....
Automatically Refresh Without Having To Click Or Press Enter To Get VBA Code To Work
Feb 25, 2013I 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.
How To Enter Information In Excel And Use VBA Code To Transfer To Word Document
Jul 17, 2014I'm trying to get certain Excel cells (varies by column and row) to populate different areas in a Word document. I've tried using both Word bookmarks and form fields and have defined the Excel names with the Word bookmark names as well as using Word's generic terms (Field1, Field2, etc.) with no luck. The VBA code I'm using now is for word forms and will open the Word document but seems to delete the form fields. I don't know if I'm not coding the cells correctly or what.
Sub CreateProposal()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = CreateObject("Word.Application")
' Open word document
[Code] ........
Messagebox Prompt Then Allow User To Enter Data Before Continuing Code Execution
Jun 11, 2013Basically, in the "Thisworkbook" code , i have some code in the Workbook_BeforeClose section. Currently , it autosaves the workbook in a folder i have specified.
However, i need to add some code.I want to check that a certain cell has a value in it before the user closes the workbook, and if the cell is empty, show a messagebox asking him to enter a value.
I know how to get a messagebox to pop up, the only thing is once the user clicks the OK button,
i need the rest of the code execution to pause, allowing him to make the change then if he clicks the "X" (top right of the screen) to close the file or application, the filesave dialog appears and he can then save the document.
how to go about this because at the moment when user clicks ok, the messagebox just disappears and filesave dialog appears and he doesn't have a chance to edit the cell.
ActiveX Command Button Code To Enter Values From One Range Of Cells Into Another
Jun 15, 2014well, the clear worked well and now I have another idea for a button for my worksheet;
I want the button to insert values from other cells. so when it is clicked, the values in cells F82-F86 are entered in cells E19 - E23.
Enter Formulas Only On Subtotal Rows
Jun 22, 2012a huge spreadsheet that is taking up way too much time. It starts out with 6,000-7,000 rows, but then, she does Subtotals & it grows to nearly 10,000 rows! Then she has to deal with each & every one of the Subtotal rows, by hand. Here is a small sample of the spreadsheet, after the Subtotals have been applied:
Manifest Dttm
Manifest No
Carrier Scac
[Code].....
This sheet has columns A-Q. The key columns for our purposes are K (Weight In Lbs), L (Pickup Charge) and M (Consolidation Charge). We need to add information to column R on each SUBTOTAL row only! On each row there will be an amount in EITHER column L or column M, but not both.
1) If the amount is in L and K is LESS than 488 then R needs to show 44.39.
2) If the amount is in M and K is LESS than 124 then R needs to show 3.82.
3) If either
a. The amount is in L and K is 488 or HIGHER or -
b. The amount is in M and K is 124 or HIGHER
THEN R needs to show a formula to do this: (L+M)/(K/100)
Is there a way to either put a formula in just the Subtotal rows to accomplish this or (preferably) have a macro enter either the 44.39, 3.82 or the formula? I was able to come up with a formula that gets the correct amount in col R. Here's my formula:
Code:
=IF(AND(M3=0,K3123))),SUM(L3:M3)/(K3/100))))))
Excel 2003 :: Allow User To Enter Post Code And Some Details Will Be Returned Using VLookup?
May 8, 2013I have produced a basic search/lookup facility on an Excel workbook that simply allows the user to enter a post code and some details will be returned using a vlookup. The document is going to be rolled out to a number of operational users so I want to basically 'lock down' everything I can in the document (basically everything except the data entry cell) and make it fool proof- I have locked all cells apart from the data entry cell and have made the file read only.
The only issue I am encountering is when the cursor is in the one 'unlocked' cell (i.e. the one the users will enter the post code into); it appears that you can break the document. For example, when the cursor is in the 'unlocked' cell, I am able to go to Tools > Options and change various settings including cosmetic colour changes but also cell calculation which breaks the lookup functionality. This is probably enabled as the cell is unlocked, but if I lock the cell, when I protect the document, it disables data entry!
if there is another way of providing this one cell for users to input data into for the vlookup to work whilst locking down the rest of the document to ensure that no-one can break it?
VBA If Range Value = Something AND Another Cell Has No Entry- Prompt The User To Enter Data- My Code Is Not Working
Apr 20, 2009I am trying to display a message box for the user if there is "agency" in cell o8 but nothing in p8... I tried the following code but it doesnt work..
If Range("o8").Value = "Agency" And Range("p8").Value = "" Then
MsgBox "Please provide name of agency in cell p8"
Sheet9.Shapes("cross").Visible = True
Else
Automatically Add Rows To End Of Spreadsheet By Pushing Enter
Jun 1, 2006I 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 RelatedFormula To Enter "0" If Cell Blank
Nov 4, 2005trying to eliminate error messages needed for math formula. With "0" in cells
works good "anyone"
Macro To Split Rows Into Groups Of 5 And Insert 3 Blank Rows In Between
Feb 9, 2013I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....