VBA Code To Populate A Cell Automatically Whenever Another Cell Is Filled In Manually?

Mar 29, 2014

I have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.

When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.

I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.

View 8 Replies


ADVERTISEMENT

Populate Formula Using User-filled Cell References

Jul 9, 2012

So I have a table where column B contains a text entry, and column C returns a certain other text entry based on if certain keywords appear in the B entry. Say, if B8 contains "Fresh Apple Pie", column C8 could return "Tastes Delicious" by searching for "Apple" in B8. I've got it work just fine using a ridiculously long If(Isnumber(Search())) function, but the problem with this is that if I want to adjust the output, or add a new input rule I have to go into the formula and that just gets messy. Instead, I'd like to start an extra sheet, or a table to the side, where the user can write in column D what he's looking for, and then in E what the formula should return. Ie., if I wanted to change "Tastes Delicious" to "Getting Sick of It", I'd just have to change the E8 entry to that text, but leave D8 as "Apple". I've been playing with array formula's, but I can't get the formula to search past the first row of criteria.

Basically, I want something like this, where columns D and E are open ended for any user to add new search criteria or change the existing (for example, I could put "Gummy" in C4 and "Candy" in D4, and B3 would then return "Candy"):

Item
Output

Search For
Classify as

Candy Apple
Fruit

[Code] .....

I've been using this formula:

{=IF(ISNUMBER(SEARCH(C3:C4,B12)),D3:D4,"")}

(the cell references are different, but you can see the formula)

And it half works. If C3 is found in B12, it'll return D3, but if C3 isn't found I need it to look for C4, which it doesn't do. Naturally, the end formula will be a much larger range than two cells though.

View 6 Replies View Related

Automatically Delete The Same Value In A Cell When Filled In Other Cell?

Mar 6, 2014

I'd like a code to automatically delete the value in column AK and its corespondent in AL, if the value in AK is filled in column C

View 7 Replies View Related

Cell To Automatically Populate

Mar 26, 2008

i am working on a spreadsheet that includes a large amount of statistical numbers / records. one part includes a win / loss column and the very next column i am entering the final score for each game. to max this easier to comprehend, there are only 3 columns. 1st column lists my opponent, the 2nd lists, with just a red L for loss, or a green W for win, and the 3rd column has the final score. (my score is always first, regardless of whether i won or lost the game.)

OPP F SCORE
--------------------------------------------------
John W 23-10
Paul L 17-25
Terri W 10-7

This is what i'm searching for: (and the easiest way possible b/c i'm not familiar with macros / visual basic, etc.) ... Once i enter the score: ##-##, I would like the "F" (final outcome) cell to automatically populate either a bold red L for a loss, or a bold green W for a win. My scores are almost never 3 digits, so they would almost always be ##-##. if u need me to provide more info,

View 12 Replies View Related

Automatically Populate Another Cell When One Is Updated?

Apr 27, 2014

I have a spreadsheet where vehicle milage is entered at the end of each month. On another sheet I have the vehicle fleet numbers and the the months along the top. What I need to happen is; when the mileage cell is updated I need the cell in the other sheet to be automatically populated, when the mileage is updated the next month I need the next cell(February)to be populated with the new milage and so on. The aim is to calculate monthly milage for each vehicle without having to enter the date at the beginning and end of the month.

Everyone I work with is completely computer illiterate and only knows how to enter the data the same way they always have(just the once at the end of the month) changing it up will only cause issues.

View 1 Replies View Related

Automatically Populate Cell Based On Worksheet Name

Mar 18, 2009

I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column on the summary page for each line of theirs that is copied over....here is my current macro I use to copy the data: (this scrpit is repeated for each sales persons sheet - "Blankenship", "Dew", etc...)

View 5 Replies View Related

Populate Automatically With Predefined Value If Cell Is Of Certain Colour

Feb 8, 2012

I'm looking for a cell formula. here is an example:

In cell A2, I want it to populate automatically with a predefined value if cell A1 is a certain colour, as below.

A1 A2
559.00

I have conditional formatting in cell A1.

View 6 Replies View Related

IF / VLOOKUP (cell Automatically Populate Commision Earned)

