Question on existing code

[JoeExcelHelp]

Hi Rudi,

Just trying to better understand this code you created (which is awsome BTW)
If I start my table on A4 Set rgD = Intersect(shD.Range("A4").CurrentRegion, shD.Range("A4").CurrentRegion.Offset(3, 1))
and all my RC references dont touch R1,2 or 3
sFile & "C5,""=""&R5C," & _
sFile & "C6,""=""&R6C," & _
sFile & "C8,""=""&RC1," & _
sFile & "C2,""=""&R4C," & _
is it safe to say anything I add to R1,2 or 3 will not effect the run?

Thank You

Code: Select all

Sub ImportActualsJBU()
Dim sPath As String
Dim sFile As String
Dim wbD As Workbook
Dim shD As Worksheet
Dim rgD As Range
Dim wbS As Workbook
Dim shS As Worksheet
Dim rgS As Range

    Application.ScreenUpdating = False
    
    sPath = "\\kewntfile01\sharedfile\Operations Planning\__Dashboards by RP\Airport Training_Class_Rosters\Airport_Class_Roster.xlsx"
    If sPath = "False" Then Exit Sub
    Set wbD = ThisWorkbook
    Set shD = wbD.Sheets("Orientation")
    Set rgD = Intersect(shD.Range("A4").CurrentRegion, shD.Range("A4").CurrentRegion.Offset(3, 1))
    Workbooks.Open sPath, UpdateLinks:=False
    Set wbS = ActiveWorkbook
    Set shS = wbS.Sheets(1)
    Set rgS = shS.Range("A1").CurrentRegion
    sFile = "'[" & Split(sPath, "\")(UBound(Split(sPath, "\"))) & "]" & shS.Name & "'!"
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C16," & _
        sFile & "C5,""=""&R5C," & _
        sFile & "C6,""=""&R6C," & _
        sFile & "C8,""=""&RC1," & _
        sFile & "C2,""=""&R4C," & _
        sFile & "C1,""<>""&""""," & _
        sFile & "C4,""<>""&""BP"")"
    rgD.Value = rgD.Value
    wbS.Close False
    Application.ScreenUpdating = True
End Sub

[HansV]

If you enter anything in row 3 of the Orientation sheet, shD.Range("A4").CurrentRegion will include cells in row 3, and hence rgD will include cells in row 6. You don't want to overwrite row 6 with the formula (and subsequently its value). So row 3 should remain empty.
It doesn't matter what you enter in rows 1 and 2.

[Rudi]

I hardly recall...

The CurrentRegion property makes selecting dynamic blocks very easy, but it can also bite you if data is entered in cells that might connect two different blocks; like connecting your data with its titles. I'm a bit foggy on the structure of your data but what Hans mentions is correct if row 3 separates titles (or criteria) from the data block. Row 1 and 2 can take any changes, but row 3 must stay empty to avoid the current region from expanding into your titles and skewing the result.

[JoeExcelHelp]

Thank You both,

Just when I think I have it figured out.. :)
Ok so let me know if this sounds correct
Because CurrentRegion includes everything until a blank row and Column
In this case the first blank row would be 3 because of my reference to A4
whereas all remaining columns and rows are avoided with the Offset (3,1)

[HansV]

This is the situation when row 3 is blank:

S238.png

Blue outline: Range("A4").CurrentRegion
Red outline: Range("A4").CurrentRegion.Offset(3, 1)
Yellow shading: the intersection of the above.

But if row 3 is not empty:

S239.png

As you can see, the shaded area now includes header rows.

[JoeExcelHelp]

Thank You Hans.. that display was very helpful

[Rudi]

TX Hans... I was setting up some explanation using screenshots and selections, but the borders, shading and colours produces a much better visual.
You'll make a great (specifically cubism)

Hans Self Portrait:

Spoiler

Image 013.png

[HansV]

More like Mondrian, perhaps. He probably used Excel to design his paintings...

S1067.png

[Rudi]

That looks like the color palette from Excel 97

[HansV]

Piet Mondriaan used only black, white and primary colors in his later work.