VBA formula for sheet reference, with a twist

[Cornejo]

Forgive my ignorance and my lack of vernacular. I'm very new to writing macros and coding. Everything I know today is self-taught and derived from forums, google, books, and whatever else I have been able to consult.

I have a macro that loops to create a specific formatting for "events". It loops based on the content located on a different sheet ('Data Dump'). The following is a general idea of how the formatting is laid out:

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event Number:[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD]2018-02001[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD](data)[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event Number:[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD]2018-02002[/TD]
[TD](data)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



"2018-02001" is what I've got a question about, which is explained later.


Forgive me if the visual is obnoxious or "noob-like". It is the best way I can assist in conveying my question. The number below the "Event Number" is achieved via formula early on in my macro and is calculated as follows:

ActiveCell.FormulaR1C1 = "=YEAR(RC[-59])&""-""&(TEXT(MONTH(RC[-59]),""00"")&""001"")"

This formula is then auto-filled to the end of the data and the "001" (at the end of the formula) increases by one. This works perfectly for the 'Data Dump' sheet, but not later on.

My macro is written to generate these "events" using a lot of "Offset" (properties?). This puts 16 cells between the "Event Numbers". I would like to achieve essentially the following:

='Data Dump'!C3

for each event type. Meaning, I just want these event numbers to be equal to the content listed in the 'Data Dump" sheet. The current code I have is:

ActiveCell.Formula = "=INDIRECT(""'Data Dump'!""&ADDRESS(1*ROW()-50,3))"

However, it doesn't account for the 16 cells in between each event. The code above yields "2018-02002" which is exactly what I want. Then, the following event comes back as "2018-02019". Tinkering with the above formula I discovered that if I keep a running total of the -50 portion of the code to subtract 16... then I get what I want, as follows:

ActiveCell.Formula = "=INDIRECT(""'Data Dump'!""&ADDRESS(1*ROW()[B]-50[/B],3))"

yields "2018-02002"

ActiveCell.Formula = "=INDIRECT(""'Data Dump'!""&ADDRESS(1*ROW()[B]-66[/B],3))"

yields "2018-02003"

ActiveCell.Formula = "=INDIRECT(""'Data Dump'!""&ADDRESS(1*ROW()[B]-82[/B],3))"

yields "2018-02004"

ActiveCell.Formula = "=INDIRECT(""'Data Dump'!""&ADDRESS(1*ROW()[B]-98[/B],3))"

yields "2018-02005"

And so on... Is there a way to tweak this formula or perhaps create a new one that can achieve my desired outcome?

I greatly appreciate the assistance.

 

[Cornejo]

So I was able to stir up a work around. Here's what I did:

Set Rng = Sheets("Data Dump").Range("C4:C10000")
    
    Range("DL3").Select
    
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(16, 0)).Select 'Range("DL4:DL19").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    For Each Spacing In Rng
    If Spacing <> "" Then
    
    Range(ActiveCell.Offset(17, 0), ActiveCell.Offset(32, 0)).Select 'Range("DL21:DL36").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(ActiveCell.Offset(17, 0), ActiveCell.Offset(32, 0)).Select 'Range("DL38:DL53").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Else
    End If
    Next

I simply established a helper column, created the loop above (which inserts 16 cells between each event number), and changed the references of my indirect formulas. This was an issue that had me wrapped up and stumped for a while, but I'm certainly glad I was able to figure out a work-around. Thanks to all the pour souls that attempted to read my first post! I apologize if it gave you a headache!