Mar 2, 2010

I am trying to get a cell to automatically populate commision earned.

Commision is worked out as a percentage to gross profit and works as follows:

£0-4000 = 0%
£4001-8000 = 1%
£8001-12000 = 2%
£12001-16000 = 3%
£16001+ = 5%

I have my spreadsheet that details their sales and profit. the profit is calculated in cell M45.

I have tried for ages to get an equation to work, and have ad no joy. It is actually driving me mad now. I need a better man/woman than me.

View 9 Replies View Related

Cell That Contains A Zip Code And Populate Another Cell

Jan 19, 2007

I am trying to create a formula that will reference a cell that contains a zip code and populate another cell with the appropriate technicians ID.

It's simple with one tech covering only one zip code

View 14 Replies View Related

Conditionally Format Cell Based On Another Cell That Is Manually Colored?

Nov 24, 2013

let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?

View 7 Replies View Related

Copy Cell Data To Other Worksheet Cell - Leave Blank If Not Currently Filled

Jul 8, 2014

I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.

I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).

View 4 Replies View Related

Automatically Add Cell Formula With Code

Dec 4, 2006

Cell b2 contains the formula
=IF( COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")
I want cell b3 to contain the formula
=IF(COUNTIF(B$1:B3,B3)=1,MAX(A$1:A2)+1,"")
I can do this within the spreadsheet by simply highlight and drag down to autofill and excel updates the formula references as I need

However - because the sheet gets very large (its a sort of rough database)
I dont fill in all the formulae on a blank sheet, but each time a record is entered using a VBA form I have the following working code copy the formula

ActiveCell.Offset(nextline, 0).Copy
ActiveCell.Offset(nextline + 1, 0).Select
ActiveSheet.Paste

However I dont want to use 'Active' - because I have to work out what cell is selected before this is executed and then reselect it after, which also causes the screen to flash when the active sheet changes so I tried the following which does not work

Worksheets(" Analysis").Range("A2").Offset(nextline, 0).Copy
Worksheets("Analysis").Range("A2").Offset(nextline + 1, 0).Paste

because 'object does not support this property or method' on the Paste.

I can save the code in a string and modify the string to generate the new code, but I cant find a way to assign the new formula to the next cell.

View 9 Replies View Related

Populate Combobox In VBA With Only Filled Cells From A Range

Nov 2, 2013

I have a combobox on a spreadsheet it is populated on a sheet called teams :

The Format control has an input range of: Teams!$B:$B

Now this has a few thousand empty cells in the range but I would need it to only populate with the cells that are not empty.

View 3 Replies View Related

Advanced Filter Is Working Manually But Not Automatically

Jan 28, 2010

I have made a small routine that updates an adv. Filter automatically, but is doesn't provide me the right outcome (seems it does nothing). I also recorded my actions and also here an empty outcome.

View 2 Replies View Related

Excel - Automatically Row Highlight Like Manually Highlighted?

Apr 13, 2013

I want to know - how to highlight a row (or selected row cells) automatically, when any of the cell is filled with any data..

the row should be highlighted when i go to the next row - like mannually highlighted

if its by macro - let know the code as i tried on what i know but was unable...

View 2 Replies View Related

Automatically Execute Code Based On Cell Change?

Sep 5, 2013

I need to be able to hide and unhide a given set of rows based on the value in a particular cell. My current code allows me to successfully do this, ONLY, when I select the cell being 'watched' for changes and press enter. It does not execute the code automatically.

The cell is changed by a set of filters that modifies the data. The watched cell is then a summation of the filter modified cells. If the filters change to all "0" or "-" then my 'watched' cell sum becomes "0" and thus should execute the code automatically.

Current Code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Outline.ShowLevels RowLevels:=3
If Range("I62").Value = "0" Then
Rows("63:87").Hidden = True
End If
End Sub

So, right now if I use the filter and the cells change then the sum in I62 becomes "0", I have to manually select cell I62 and press enter and which point the above code executes exactly as I intend.

I need the execution part to be automatic and not have to manual click into cell I62.

View 1 Replies View Related

Update Automatically, Not Manually Extending The List On The 2nd Sheet

Nov 18, 2008

I have a list (external), which will be updated every month from an external source with new numbers and possibly a new size. This will be a count of services people use. On a 2nd sheet I need to allocate a cost to these services (pivotdata). This works fine if the list stays the same size, but it most probably won't so I need it to update automatically, not manually extending the list on the 2nd sheet. In brackets are the named ranges of the data in the spreadsheet.

View 5 Replies View Related

VBA Code To Automatically Fill Down And Apply Formula Till Last Cell

Aug 22, 2012

I am using this code and it works fine:

Dim MyData As Range
Dim MyResult As Range
Set MyData = Range("E1:E1000000")
Set MyData2 = Range("F1:F1000000")
Set MyResult = Range("J4")
MyResult.Select
Selection.Formula = "=COUNTIFS(" & MyData.Address & ",""=Kim""," & MyData2.Address & ",""=done"" )"

Is there any way I can change the code so that it automatically finds the last cell as Im using Range("E1:E1000000") and Range("F1:F1000000") because there will not be more than 1000000 entries.

View 2 Replies View Related

How To Automatically Add Row If Last Row Is Filled

Jun 2, 2014

I'm trying to build a invoice template for my business. Trying to make it as simple to use as possible for my staff to input the data. Currently they will manually type in everything but later on I would like to make the spreadsheet have available drop down boxes for the equipment and it will auto fill the hourly rate in.

Right now my main issue is I have the invoice template the way I would like it, but there are only 5 rows in each category. I would like for it to, once the last row in each category is filled, make a new blank row with the exact formatting of the one above it. Then continue to make blank rows if that subsequent row is filled in as well.

Basically, to better describe what I'm looking for, say I have 15 employees on the job and 10 pieces of equipment. I currently only have 5 rows in each category and would like them to expand out automatically without doing a copy rowinsert. It doesn't matter if the page expands to multiple printable pages as long as it doesn't get jarbled in the process.

I am very new to Excel and not even sure if this is possible. I would also need this to carry on with the .xlsm file so that my other employees can use this invoice template on their laptops.

I attached the invoice template I created so what I'm describing can be better understood.

View 3 Replies View Related

Show Number On Sheet1 From Cell On Sheet2 If Cell Next To Cell Containing Number Filled

Aug 13, 2013

SAMPLE FILE "error_finder.xlsx (36.5 KB)" attached...

The VP of our company needs me to create a spreadsheet.

For this s/s I need the following:

Imagine 2 sheets in Excel...

sheet1
sheet2

sheet2 has sequential numbers in column1 starting in row2 with the number 1 until wherever..., so cell A2 has number 1, cell A3 has number 2, etc...

Manually, a user has to fill in certain text next to a number.

This for example would look something like this:

_|A| B
-|-|-----------------------------------|
1| | Error-description
-|-|-----------------------------------|
2|1| Whatever the 1st error would be...|
-|-|-----------------------------------|
3|2| Whatever the 2nd error would be...|
-|-|-----------------------------------|
4|3| Whatever the 3rd error would be...|
-|-|-----------------------------------|
5|4|
-|-|-----------------------------------|
6|5|
-|-|-----------------------------------|
and so on...

All this would be on sheet2

In a cell on sheet1 I now need the number from column A displayed, that has the latest entry in column B.

In the example above this would have to be the number 3 in cell A4, because right next to it (in cell B4) is the last entry "Whatever the third error would be..."

Addition: There are several sheets, each sheet stands for one error listed on sheet1 in column A

sheet1 for example would look like this:

_|____A____|_B_|_C_|_D_|
-|---------|---|---|---|
1|_________|833|933|934|
-|---------|---|---|---|
2| error 1 |___|___|___|
-|---------|---|---|---|
3| error 2 |___|___|___|
-|---------|---|---|---|
4| error 3 |___|___| 3 |
-|---------|---|---|---|

In this example, because "whatever error" (cell B2 to B4 on sheet2) was found on press 934 was the 3rd error (cell A4), the number 3 (cell A4) would have to be populated on sheet1 in cell D4.

View 9 Replies View Related

Put Manually Formula In Specific Cell?

Jun 5, 2014

I would like to be able to put manually a formula in a specific cell (like a "requested date" cell) and then have a table that use this date for the headers and calculate +1 year for each columns.

For exemple:

=2012-01-01
=2013-01-01
=2014-01-01
=2015-01-01

requested date:
2012-01-01

Would you know a way to easily do it? when I try to do it manually (+365) it doesn't consider leap years. I thought about a =DATE formula but the requested date have to be manually entered...

View 3 Replies View Related

Move Contents Of Manually Selected Cell

Aug 22, 2009

I have an array, let’s say B1:D20. I would like to be able to manually select any cell within this array and then run a macro which will place the contents of the selected cell into the cell in column A in the same row as my selection.

View 3 Replies View Related

Cell Colour Change Only Works Manually

Dec 3, 2011

I have the below code that changes the colour of a cell to green if the figure increases, and to red if it decreases.

The issue is that it works fine if you manually change the figure in A1 or A2 by entering the figure and hitting enter. If the cell figure changes in A1 by any other means such as RTD or say a value in C1 "=C1" then the colour change is not triggered.

Code:
Public oldval, myval

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
oldval = myval
If Target.Value > oldval Then
Range("A" & Target.Row).Interior.ColorIndex = 4

[Code] .......

View 9 Replies View Related

Change Color Of Cell If Manually Changed Value

Jan 3, 2014

How can I get a cell to change color if a user were to modify or change the value of the cell contents?

View 4 Replies View Related

Excel 2010 :: VBA Code To Automatically Change Colour Shape Based On Cell Value

Mar 2, 2012

I have a peice of code that i know is inefficient and it is in danger of becoming too large. I have a spreadsheet that has circles aligned to each cell. There are around 100 in total. The code changes the colour of the shape based on the cell value in which it sits. However, the code needs changing and also it does not automatically update the colour shape even though the cell value changes. I have to manually select a cell and then the formula bar and then press return for it to update.

I am using excel 2010.

This is the code i am using for each shape.

Code:
If Range("n12").Value = text Then
ActiveSheet.Shapes("Oval 250").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If

[Code]....

View 6 Replies View Related

Way To A Column Be Filled Automatically By What Is Written On Others

Jan 15, 2009

Is there any way to a column be filled automatically by what is written on others columns.

Example: If one of the cells A2 or B2 and C2 are Nonblank, fills automatically the cell H2 with "ABC2" And If some of the cells D2 and E2 are Nonblank fills the cell H2 with "DE2"

View 2 Replies View Related

Use Current Cell And Column As Range Without Entering It Manually?

Jan 6, 2013

I have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:

VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range

[Code]......

View 2 Replies View Related

How To Allow Both Picklist Items And Manually Entered Data In A Cell

May 27, 2014

I have been using Excel for several years and I'm fairly proficient at its basic functionality. I've recently learned and made extensive use of pivot tables. However, I have not taken advantage of such features as Data Validation, VBA, etc.

View 3 Replies View Related

Reversing Sign Based On Cell Being Manually Coloured

Nov 13, 2008

I have a range: AC2:AG1400 that are filled with numbers....

Someone manually highlighted, in bright yellow, some of the numbers.

I need a macro that could reverse the signs of only those that are coloured in that yellow.

View 9 Replies View Related

Copy To Next Cell Down When Cell Is Filled

Jul 29, 2006

Ive tried to use many of the post on this topic but I suppose I just dont understand how to stitch this into my actual code. Im extremely noob at VBA and the only way I can make macros is with the record tool.

My exact problem is I have a row of data in one workbook which is generated by a calculator built into it.

I then need the data from the output result table to be copied to another workbook in a table. I would then like the user to be able to run a different calculation on the calculator and have the results copy to the next row down as not to disturb the results of their first calculation. (I would like to assign this to a button next to the original result table)

Heres the code I recorded that just overwrites to the same spot. I was hoping I could just edit the paste location to make it skip down to the next available cell starting below the first (C25:F25).

I dont know how you guys make your code look all nice and indented either, so I apologize if its confusing I copied what the recording outputed....

View 9 Replies View Related







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