VBA Macro To Take List Of Multiple Inputs / Apply To Another Spreadsheet And Return

Sep 22, 2013

I want to create a macro to take multiple inputs from sheet 2, apply them simultaneously to the inputs on sheet 1, take sheet 1's output and list next to the inputs on sheet 2. I want to apply this to a spreadsheet with complicated calculations. Example spreadsheet attached. Related Macros I have will do a similar thing but only take one input at a time. Code as follows.

Sub x()
Dim r As Range
With Sheet2
For Each r In .Range("A2", .Range("A2").End(xlDown))
Sheet1.Range("A2") = r
r.Offset(, 2).Resize(, 3).Value = Sheet1.Range("C2:E2").Value
Next r
End With
End Sub

View 5 Replies


How To Analyze Trends From Google Spreadsheet Inputs

Jan 15, 2013

I am using google spreadsheet to gather survey data:

The spreadsheet looks like this:

[Code] ....

Is there any way to compile the information and create a line graph to display the trend of A and B over time?

View 4 Replies View Related

Apply Macro To Multiple Worksheets

Oct 11, 2007

I have a workbook in which I have 31 sheets. I've also recorded a macro that works great when I run it on one sheet, but it comes up with an error when I try to run it on grouped worksheets. I searched Google, and a few articles I saw said that in order to run a macro on grouped sheets, you have to use loops. I don't know if this is true, but I don't know how to run loops anyway, so. I want to run the macro on 30 of the 31 sheets. I was going to put the code in, but when I did that my post didn't work, so I'm thinking there might be a limit on the length of a post.

View 5 Replies View Related

Macro To Apply Subtotals To Multiple Sheets

Jul 7, 2006

I am trying to loop through all of my worksheets in my workbook to apply a subotal to each of the sheets. I can get it to work with applying to just one named sheet, but I cannot get the loop to work. The sheets named "data" and "PriceList" do not need the subtotal applied.

Below is the code I am using:

Sub SubTotals()
Dim LastRow As Long
Dim wsDst As Worksheet

View 5 Replies View Related

Multiple Options Cell Values Based On Multiple Criteria Variable Inputs?

Mar 18, 2013

I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.

I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).

The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.

As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.

The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R

You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.

This is the area where my skill at using scripting dictionaries fails.

The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words

if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")

So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)

Here is my code so far.

Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")


View 1 Replies View Related

Programming Multiple Inputs With Multiple Data Fields

Feb 19, 2009

I am trying to set up a "diet log" for my nephew to track his eating habits for a school project. I am trying to make the "total" section of the attached spread sheet auto calculating depending on the information placed in the "breakfast / lunch / dinner / snack" catagories. I have two worksheets in the file. The first being the preferred method of a single cell and seperating the items using only a ",". The second sheet has a little bit different lay out which I don't think will work as nicely.

Honestly, I am not sure if this will even be possible, but I figured I would try here as a final resort. Currently there are just some sample foods in there as I will let him fill in the actual workbook for his use.

View 7 Replies View Related

Userform - Multiple List Box And Display In Cell Of Spreadsheet?

Jul 14, 2013

I have designed a multiple list box on my userform and display it on one of the cells in the spreadsheet but with each list item separated by a comma when its display on excel.

The problem with my code is the delimiter appears at the first list item, but it should only appear if more than one list item is chosen.


I was thinking if there is something like if gItem = 0 then only display sFruits Else display what I have below???

For gItem = 0 To FruitsList.ListCount - 1
If FruitsList.Selected(gItem) = True Then
sFruits = sFruits & delimiter4 & FruitsList.List(gItem)
End If
.Cells(gRow, "AO").Value = sFruits

View 4 Replies View Related

Macro To Send Multiple Emails To Multiple Recipients With Standard Message As Per Spreadsheet Table

Feb 18, 2014

get the code for sending multiple emails as per the spreadsheet list. Assume the spreadsheet has 100 line items and each columns specifies the name of the person, value, recipient email address ("To" and "CC") and sender name.

And the Body of text is :

Hi "Name of the person" Please find the value of "Value" to be paid for the moth of xxx and kindly let me know for further clarification.

View 1 Replies View Related

Excluding Text Inputs For Attendance List

