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]

I did something along these lines recently, here is some of the code I used:

col = InputBox("Please Input The Column Your Plant Code Is In (As a Letter)")

Columns(col).Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

I then filled in that column with a header and a formula. Seems like you should be able to do pretty much the same, but I think you are going to have to enter the data location instead of click on it, like you really want to do. Hope this helps.

 

[zookeepertx]

Okay, that's working. Thanks!!

Can you help me with the proper syntax to refer to the data source sheet? I'm trying to just select a few cells on that sheet & bold them to see if I'm even on the right track, but can't seem to get it to recognize that sheet. Or something like that . . .

Right now I have both practice sheets in the same workbook, just to make it a little easier. When the Input Box asking for the source sheet comes up, I've tried typing Acct Activity or 'Acct Activity' in conjunction with various versions of this:

 ('data'!A1:B2).Select
    Selection.Font.Bold = True

 (data!A1:B2).Select
    Selection.Font.Bold = True

 Range(data!A1:B2).Select
    Selection.Font.Bold = True

but am just getting various error messages.

Ideas?

Jenny

 

[Marcelo Branco]

Maybe

Sheets("Data").Range("A1:B2").Font.Bold = True

 

[zookeepertx]

YES! Perfect! Thank you very much!

Jenny

 

[Marcelo Branco]

YES! Perfect! Thank you very much!

Jenny

You are welcome and tks for the feedback

M.

 

[zookeepertx]

I should probably mention that I changed the second reference from "source" to "data"; that's why the original post doesn't exactly match the syntax of the coding in the later post.

Jenny

 

[zookeepertx]

I'm thinking about this from a little different angle right now. Lets see if you all think this is possible:

1) I first want to find all instances of the value in research sheet A1 that occur in the A column of the data sheet.

2) Out of only THOSE rows, find an instance of the value in research sheet B1 that occurs in the B column of the data sheet.

2a) If there's no match, then do nothing
2b) If there is a match, then fill in research sheet (col)1 with data found in data sheet C on the row found
​

3) Continue through to the end of the research sheet.

Anyone able to see how to word this in a macro? (Maybe some form of a VLOOKUP?)

Thanks!

Jenny

 

[buxtongt]

It might be a little on the slow side, depending on how much data you have, but it sounds like filters would do what you wanted. Set a particular cell to a variable name, then sort by that variable in the other sheet. Do this for each column until you have what you want displayed. Then you could select the entire range and copy only visible rows (f5, special) and paste them into your other sheet. If there was nothing displayed it would just copy over a blank row. Hope this helps.

 

[zookeepertx]

Hmmm, I've never actually done anything with filters; can you explain a bit more?
Thanks!

Jenny