VBA - Move To Next Blank Row After Input

Jan 23, 2014

I have a form on a separate tab where I input data then have a button that should update the data to a separate tab. Using the code snippet below, on a blank sheet it works but on the second input it ends and the next record input overwrites the second line and all subsequent input does the same. It basically stops at line two.

Code:

Sub Button7_Click()
Dim DateRecvd As Date, OccurDate As Date, SalesLine As String, OwnerLName As String, OwnerFName As String, RepID1 As Integer, RepID2 As Integer, MgrName As String, QAName As String, ContractNbr As String, Policy1 As Integer, Policy2 As Integer, Policy3 As Integer, Policy4 As Integer, Policy5 As Integer, Summary As String, ActionTaken As String, Outcome As String, Volume As Long, SiteNote As String

[Code]...

The Clear Form is used after entering a record and it works fine. I tried a few different things I found online but no luck, it always goes back to the second line and won't advance to the next empty row.

View 2 Replies


ADVERTISEMENT

Move Between Input Cells Only

Apr 6, 2008

I have designed a form for my wife to use in school to help teach fractions and decimals.

The children will enter the numerator into a cell (say D2) and the denominator into another cell (say D6) and the decimal equivalent is then displayed to 2 decimal places in a third merged cell (say C8 - E8).

What I would like to do is this:

When the worksheet opens, the only cell that I want the children to be able to enter data into is the first cell (ie D2). When they enter a value (say 1 for the numerator) I'd like the return key to move the data entry to the next cell (which is D6). After this, when they have entered another value (say 3 - they are invesigating thirds for example), I'd like the return key to move to the next numerator cell (which is H2), then denominator cell (H6) and so on.

How do I do this? I have never created forms or macros and I suspect that this is how to do this.

For the first set of fractions that they are investigating, thirds, I'd like to limit their data entry in the denominator cells only (D6, H6, L6, P6, etc) to 3 and reject any other number. Later in the worksheet, they will be investigating quarters, fifths and so on.

How do I do this? I don't want then to use any list or drop-down, simply to type a value into the cell and it be accepted (if it is a 3 when investigating thirds) if it is correct or simply ignored if it isn't. Is this possible?

way, especially if it involves anything complicated like macros. Being active on lots of forums it is quite depressing when I see that someone has answered a poster's question but the answer seems to either assume a high level of knowledge or tends to assume that certain steps are taken without actually stating them.

View 9 Replies View Related

Move Between Input Cells By Keyboard

Aug 16, 2008

There are a series of input cells in a protected worksheet and when the user hits an enter, tab or arrow key, the selection cycles through them in order. The cycle also restarts when the mouse clicks one of the cells out of order. After I switch worksheets and return to the first worksheet with the code, first it says there is a "Compile Error: Sub or Function Not Defined" and the code "Call SetKeys" in Sheet1 is hilighted. When I ignore that and stop the debugger, there is a "Run-time error '13' Type mismatch" and the code line "If arrIndex > 0 And arrIndex < UBound(arr) Then" is highlighted.

My question is: why doesn't the code work after I switch worksheets? especially given that it works perfectly before I switch worksheets. (ie the file opens on sheet 1 and everything is perfect, click sheet 2 tab and back to sheet 1 - nothing works) I also have yet to figure out why it seems to be necessary to post the same code in both ThisWorkbook and Sheet1.

Private Sub Workbook_Open()
'This code allows the user to change the unlocked cells, but protects each worksheet
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="mypassword", _
UserInterFaceOnly:=True
Next wSheet............................

View 5 Replies View Related

Input Daily Data - Variable Do Not Move In Formula

Jun 6, 2014

I have create an excel formula that I need to input data daily.

Every day, I need to insert a line and input my new data.

Below is my formula

=IF(V159="Skip","Go",IF(P160>0,IF(COUNTIF(C60:D154,">"&H159+3),"Good"),IF(COUNTIF(C60:D154,"<"&H159-3),"Good")))

The problem is that when I insert a line. Most of my variable will change according except C60:D154

As you can see below

V159 change to V160 = This is good