Jul 29, 2014

I'm making an attendance list that calculates regular hour and overtime hours for days in a month on rows. Besides the number of hours, there's input for v and s, which stand for vacations and sick days. I'm using the "if" function to separate hour and overtime, and when I put v or s in a cell it messes up my totals. Is there a way to make it so that the cells with v or s don't affect my other columns?

View 4 Replies View Related

Lookups With Multiple Inputs

Aug 19, 2009

I have a spreadsheet with a medium-sized data set, and I need to look up values from the large table, and thin the table down to only the relevant values.

To arrive at each unique value, I will have to match 3 input cells to the first 3 cells of the data table, then copy values from across that specific line to the appropriate cells of my smaller table. I really don't need to sum or do any math in this step, its just a copy from the master table to the smaller table, the math will come later.

I think I'm attaching the file (remember-first post) so hopefully it will help with what I'm trying to accomplish. Hopefully this isn't a big deal, but I've been pulling my hair out on this all day and I just can't find a solution anywhere that really matches up to this.

View 6 Replies View Related

Multiple Inputs Into Other Worksheets

Dec 26, 2006

I have a spreadsheet with over 70 worksheets. The majority of these are concerned with Work standards (National Occupational Standards), each of these standards are further broken down into performance criteria.

What I would like to happen is for a person to be able to enter a "Yes" or "No" response in a box to a specific question on an assessment report or a witness testimony worksheet. If a Yes is entered I would like the spreadsheet to automatically enter a reference i.e AR1 or WT2 (stated by the inputter on that worksheet) in each of the other seperate worksheets that cover this perfromance criteria with this reference.

Because many of the worksheets contain similar performance criteria, i.e "treating people fairly and with respect". The answer of "Yes" on the assessment report worksheet may require a reference inputting on anything up to 60 different worksheets. Is this possible or am I dreaming of the impossible?

I really should have asked this question before I wrote out all the seperate worksheets.

View 14 Replies View Related

VBA Code For InputBox With Multiple Inputs

Apr 7, 2014

I am trying to create a vba using only one macro button (excel). I would like for them to enter their names and have their own password that will then insert their signature(pic.jpg) in a specific cell.

I created a certificate in excel and I don't want 12 (number of people) buttons visible. Id rather have a long code. Im not sure if using If and Then and ElseIf is the way to go.

View 8 Replies View Related

Search With Multiple Inputs/Outputs

Jun 2, 2008

I am looking to create an excel search with two input and three output values on Sheet 1 that will link to a table on Sheet 2. To help illustrate:

Sheet 1:

The two white cells indicate the two input values, and the three blue cells indicate the three desired output values.

Sheet 2:

