VBA Autofill Based On Specific Cell Values?

Aug 25, 2012

I want a range to be filled based on 2 cell values in my sheet. The autofill range should be filled based on the values in the input range. For instance in cell A1 I place the value 1 and in cell A2 I place the value 10, then the macro should automatically fill in the numbers from 1 to 10 in another column. If I change for instance the input values to 5 and 15, then the autofill range should fill up the numbers from 5 to 15.

View 4 Replies


ADVERTISEMENT

Insert Pictures Based On Cell Values From One Specific Folder

Mar 14, 2014

where i have a products list and respective pictures in a folder. What is should do is to get the relevant pictures appear on the screen when i select the relevant name.

View 1 Replies View Related

Autofill Incremental Cell Values In A Formula

Jun 7, 2007

I have two worksheets in the same workbook. In Sheet1, I've got a whole bunch of stuff that I need listed, like names, addresses, phone numbers, etc. What I want to do is pull the values of only the names, which are listed every 42 spots, and put them into a list on Sheet2. So, for example, in Sheet1, A1 is a name, A43 is a name, A85 is a name, etc. In Sheet2, I want to list only the names. And if there isn't a name yet, I want a blank cell. I've sorta figured out how to do it, but I get a problem with the values not incrementing correctly.

So, example code would be
=IF(ISBLANK(SHEET1!A1),"",A1)
=IF(ISBLANK(SHEET1!A43),"",A43)
etc.

The problem is when I drag the auto fill, the next values it gives are
=IF(ISBLANK(SHEET1!A3),"",A3)
=IF(ISBLANK(SHEET1!A44),"",A44)

Instead of the

=IF(ISBLANK(SHEET1!A85),"",A85)
=IF(ISBLANK(SHEET1!A127)"",A127)
....

that I want it to give.

is there anyway to get the values to go up like I want them to, or do I have to enter them all individually? That would really not be fun for me, so I hope that's not the case.

View 4 Replies View Related

Autofill Based On Cell Condition

May 20, 2014

I have a workbook sheet change condition that, when triggered runs the following code (amongst others).

[Code] ......

What I would like is to amend the above (possibly in a loop?) so that it skips the rows in which the value "New" features in range F17:F190.

So basically Autofill as per the code but leave the values in the cells of the range in which New features in range F17:F190

View 4 Replies View Related

Autofill Last Day Of Month Based On Another Cell

Jul 25, 2012

In cell A12 the format is mm-###. Based on the mm (2 digit month) in cell A12, I would like cell B12 to auto fill the last date of the month as mm/dd/yyyy.

View 4 Replies View Related

AutoFill Down Rows Based On Another Columns Last Cell

May 13, 2008

I have a workbook which has one sheet "Raw Data" where I will cut and past blocks of data of various numbers of rows. Another sheet "level one calcs" conducts various calculations on the raw data. I was wondering if there would be any way to create a macro which would copy down (autofill) the last row of my my calculation page for the exact number of rows I added into the raw data. The goal here would be so that I dont have to highlight and copy down the formulas on the calc sheet each time I add in new data.

View 3 Replies View Related

Excel 2007 :: Autofill Column B With Sequential Values Based On Value In Column A

Nov 27, 2011

Autofill column B with sequential values based on whether value in column A changes its value.

I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.

This is what I imagine.

A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"

I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.

I'm using MS Excel 2007 in Windows 7.

View 2 Replies View Related

Macro To Autofill With Custom List Based On Value Of Current Cell

May 25, 2012

I have an Excel file for work that has the following:

Column 1 is Agent
Column 2 is Interval (in 15 minute intervals from a report that I pulled)
Column 3 is Agent Calls (has a number anywhere from 1 - the highest currently is 19, which tells me how many calls that Agent had on that 15 minute interval.)

What I have currently is that if it says:

John Doe 15:00 5

It will automatically insert rows after the number based on a # -1, so it will add 4 empty rows after this row.

I have another step that will automatically copy Column 1 and Column 2 to the blank rows directly beneath them until it reaches a cell with data.

Now, what I need is a macro that will take Column C and where it says 5 automatically know to change that to '5a' then proceed to go down the list with 5b, 5c, 5d, & 5e. If it sees 4 it will know to change that to '4a' then proceed to go down the list with 4b, 4c, & 4d. I currently have numbers ranging from 2 - 19.

