Using Data From Input Box In Array Formula In Macro

Nov 10, 2011

I often have to research long sheets of data, which requires filling data in on my research sheet from several different sources. The research sheet has a file # in A and a vendor # in C. The data sources have the file #s & vendor #s in varying columns, but I use a pivot table to end up with file # in A, vendor # in B and the needed data in C.

The length of the research sheet varies from month to month - often 800-900 rows, so you can imagine how tedious it is to find the correct file #/vendor # combination to plug the data in. (Many of the rows on the research sheet will not have a row on the data source sheet.)

I managed to get an array formula in one of the columns on the research sheet that actually DOES work:

Code:

{=INDEX('Acct Activity'!C$1:C$7,MATCH(A7&C7,'Acct Activity'!A$1:A$7&'Acct Activity'!B$1:B$7,0))}

But the problem is that it returns #N/A in any row that doesn't have any data. This messes up the sum formula in the farthest right column. So, I tried to incorporate an ISERROR in with the formula, but couldn't get it to work; it left all the cells blank.

In the meantime, I got to thinking that, since there are up to 3 columns requiring data on the research sheet & each column's data comes from a different source, wouldn't it be great to have a macro where I could use a couple of Input Boxes: 1 that would let me click on the column where the data NEEDS to go & 1 that would let me tell it (either by typing the worksheet name or by clicking on the worksheet " PREFERRED " where the data come from)!

So, I spent the entire day yesterday trying to make any of that work & can NOT figure it out! I got the macro to pop up the first input box, & can key in the column letter, but clicking OK doesn't do anything. Yesterday, I had it so I could click OK & the box would go away, but nothing else happened & the second box wouldn't work right.

THEN, I started trying to figure out how to do an array formula in a macro & my head nearly exploded! I have a terrible time trying to understand written descriptions if they're in "tech speak", so it was all greek to me.

Here is all the farther I got with the macro:

Code:

Sub ClearingRsch()
' Jenny 10092011
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False

[Code] ......

View 9 Replies


ADVERTISEMENT

Macro To Input A Formula

Mar 26, 2009