I want to enter the two input values (Length and Width), press the "Search" button, and have the corresponding output values (DWG#, Item #, and Tooling #) from Sheet 2 appear in the three output cells on Sheet 1.

View 9 Replies View Related

Lookup Value In List And Return Multiple Corresponding?

Apr 14, 2014

How to write formula of following condition:

Input date on left column and the cell on right will automatically look up this date from the list located in other spreadsheet and return multiple corresponding in sequence order? Once another date is entered on left column, it will do the same function again on right column?

View 5 Replies View Related

User Inputs Area Into Macro And Macro Continues To Run After Input

Oct 9, 2012

I have recorded the following marco and it works fine. I would like to modify it so the user would highlight a range of cells rather than have the fixed area :


What code lines would need to be added?

Application.Goto Reference:="R4C5"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Array("'ConsExecProp@20120920'!R4C5:R113C21"), Version:=xlPivotTableVersion12 _


View 4 Replies View Related

Expense Tracker By Month For Multiple Inputs Then Add Together?

May 14, 2014

I am looking to take the data off of a "detail" sheet and put it to a summary page. I want the summary page to find the capital and expense from "details" sheet by the month on the "details" sheet. Then for every month add all the expenses and capital and put as 2 values per month, Capital and expense, on the summary page. I am not really sure where to begin but have added my excel workbook that I have started.

View 2 Replies View Related

Conditional Formatting Based On Multiple Inputs?

Feb 19, 2013

I'm working on a fairly complex spreadsheet where I want to pull conditional formatting into an entire row. I have a column in tab1 where I have a bunch of beer styles listed. On tab2, I have a 2 columns: a complete list of styles with a 'yes' or 'no' in the next column based on certain criteria. What I'd like to do is have the column in tab1 highlight if that style in tab2 has a 'no' in the column next to it.

View 3 Replies View Related

VBA Functions - Multiple Inputs And Keeping Track Of Them

Sep 17, 2013

I have several formulas that I use over and over, so I'm creating VBA functions that I can call from a worksheet.

But...when I have multiple inputs going into a function, is there a way to remind the user which input comes next in the same way that Excel does with built-in functions?

For example, when you type in =PMT( Excel brings up the helper text so you know that the order of inputs is (rate, nper, pv, etc....

Is there a way to bring up that same helper text in my own function? So if I have up, down, right, left as inputs and give the spreadsheet to someone else, they can see it's up, down, right left and not right, left, up, down .

View 4 Replies View Related

How To Look Up Value In List And Return Multiple Corresponding Values Totaled

Dec 9, 2013

I have a file with multiple sales for each product, each month it can change depending on sales and i want to get the total of each product. As the range is not defined this is where i am stuck. I want the total for each product based on the product name or cell reference.

Logic: Look through range and sum total each product equal to "A2" for example.

View 6 Replies View Related

Return List Based On Multiple Criteria

Mar 13, 2014

I have a spreadsheet with several hundred line items that I basically want a summary of.

string1 criteria1 other2 <---disregard because of other2
string1 criteria2 other1
string1 criteria2 other1 <---disregard because it is a duplicate
string2 criteria3 other1
string2 criteria3 other1 <---disregard because it is a duplicate
string2 criteria2 other2 <---disregard because of other2
string3 criteria1 other1
string3 criteria1 other1 <---disregard because it is a duplicate

I need the formula to return the value in STRINGS & CRITERIA, but I do not want it listing duplicates.

-I need the summary list on a separate sheet
-STRINGS, CRITERIA, and OTHER are all strings
-STRINGS are in alphabetic order
-CRITERIA are listed in groups with regard to their STRING, but not necessarily alpha-order
-OTHER are not in any particular order, but there are only two choices for OTHER
-OTHER: if OTHER=other2, it should not be listed in the results


string1 criteria2
string2 criteria3
string3 criteria1

View 1 Replies View Related

Graph That Consolidates Multiple Inputs From Dropdown Form

Apr 22, 2014

1. I need a template that allows a user to select a location from a dropdown menu (Cell B1 of Example Data tab).
2. Titles in cells C1:V1 and B2:B8 will stay the same.
3. Data inputs within range C2:V8 would change for each location. The idea is based off the location selection from the dropdown menu, different data is shown within the range and available for updates.
4. The chart (see Example Chart) would sum all location inputs in range C2:V8

View 3 Replies View Related

Return Multiple Values From A List That Fit Logical Test?

Nov 15, 2013

I need a formula that will return a range of values from a list.

Example: I need a formula in cell A2 that looks at the list and will return all numbers that are >99 but 199 but299 but

View 6 Replies View Related

Lookup To Return Multiple Values And List Sequentially

Mar 17, 2008

I need to lookup data based on a cells content. The answer has to output to a table as a sequential list. I have attached a sample workbook (there will be 100+ sheets in the final version!). The Register sheet lists competencies against page# and title. For each Evidence Sheet I need to populate the Competency grid (C4:D10) in a sequential manner i.e. no blank lines,and the Title (B13) based on the Page # (D2). I have looked at a great number of postings, but cannot see a solution that fits.

View 7 Replies View Related

Moving Rows Into Multiple Spreadsheet Using Macro

Jan 20, 2008

Is it possible to move rows of data in a spreadsheet to multiple spreadsheet accordingly?
I had lists of tasks in a single spreadsheet and i need to segregate the tasks for all my staff in serial while no duplication among all of them. For instance, i got 4 personals in my department and i need the 1st 4 tasks to be distribute to each of them and next 4 tasks accordingly. This is due to all tasks are equip with due date and i need to calculate how much time i need to accomplishing them. i'm used to manually move it and found it time consuming, so i was wondering if someone would instruct me where or how to achieve it by using a simple macro.

View 9 Replies View Related

Add New Data To Existing Cell Based On Multiple Selective Inputs?

Apr 1, 2009

I have a spreadsheet of courses required to reach a certification. On this spreadsheet I have listed the number of hours required for each course in one column, and how many hours I have accrued in an adjoining column. Not all the hours will occur at once, so I tend to bound from cell to cell adding hours in small amounts. What I am trying to do is create a macro that will allow me to add to the existing number of hours to the newly accrued hours, without typing over what is already there.

For example…Class 1 requires five hours total, and I have two hours accrued. If I accrue two more hours (for a total of four hours) I want to update cell E2 without going in to this cell manually and changing this number. I would like to enter the additional two hours in a text box or similar function, and have that function update E2. To add to the level of difficulty, there are four levels of class. This means not only do I need to be able to select which class hours need updated, but which level of class. I have attached the spreadsheet I am working with to try to make things a little clearer.

View 2 Replies View Related

Filldown Macro For 2 Sets Of Inputs

Oct 8, 2008

I am currently using this macro to fill down cells full of numbers which have been passed through formulae. It works for one input (C10), however I want it to work for another input (C11). Im trying to make it fill down the cells after a new set of headings identical to row 12. It has to take into account the rows used for the original fill down which relies on C10. I have tried to use the same macro twice, but I cant get it to do both layers. Basically I need a fill down of layer 1 (already done), and then a fill down for layer 2 after layer 1.

View 2 Replies View Related

Macro Prompts And User Inputs

Jul 20, 2009

I am not new to excel - but I have never required the use of advanced excel features before today - so I apologize in advance. I am a health professional student (graduate level) assisting my preceptor in developing patient management mechanisms.

I am building an excel spreadsheet to manage current medications for several patients.

I have built the spreadsheet with the first 'sheet' being a directory and subsequent sheets being patient data files(one sheet per patient).

I am now in the process of creating a macro to build new patient data files (a new sheet for a new patient). I recorded a macro to build the new sheet with the proper formatting of information. What I am trying to do now is automate the naming and linking of the new sheet.

What I am really after is how can you create a "prompt" such that after activating the macro button it asks:

Patient last name:
Patient first name:

Then uses this data to:

1. Rename the newly made sheet to 'last, first'
2. Title the newly made sheet with 'last, first'
3. Create an entry in the directory in the 'last, first' format
4. Finally, build the hyperlink from the directory to the newly created sheet

View 11 Replies View Related

Macro To Create Multiple Tabs With Same Spreadsheet On And Date Them

Jul 20, 2014

I am trying to create a workload spread sheet for work I need to have the same looking spread sheet 365 times but also need each sheet to be dated, EG (tue,01/04/2014 through to tue,31/03/2015) I can create 365 tabs that have the same spread sheet on and I can create dates but not do both at the same time, it isn't fun doing copy and paste 365 time.

View 1 Replies View Related

How To Calculate Sales Based On Partial Cell Content (multiple Inputs)

Jan 15, 2010

I have made a dynamic calculator which will calculate the total sale based on 3 criteria in 3 different cells as follows:-

I would like to derive the sales qty in cell C5 if all the 3 conditions in the range B2:B4 is true.

In this case the sales qty for Style-Colour-MRP combination(9103-PINK-399) should be 6.

I am unable to figure out with the formula for the same....

View 9 Replies View Related

If Macro Picking Up 5 Inputs Changing To 3 Outputs

Sep 11, 2012

I'm trying to take 5 inputs that are all in text format and inputs have fixed options what i need is a macro that will take them and turn them in to the relevant outputs. there is a select amount of combinations and i've been asked to do each combination as a separate macro or sub macro.

What I have so far is;

If C6 = "Panel plate" And D6 = "24 by 21" And E6 = "<1.59" And F6 = "N/A " Then I6 = "120" And j6 = "4" And L6 = "120"
End Sub

6 Panel plate 24 by 21 <1.59 N/A

(Doesn't seem to work)...

If those four cell match up then i need I6-L6 to line up with whats required.
If they don't equal that i need it to stay clear.

I also need to add the same macro with different options.

View 7 Replies View Related

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