I have built custom list with this information so if I change all the numbers from 4 to 4a or 19 to 19a and double click on the black box it will autofill exactly like I want but it only goes down to the next cell with data, and I have 100+ lines per agent, and up to 21 agents a day, so this gets time consuming.

Here's how it looks when I'm ready to start the macro:

JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:45 4a

[Code] ......

And I need it to look like this:

JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15 4b
JohnDoe - Doe, John15:15 4c
JohnDoe - Doe, John15:15 4d

[Code] ........

View 7 Replies View Related

Autofill Cell Based On Results Of Multiple Dynamic Dropdown Lists?

Aug 25, 2014

how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.

What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On

Is there a way to do this with the set up I have?

I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory

=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........

And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm

I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list

View 2 Replies View Related

How To Hide Specific Rows Based On Values In A Different Worksheet

Mar 7, 2014

Say I have two worksheets, "Sheet1" and "Sheet2". Let's also say A1 in Sheet1 could have one of four values: cat, dog, rabbit, mouse. Now, based on which value cell A1 on Sheet1 is, I need different rows hidden in Sheet 2. For argument sake let's say if cat is chosen rows 1-5 are hidden, if dog, 6-10 and so on.

View 5 Replies View Related

Count Column W/blanks & Values Based On Specific Date

Nov 25, 2009

I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but itís including all other cells in the column that are not and should not be included in the refreshed tableís data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.

View 3 Replies View Related

Loop Through Sheets And Convert Specific Formula Cells To Values Based On Criteria?

Jan 9, 2011

I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.

for example

I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.

I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.

Book1.xlsx

View 8 Replies View Related

How Can I SUM Values Only When Specific Text In Another Cell In The Row Is Met?

Oct 24, 2008

I'm creating 10 'categories' for Column "C" that I plan on using drop-downs to select from. Column "H" has a value tied to "C". What I want to do, is have Excel SUM all of the values in Column "H" where the Category in "C" is a specific text value (e.g. say a category is "hardware", I want a cell with a formula that sums all of the values in "H" where "C" is "hardware".)

View 8 Replies View Related

Extracting Specific Cell Values

Feb 21, 2008

I have a spread sheet with following:

Name|Address|Home Phone| Cell Phone| Status|Project Name|

I want to select name and extract only home phone and cell phone nos.

Please help me with a code. I have about 2500 entries.

View 14 Replies View Related

Find Values Above And Below Specific Cell

Jul 6, 2009

i'd like formula to find value above and below specific cell

for ex

i have value in cell c5
i want formula helps me to find
values above c5 in cell c4,c3,c2,c1
and
values below c5 in cell c6,c7,c8,c9

View 9 Replies View Related

Remove The Specific Values From Cell

Dec 11, 2009

as-18542-1
sp-25641-1
as-35678-4

u can see in that no last part like this.-1,-2,-4
i need to remove that part.i have lot of no like this.i am going to do this manually it will take long time.pls some body can make macro or something for this.

answe should be like this.
as-18542
sp-25641
as-35678

View 9 Replies View Related

AutoFill Based On Variable Last Row

Jan 20, 2008

What I need to be able to achieve is a function in my macro that will

a: Count the number of rows in the active sheet
b: Allow me to use this info in other functions, such as subtotals, autofills

After searching many sites I'm pretty certain the function I need to use is "rowcount".

I've managed to incorporate a loop using this function, which is fine when running the macro but not so good when you are stepping through 5000+ rows

RowCount = ActiveSheet.UsedRange.Rows.Count
For x = 2 To RowCount
Cells(x, 42).Select
ActiveSheet.Paste
Next x

So for me now it seems as though "x" should represent the number of row that has been counted in the above code. But when I try to use "x" in functions the macro falls over

Range("AQ2:AX2").Select
Selection.AutoFill Destination:=Range("R2C43:RxC50")

View 3 Replies View Related

Auto Increment Specific Cell Value Based On Different But Related Cell Value

Nov 20, 2011

In my excel workbook, I have a customer table and invoice sheet among many others.

As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.

Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.

For example, customer called Bob (Bob-1);
Number of purchases = 1
Customer Type = Single
Discount = Yes

Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:

Number of purchases = 2
Customer Type = Multiple (can be achieved by using IF statement on No. of purchases)
Discount = No

View 1 Replies View Related