i need a macro to insert the formula i have in Column M row 3, and insert it all the way down to the last letter that is contained in column k. So in this example The Last Letter in Column K is AQ, ( i dont physically mean last i mean last in alphabetical order in excel , such as ( W X Y Z, AB, AC , AD ) AD would be last.

So since AQ is last it would insert the formula 43 times, which is AQ. THe AFter tab should clear any confusion up because this is how it looks finished. I use various sheets like this so the last letter changes, so having this macro be dynamic and not attached to a sheet name would be ideal.

View 2 Replies View Related

Macro For Input Box To Replace Formula Cells With 0?

Jul 22, 2014

Sample Data from "Demand Planning Prem" worksheet

Date
SKU
Name
Begin FG's

[Code]....

This worksheet has around 40,000 records, and I need to create a Macro that will ask the user for a date, and when the user enters a date the macro goes through column A and finds all of the matching dates and then replaces the numbers (along with the formulas) in the corresponding Add Returns cells (column E) with 0's.

I have only been able to create an inputbox, but I am completely lost regarding the criteria due to being very new to VBA.

Sub ClearData()
Dim EnterDate As Date
Dim msg As String

[Code].....

View 2 Replies View Related

Can A Macro Write A Formula As An Array Formula

Jun 7, 2006

I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?

View 2 Replies View Related

Input Data Macro

Apr 18, 2009

I am looking for a macro which will help make my workbook more user friendly.

Is it possible to have a macro which when run brings up dialog boxes for user input into certain sheets etc.?

View 14 Replies View Related

Run Macro After Cell Data Input

Jan 24, 2012

I want to run a macro that unhides 15 lines (line 20 to 35) when data is inputed on cell 'F6' and to run another macro called "search" (i have already finished this macro) which will fill in information on the unhidden lines from above.

View 4 Replies View Related

Macro To Enter Data Without Using Input Box

Aug 25, 2012

I need a simple macro to enter data for hours worked, but don't like the inputbox.

I need to enter the Emp Nbr in B1.
AM in Time in B3
AM out Time in B4
PM in Time in B7
PM out Time in B8

I then write down the hours worked and

Return to B1

When I enter an new number, I want it to put 0 in B3,B4,B7 and B8

(see attached) Oops can't post. There is probably a better way to calculate the sub-totals, but this works except on an enter of 1200

View 5 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

Convert Array Formula To Macro

Oct 31, 2006

The attached has a mega array formula that really slows down the recalc on this spreadsheet. I would like to use a macro to write the value of the formula to the worksheet "Database" column J.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range(Range("rReason").Offset(1, 0), Range("rReason"). _
Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then
Target.Offset(0, 1) = Application.VLookup(Target, ValList.Range("ReasonLkUp"), 2, False)
'This formula below needs a VBA equivalent
' Target.Offset(0, 4).FormulaArray = "=IF(MIN(IF(R7C3:R35000C3=RC[-7],IF(R7C8:R35000C8>0,R7C8:R35000C8)))=RC[-2],MIN(IF(R7C3:R35000C3=RC[-7],IF(R7C8:R35000C8>0,R7C8:R35000C8))),0)"
End If

If Not Intersect(Target, Range(Range("rSurname").Offset(1, 0), Range("rSurname"). _
Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then
Target.Offset(0, 1) = ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1)
End If
End Sub

View 9 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

How To Leave Cell Empty When There Is No Data Input After A Formula

Jul 28, 2014

When i use

Code:
=IF(ISBLANK(I31);"";I31-IF(ISNUMBER(J31);J31;0))
The cell gives me an error, i think because I31 has a formula that returns no value.

How can i stop the formula if cell I31 gives me no data??

View 5 Replies View Related

Highlighting Changes In Cell After Data Input Macro Run

Jun 27, 2013

I work for a marketing agency where technical savvy-ness is really limited. We analyze a lot of web page elements. I have a program that exports this data into a csv file and I've created a template for the user to add this sheet. This data is primarily text. I've created a button on another sheet that runs a macro that extracts data from the first sheet onto the second sheet in a particular format for ease of reading and editing it. That part is all good, this macro works and the button works. Now I'm trying to have it such when a person edits one of these cells, it highlights the changes on this newly sheet using VBA (the highlighting changes function just turned out to be too messy and buggy for what I want). I have this basic change macro that I have on the sheet currently:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H500"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = 30
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This works as well. However, my problem is that when I run the data button macro, of course excel reads this as a change to the cell and highlights it as a change before any actual changes are made. How can I have it so I run my data macro, and the cell changes are highlighted only *after* the user goes in to edit the text data plugged in from the data macro?

View 3 Replies View Related

Determining Formula To Choose And How To Input The Data In Correct Fields

Aug 8, 2014

I am trying to save some time converting a cut up inventory catalog number. Specifically focusing on the color code. I have the list of colors associated with their specific part number. The order cannot change or my sheet will mismatch.

What I want to do is convert the # in column A to the color code text in column b so that it matches the translated code in column C? For example

1 I want to tell 1 to = Polished Chrome anytime #1 appears in my column.

What formula should I choose to achieve that goal. As well, how do I know what data to imput into the formula wizard?

Sheet is attached.

I have Cut a few thousand items off the bottom of the sheet. I can apply to additional data left off.

View 4 Replies View Related

Macro To Search Row And Column For Intersect And Then Input Data To Where They Meet

Jul 5, 2014

In my "cover" sheet I click the allocate cover button and it then generates a list of data below the first table. This generated data now needs to update the "timetable" sheet by using the "allocated cover teacher" and "period" cells as a reference to then input the class code into the timetable at this time.

cover.jpg

So in this picture we see that Dorothy*Schultz (in cell E27) has been given a class 3 MABA RM 15(cell D27) on TuesdayP1 (C27). I want search for Dorothy*Schultz and TuesdayP1 in the "timetable" tab to input the name of the class which in this instance would be input to cell I20 in the "timetable tab".

VBA COVER Forum Version 1.xls

View 10 Replies View Related

Delete Column - Macro Using Data Validation User Input Value?

Nov 7, 2012

I would like to delete a column using data validation list. for example :

i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.

And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.

View 1 Replies View Related

How To Filter Data By Array Formula

Dec 23, 2013

My Sheet1! looks like (image attached)

On Sheet2! i want to import all rows belongs to Nov-13. Similarly on Sheet3! all rows belongs to Dec-13 ... How to do such dynamic filter using array formula?? I need array as my data source is a form & new rows will be added everyday. I want to do that by formula not using spreadsheets 'Filter" option.

Expected result will looks like below (All Nov-13 rows will return)

View 3 Replies View Related

Data Binning And Array Formula

Apr 5, 2007

see attachment. I would like to calculate the average of the values whose time values fall in between the lower and upper time limits. Or let me rephrase that: If a given amount of time vector entries falls in between the lower limit time vector (at row x) and the upper limit time vector, then I would like to calculate the average of the corresponding value entries. (oh, not only the average but also the standard deviation)

Searching the forum I found a similar entry, but unfortunately Andy Pope's solution attachement seems to be missing: [Solved] Formulas: Counting within bin sizes. I have tried to use the same approach (see attachment) but only came up with counting the frequency instead of calculating the average.

View 4 Replies View Related

Using An Array As Input To A Custom Function

Apr 22, 2009

Does anyone know why you can't specify an array, like an array of Doubles, as an argument or input to a custom function? For example:

View 14 Replies View Related

Input/output Values Into/from An Array

Nov 6, 2009

I have a long list of values, a few thousand lines, that I need to input into an array so I can run a formula on the values in the array and then take those results and put them into another array which I can then call to output down a different column. Is there a faster way to accomplish this than the typical For Loop with the activecell.value and activecell.offset commands, that just takes forever.

View 15 Replies View Related

Use An Array From One Procedure As Input For Second Precedure

Feb 28, 2010

There's probably a post for this, I just can't find it.

I need help "reading" an array from a previously used procedure. The second procedure is dependent on the values from the first, however, I can't seem to "import/read" the array from my first procedure.

I know the code is executing correctly in the first procedure, i can output it to a worksheet. The output data isn't of any particular interest, it would be "taking up space" on my worksheet, but the values in the array are important for latter caclulations. I would prefer the array be "hidden".

Ex.

public delta( 0 to 360) as double

sub first()

code...
delta(theta) = x0 - x
worksheets("output").cells(theta, 1).value = delta(theta) 'Just to make sure the code is working

end sub

sub second()

?What code goes here to read the previous array delta()

end sub

View 9 Replies View Related

One Array Formula To Sum Product With Weighting Data

Jun 11, 2014

My problem is a little more complex than sumproduct.

I have a ratings column and exposure column. I want to weight those ratings before doing a sumproduct, at the same time ignoring #value.

In the attached file, I want to achieve the green cell in one go, based on the orange raw data.

Attached File : Weight Data.xlsx‎

View 2 Replies View Related

Array Formula: Add Data Which Meets Certain Conditions

Jan 1, 2009

I'm working with wookbooks used company wide and I cannot add any helper columns which would solve the problem. I need to add data which meets certain conditions see attached workbook for a sample.

View 5 Replies View Related

Adjust Macro - Imports Data From Several Input Files And Rearranges It In A Master File?

Apr 25, 2014

I have the following macro that imports data from several input files and rearranges it in a master file.I want to change it so that I can use it in each of the input files. Therefore, it should look in the input file for Spreadsheet "XYZ" and rearrange it in Spreadsheet "Data".

[Code].....

View 9 Replies View Related

How To Use VBA Array To Combine User Input Form

Jun 19, 2013

I have a form created using the instructions here [URL] ..... however I only have two boxes that I need completing. What I need to do is combine both inputs from the form into one cell. The code below shows this working but I am unable to get the array to work.

Essentially I need to get the array to drop in the old number and new number into the new cell so it looks something like this:

Old Number - 1234
New Number - 6543
Value in cell 1234 6543

The code I am using is set out below:

VB:

Private Sub submitmeterswap_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim myarray As Variant
Set ws = Worksheets("x")

[Code]....

View 4 Replies View Related

Array To Check User Input Match

Feb 22, 2007

This is what i want to do: SEE ATTACHEMENT

•Write codes to pop up an InputBox to ask the user for a customer name.

•The program uses the user’s input to check whether the name is on the list. If it is, display an msgbox saying that the customer name is on the list, and the corresponding cell will be indicated in boldface and in blue. Otherwise, an msgbox will be displayed saying that the customer name is not on the list.

Dim the customer list as an array (string var. type) and Dim Found as Boolean. You will need the If-Then construction and For-Next or Do-While/Until loop too). Create a button to run and another to restore the formatting to its original style.

This is what i did so far and still having problems with it:

Option Explicit

Sub customers()

Dim Arr()
Dim R As Integer
Dim C As Integer
Dim ReturnColumn As Boolean

View 6 Replies View Related

Data Validation Cell Not Recognized In Array Formula

Nov 14, 2013

I am trying to get my array formula to recognize the text selected in my data validation cell. The formula worked perfectly until I decided I wanted to try and use a data validation list to eliminate spelling errors. Even though the text I am trying to recognize in the cell is exactly the same, because it is in a data validation format it no longer works. Is there a way I can modify my formula so it works? Example below...

formula: {=SUM((A2:A109>DATE(2013,10,31))*(H2:H108="Joe Blow"))}

A2: 11/2/2013
H2: Joe Blow (chosen from a data validationi list)

The result of the formula should equal 1 in this example, or more if the same criteria occurs more than once within the range of A2:A109, and H2:H109.

View 7 Replies View Related

Find Value In Array Based On Partial Input From User

Feb 22, 2013

I am trying to find a value in an array based on partial input from a user.

The user will enter the last four digits of a number.

Then the formula should find the number in a list and return that complete number

I will explain using example.

Here is the list of numbers (array):

3003280197
3003283233
3003625456

User input= 3233

formula output = 3003283233

View 3 Replies View Related

Array Formula: How To Aggegrate Data, Removing Blank Cells

Aug 29, 2007

Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount.

ie:
------------
column1
------------
(blank)
1/1/2007
(blank)
(blank)
(blank)
2/5/2007
(blank)
(blank)
3/7/2007
(blank)
... etc ...

I would like some type of array formula, that could
agegrate that whole column into a new column, removing the blank cells.

so the new column would be:

--------
Column2
--------
1/1/2007
2/5/2007
3/7/2007

I was thinking something like... but this doesnt get rid of the blanks...
(this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.

=ARRAYFORMULA(if(isnumber(A9:A17),A9:A17,0))

View 10 Replies View Related

Wrong Data Type Error In Simple Array Formula

Jun 30, 2013

I am trying to use FIND and an array formula to find the position of text in a range of cells (A2 and A3 in the example) which could be one of a number of options (C1:D1 here). But the array formula throws up the following error: "A value used in the formula is of the wrong data type". The simplest illustration of the problem is as follows. The formula in B2 is

Code:
{=FIND(($C$1:$D$1),A2)}
and $C$1:$D$1 contain REF and ATM respectively. [/CODE]
REF
ATM
203047 05AUG 08.55 OKEHAMPTON ATM
#VALUE!
CO-OP GROUP 380611 REF 191 7553375222 BCC
22

We see that B2 has a #VALUE! error - wrong data type. But for some reason B3 is ok returning 22!

View 9 Replies View Related

Create A Dynamic Array So That When A User Enters The First Sheet Name Via An Input Box

Nov 20, 2008

I'm trying to create a dynamic array so that when a user enters the first sheet name via an input box, excel will know to select the other sheets with ending (2), (3), (4) etc.

Currently this code works only if there are 4 sheets present (as I set this way), anything lower/higher is out of range.

How do I make it so that it will select sheets if there are less than 4 sheets present?

I tried nesting, but I pretty sure this array needs redimming or something. Is ubound a better option?

View 5 Replies View Related







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