Need Macro help....Loop Offset Drop-Down etc...

[jagerplz]

OK....total Noob here (apologies). I'm decent at Excel, and fairly new to macros. Here is my problem:

I have a spreadsheet (2 tabs - 1 raw data, 1 for reporting). The reporting tab will be used for reporting the requested info from the raw data in a specified format. Cell B1 on the reporting tab is a drop down selectable box with the numbers 1 through 10 in it. Each of the numbers represents a specific phase that is also assigned to each individual record in the raw data. I need a macro that will allow the user to select a number in B1 of the reporting tab, and return the results that are associated with that phase onto the reporting tab. I know once I have the macro to perform the "if" statement - I can use the offset feature to arrange the data how I would like it to be presented on the reporting tab.

Any help would be greatly appreciated...

 

[Smitty]

Welcome to the Board!

I'd record a macro invoking autofilter based on one of the phases that will leave you with just the records related to that phase. You can then use Goto-->Special-->Visible Cells Only and copy the result to the first sheet.

If that does what you want you can call the code from a Change event and substitute the drop-down value for the hardcoded phase 3 from when you recorded. Here's some boilerplate Change event code:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[dfenton21]

Hi There,

Welcome to the board.

An alternative to using the autofilter is this code.

I don't know the exact layout of your spreadsheet, so for demo purposes, I'm assuming that your phrase numbers and descriptions are stored in range(d1:e10) in your rawdata sheet. Please change as needed.

Sub CopyData()

    Dim iDestRow As Integer
       
    'first row of your Report
    iDestRow = 1
    
    'set phrase text based on B1 number
    For Each cell In Sheets("RawData").Range("D1:D10")
        If cell.Value = Range("B2").Value Then
            sCriteria = cell.Offset(0, 1).Value
        End If
    Next cell

    'loop through raw data and copy data to reporting tab where column B = phrase
    For Each cell In Sheets("RawData").Range("B1:B50")
        If cell.Value = sCriteria Then
            
            'copy this line to add more columns to your report
            'change RawData offset number and Report column letter
            Sheets("Report").Range("A" & iDestRow).Value = cell.Offset(0, -1).Value
            
            iDestRow = iDestRow + 1
        End If
    Next cell

End Sub

As Smitty mentioned above, you can call this from changed event.

Please let me know if you have any questions.