VBA Change Cell Value To Specific Letter Based On Value Of Different Cell In Different Worksheet

Jun 6, 2014

I've done the following:

Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub

However, this doesn't even work.

When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...

View 5 Replies View Related

Sum Three Adjacent Cell Values In Specific Row And Then Sum Up All Rows

Mar 12, 2013

I'm looking for a formula to sum three adjacent cell values in a specific row and then sum up all of the rows.

A B C
1 0 2
3 1 2
4 4 0

For example, the value for the above would return 17 = [(1+0+2)+(3+1+2)+(4+4+0)]

View 5 Replies View Related

Extract Specific Unique Values From Row Into Cell

Feb 27, 2014

I've had a look at some examples of extracting unique values but it seems quite complex to me!

I have a list of customer names in column A, column B contains a material code (number format), column C contains the material information sheet (text & number format), column D contains the date that the material information sheet was sent (all column headers are in row 1).

Columns E, F & G follow the same format as above but for a different material code - this is repeated further in Columns H, I & J.

All of the material codes are different, but some material information sheets can be the same for some materials.

In column K I need to identify the unique material information sheet values for each customer, as some are duplicated.

Is this possible? There may be some blank values in the row i.e. a customer does not receive a certain material.

Perhaps just an easier way of organising the spreadsheet!

View 9 Replies View Related

Place Specific Cell Values Into An Array

Aug 23, 2006

I want to get each distinct values from a range (say, c1:c20) and put them into an array for further processing.

View 3 Replies View Related

Autofill Rows Based On Number

Jul 26, 2012

I have wookbook with a lot of sheets on it

The first sheet to be filled is called "Local Info"

In this sheet in the Active Cell "G7" the user must register a number between 0-500

This number must affect my next sheet called Competence Map Autofilling rows 7+8 from Columns A to AM

So If for example I put 83 in "local INFO" G7.

I will get 172 rows with text (the first 6 Headers) the 7-8 original and from 9-172 filled with the information given.

View 3 Replies View Related

Autofill Range Using VBA Based On 2 Variables

Jan 14, 2014

I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:

Code:
Sub SummariseSheets()
'collates individual client development data from consultant
'worksheets and compiles in one sheet on summary page

[Code]....

The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.

View 2 Replies View Related

Finding Sum Of Cells That Have Specific Values In Adjacent Cell?

Apr 4, 2013

I have a list of data:

2
140

1
660

10
140

0.92
660

2
130

0.18
660

4
510

0.44
820

4
510

I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)

View 2 Replies View Related

Highlight Rows Where Only Specific Cell Values Match?

Jul 22, 2014

I'm finding it hard to explain what I am trying to do (which may be why I can't find a solution through Google), so I have an example. I am using a much larger spreadsheet than this sample(18,000 rows)

ID
First_Name
Last_Name
Company

[Code]....

I want to highlight rows if there are multiple instances of ID and # values - I included a column at the end showing which rows in this data set would be highlighted.

View 2 Replies View Related

Only Countr Cell Values That Fall Within A Specific Range

Oct 24, 2007

I need to keep track of tardy occurences, but I only have to consider occurrences that have occurred in the past six months from the current day. Column A contains the names of the employees, Column C thru IV contains daily dates beginning with 1/1/2007. Column B contains a CountIf function to count the number of times T appears in columns c - iv.

View 9 Replies View Related

Generating Values In Number Of Cells Based On Adjacent Cell Values

May 22, 2014

What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username

The following is my code

[Code] ....

Above code runs without errors but does nothing.

View 4 Replies View Related

AutoFill Cells Based On Combobox Selection

Jul 14, 2014

What I'd like to do is fill specific cells with specific information drawn from other pages based on a combo box selection.

For example, if I were to pick 10131 in the combo box, I'd like it to fill out cells X, Y and Z with information from the three cells to the right of 10131. Assume X, Y and Z are not touching.

View 1 Replies View Related

How Do I Autofill Colors, Based On Input Of Text

Jun 16, 2008

i'm trying to do is have a formula that when certain text is typed into column 'b' (fmc, nmcmc, etc... anyways..) when an airplane is 'fmc' its 'green' and i want the aircrafts tail number block in column 'a' to autofill in with a green color, and when its 'nmcmc, i want it to be red. when its 'bq' i want it to autofill in as yellow.

View 10 Replies View Related







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