C60:D154 do not change accordingly. How can I make it change accordingly ?? Why it is not changing when I insert a line ?

=IF(V160="Skip","Go",IF(P161>0,IF(COUNTIF(C60:D154,">"&H160+3),"Good"),IF(COUNTIF(C60:D154,"<"&H160-3),"Good")))

View 7 Replies View Related

Interupt Code For User Input: Move The Contents Of One Row To Another

Nov 2, 2008

Need way to interupt my code whilst the user selects a row. ie. if I wanted to move the contents of one row to another I would want to say: "select source row" (and want the user to click on any cell in that row) I could then put the rownumber in a variable with

View 2 Replies View Related

Handling Blank Input Box

Dec 27, 2008

handling blank input box
If I do this:

View 5 Replies View Related

Move To First Non-blank Cell In Column

Feb 8, 2009

I will be moving to a specific cell - "N1". I'm looking for code that will then scan down from that cell, making the first blank cell the active one.

View 4 Replies View Related

Input In Any Blank Cell On The Workbook

Mar 5, 2009

Running excel 2003 and would like to make a macro for a workbook. Example I want a macro to say (see the big truck), I can record this ok but can't get it too play in any cell on my workbook. I want too be able and input this in any blank cell on the workbook. Is this possible? when I try this I just get nothing in the cell.

View 2 Replies View Related

Move To Next Blank Cell When Pasting Range

Jun 10, 2009

VBA to find the next appropriate blank cell in column "A" when copying ranges of varying sizes and pasting them in a new worksheet one after the next.

Currently I have recorded a macro that uses the code below to get to this location, but I am worried this will break when I get new data of a different range size;

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'my initial range selection
Selection.Copy
Windows("myfile.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select 'finds last cell of my paste
Range("N69").Select 'I hit the down arrow once to get to new blank row
Selection.End(xlToLeft).Select 'to go to begining/col A of new row for next paste

I have tried playing around with

ActiveCell.Next

but can't get it to work.

View 3 Replies View Related

Pulling Data From List If Cell Blank Then Move To Next

Apr 16, 2009

Trying to pull data from a list and if the cell above is empty then pull the data from the cell below and so on........

I have some code for it..but its just a little bit too long and I can't use it

This is what i have so far, trying to simplify it as its way to long to fit in a singel cell....i did think about breaking it down in to collums of either 8 or 6 and having it in chunks that way...but if i did that then i would still have to eventually use this forumla below as i don't really want huge gaps within the form i'm doing.

View 13 Replies View Related

Copy; Paste With Input Box, Adding A Blank Cell

Nov 1, 2009

Data range is A2:A301. From this range sometime all and sometime selected data is required to be copied in another column for example C2
downward. What is needed: select the range from A2:A301 with an input box then the required data to be pasted in C2 downward but with a blank cell after four cells.

C2
C3
C4
C5
blank
C7
C8
C9
C10
blank
and so on.

I'm doing it Ctrl+C n Ctrl+V but that is very time consuming.

View 5 Replies View Related

Userform Macro To Use Textbox Input To Generate Blank Rows?

Mar 28, 2014

I have a userform with 16 Textboxes Named "Text1" through "Text16". What I am trying to do is apply code that if there is content in the textbox it generates a new row after row15 and inputs the text in cellA of that row. Ex. "Text1" = JOE "Text2" = TONY......Hit Commandbutton1 on userform. It inserts Row16 puts "JOE" in A16.....then sequentially it inserts Row17 puts "TONY" in A17.

View 14 Replies View Related

Possible To Define Range For Combobox Input Based On Number Of Non-blank Cells?

Nov 25, 2013

Rather than having to give it a range, I'd like to have a generic range giving me room to add or subtract values in the column without changing this statement.

Code:

Worksheets("Sheet1").Shapes("Combobox2").ControlFormat.ListFillRange = _
"O2:O14"

View 3 Replies View Related

Clear Data Depending On User Input And Move Cells Forward Without Deleting Data Table

Oct 25, 2012

I have an month input in cells B2 (user can enter values from 1-5)

I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.

So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.

So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.

1
2
3
4
5

£11
£133
£29
£193
£100

[Code] ........

View 3 Replies View Related

Move Down Column And For Every Blank Cell Clear Contents Of Cell To The Left?

Jun 1, 2014

i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows

View 3 Replies View Related

Prevent The User From Saving The Data Input From The User Form If Any Of Those Three Fields Is Left Blank

Feb 9, 2010

I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.

What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)

