Using data from Input Box in an Array Formula in a macro.

[zookeepertx]

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.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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.)
<o></o>
I managed to get an array formula in one of the columns on the research sheet that actually DOES work:

{=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.
<o></o>
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)!
<o></o>
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.
<o></o>
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.
<o></o>
Here is all the farther I got with the macro:

Sub ClearingRsch()<o:p></o:p>
' Jenny 10092011<o:p></o:p>
    With Application<o:p></o:p>
        .ScreenUpdating = False<o:p></o:p>
        .EnableEvents = False<o:p></o:p>
        .Calculation = xlCalculationManual<o:p></o:p>
        .DisplayAlerts = False<o:p></o:p>
    End With<o:p></o:p>
<o:p></o:p>
    Dim col As Range<o:p></o:p>
    Dim source As Range<o:p></o:p>
    <o:p></o:p>
    Set col = Application.InputBox(Prompt:="Click on column header", _<o:p></o:p>
    Title:="Specify Column", Type:=8)<o:p></o:p>
    Set source = Application.InputBox(Prompt:="Click on data source sheet", _<o:p></o:p>
    Title:="Specify Source", Type:=8)<o:p></o:p>
    <o:p></o:p>
    <o:p></o:p>
    With Application<o:p></o:p>
        .ScreenUpdating = True<o:p></o:p>
        .EnableEvents = True<o:p></o:p>
        .Calculation = xlCalculationAutomatic<o:p></o:p>
        .DisplayAlerts = True<o:p></o:p>
    End With<o:p></o:p>
<o:p></o:p>
End Sub

<o> </o>
Can anyone help me with this?
<o></o>
Thanks!
<o></o>
Jenny

 

[buxtongt]

Highlight your entire range and select sort and filter>filter (in excel 2010/2007). Then you can click on the arrow that should be showing at the header of each column and select only the value(s) you want to see. From what you are saying you could do this for your specific value in column A then do the same for your value of column B and be left with only the data that matches both criteria. Hope this helps.

 

[zookeepertx]

Oh! Well THAT'S neat! I'll have to remember that! Unfortunately, with the length of the sheets, I'm not sure it's feasible. I can't seem to select more than one value at a time in a column; I'm on Excel 2003, so that's probably why. The research sheet is usually 800-1,000 rows & the data sheet is usually 100 or more. So, filtering one at a time probably wouldn't save any time over the manual method I'm having to use now.

But that filter is great; I'm sure I'll make good use of it! Thanks!

Jenny

 

[buxtongt]

If I remember correctly in 2003 you can't select a list, but you should be able to do ranges, upper and lower limits, etc, etc. You could also use a macro to pull one value at a time from a list if you wanted to try to automate the process some.

 

[zookeepertx]

Oooh, even cooler! Using "Custom" on the drop down arrow at the top of a column lets me choose a range or filter for 2 values at once. I just KNOW I'm gonna need that. Thanks again!

As far as the research/data thing:
So far, I've been thinking about a formula or macro to run on the RESEARCH sheet to find the File/Vendor on the data sheet. It occurs to me that it might be easier to start from the data sheet & find the File/Vendor on the research sheet and then plug the data in the correct column.
I wonder if a VLOOKUP could do that? I'd still need to find some way to tell the macro which sheet to look at & which column the data needs to go into.
I've been wrestling with this for 2 solid days & am getting really tired of fighting it & not making any actual progress. I feel like I should have seen the light by now!

Jenny