having macro move down to next row

[menbanx88]

Hey all!
I am a macro/vba noob in these parts. Some of my work colleagues have been trying to figure out a macro that will allow us to move down to the next row and capture the data successfully. Right now, we are manual changing the range/criteria1 row by row. How could we configure this to automatically go to the next row of data?

Edit: I attached the workbook. the macro tab, in H3 is our wanted output. However as you can see in the MacroEquation tab and the actual macro, we are manually changing the cirteria1 from sheet CMS NY 2016 column A. How could we modify this marco to reproduce our output in h3 for all of the remaining rows? instead of hacing to change for each row/criteria?

Windows("FY16CMSMetrics.xlsx").Activate
Range("A2:A2261").Select
Range("A2261").Activate
ActiveCell.FormulaR1C1 = "360084"
Windows("FY16MedicareImpactMetricsMACRO.xlsm").Activate
ActiveSheet.Range("$A$1:$Z$3448").AutoFilter Field:=1, Criteria1:="360084"
Range("C2260:X2260").Select
Selection.Copy
Sheets("Macro Equation").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G3").Select
End Sub

[jolivanes]

Please re-read the forum rules. In this case rule #3.

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

To add a file to a post

[menbanx88]

Thank you for the tip! I have updated my post. Hopefully I have everything this time. Thank you for your help!

[davesexcel]

Thank you for the tip! I have updated my post. Hopefully I have everything this time. Thank you for your help!

Could you also explain what is supposed to happen

[menbanx88]

ah yes, sorry. Let me attach the instructions. we copy and paste the data(c-x columns from cms cn fy 16, by row, not total sheet) into the marcoequation tab with column b6-b27. we then copy and paste column D concatenate function into column E, which should spit out the needed concatenated values in Column G. Then we copy and paste column G3 into H3 which gets are output needed. Thanks

[davesexcel]

Since this is a homework assignment, you will have to be more specific with your questions.

Your original question

How could we configure this to automatically go to the next row of data?

In most cases, activecell.offset(1).select will select the cell 1 row down.

You may want to consider looking into loops as well if you are repeating an operation.
Run this loop example and look at sheet, "HAC FACTOR" to see what has happened.

Sub LoopExample()
    Dim sh As Worksheet
    Dim LsTrW As Long
    Dim rNg As Range
    Dim c As Range

    Set sh = Sheets("HAC FACTOR")
    With sh
        LsTrW = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rNg = .Range("A2:A" & LsTrW)
    End With

    For Each c In rNg.Cells
        c.Offset(0, 6) = "Hello " & c
    Next c

End Sub

[menbanx88]

ok the looped worked! So for the loop, we would want to cut out the operations of the cut and paste action and have H3 and down spit out the value for each row categorized by the provider number .
Also, if were to include the loop function, where would we tie into the orginal marco listed above?

I would like for the final output to be:

Something like this 10001(Column A): (column B) '10/01/15' StartDate,'09/30/16' EndDate,4 FacilityId,0.8319 WageIndex,1 COLAdjustment,0.8816 GeographicAdjustment,0.06744 OperatingIMERate,0.07289 CapitalIMERate,0.01388 OperatingDSHAdjustment,0.04068 CapitalDSHAdjustment,554.46601918 UncompensatedCarePayment,0.25 UCPFactor,0.332 OperatingCCR,0.029 CapitalCCR,0.99919 VBPAdjustmentFactor,0.9934 ReadmisstmentFactor,438.75 CapitalStandardFederalPaymentRate,3389.78 LaborPortion,2077.61 NonLaborPortion,22538 FixedLossOutlierThreshold,0.8 MarginalCostFactor,0.02 Sequestration,0.01 HACFactor ,0 [SCH_EACH],0 HSPRate

as opposed having to copy and paste each row of data into column b in the macro equation tab


does that make sense?