View 2 Replies View Related

Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column

Sep 19, 2008

I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.

A B C D E
1 X X X X
2 X X X X
3 X X X X X
4 X X X X
5 X X X X

View 9 Replies View Related

Input In Multiple Cells To Generate Input Into Single Cell?

Feb 22, 2014

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

View 4 Replies View Related

Input Box To Create And Input Info Into A Formula Which Then Populates A Cell With A Value

Jul 24, 2014

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.

View 9 Replies View Related

Transferring Of Input From Input Cell To Output Cells?

Jun 8, 2014

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.

View 3 Replies View Related

UserForm Input (a Button That Opens An Input Box Or Dialog Box)

Jul 4, 2006

I have to design an input user form (a button that opens an input box or dialog box), which asks user to "Enter number of "columns"".

User enters a number i.e. 2 or 3 etc

It then asks user for the headings of these coulmns and places first column heading in A1 and second column heading in B1, third in C1 and so on ...

View 9 Replies View Related

Input Text In Given Cell After Any Input In An Area

Feb 28, 2007

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 Related

Is There A Way To Stick A Few Row Of Cells Together So That When I Move One They All Move

Jul 7, 2009

is there a way to stick a few row of cells together so that when i move one they all move.. i have some rows that when i sort them i want them to move together with the other ones..

View 9 Replies View Related

How To Change An Input Box Based On Input In Another

Dec 9, 2008

What I am trying to accomplish....

If the input changes in D5, K11 will reset to $0.00..

Example. If cell K11 is set to $300.00, and the corresponding input in D5 is '123', when I change the contents of D5 it sets K11 back to '0.00'.

View 9 Replies View Related

VBA To Automate Text Input Into Input Box

Feb 6, 2014

I am in need of VBA code so that I can fill out an input form generated by someone elses macro input box. (I am the user, not the one developing the input box).

I am working to streamline a whole cluster of macros and this input box is currently my bottleneck. When the input box pops up, instead of me manually typing, I want to autofill the space with "104" and then click 'ok' uisng VBA.

I can not modify the actual VBA that created the input box (ie make 104 the default) because other users need the box to be blank.

find out how a developer would change the original vba code, not how a user can interact with an input box by using vba.

View 3 Replies View Related

Input Box Allow To Input The Column Number

Dec 5, 2007

I want to create an input box that will allow you to input the column number it should start in... so instead of it being fixed at A, you can choose column B, C, D all the way to the end if you like.

Sub SplitSheetsNoDelete1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Lmainsheet As String
Dim Lrow As Integer
Dim lcontinue As Boolean

Dim LColamaster As String
Dim lcolatest As String

'Retrieve name of sheet that contains the data
Lmainsheet = ActiveSheet.Name

'Initialize variables
lcontinue = True
Lrow = 2.....................................

View 9 Replies View Related

Move Cell Value To Another Cell If Another Cell Is Blank

May 27, 2014

Attached is a sample of what I am working with.

For some reason when pulling this report into excel, the description is under the part number and the ordering information is on the line with the part number. What I am trying to do is have the description move up one row and over one column if the value in C is blank on the same row. All of the information should be on one line. This report can have hundreds of records so I need some kind of looping code to get the end product where I need it.

example1.xlsm‎

View 2 Replies View Related

Leaving Blank Cells Blank In Dragging Formula Combining Different Formulas In One Cell?

Aug 2, 2014

I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.

View 8 Replies View Related

Excel 2010 :: VLookup Result Blank If Column Index Number Is Blank

Mar 3, 2014

The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.

I'm not sure how to say leave the result blank if the column index number is blank.

Excel 2010
Userform = Tab 1
Database = Tab 2

View 6 Replies View Related

Count Blank Cells Within A Range Not Including Fully Blank Rows

Jul 15, 2008

I 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.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved