Row addition macro: How to also copy what's above?

[JPWRana]

The following formula adds a row for me when a certain cell has a 4. If it has 5-8 it adds 2 rows. If the values is 9-12 it adds 3 rows.

Sub Row_Addition() 'This adds rows If I have more than 4 pictures per Pole 
    Dim i As Long 
    Sheets("Checksheet").Activate 
    For i = 79 To 5 Step -1 
        Debug.Print i 
        Select Case Cells(i, "N").Value 
            Case 5 To 8 
                Cells(i + 1, "N").EntireRow.Insert 
            Case 9 
                Range(Cells(i + 1, "N"), Cells(i + 2, "N")).EntireRow.Insert 
            Case 10 To 12 
                Cells(i + 1, "N").EntireRow.Insert 
            Case 13 
                Range(Cells(i + 1, "N"), Cells(i + 3, "N")).EntireRow.Insert 
        End Select 
    Next i 
             
End Sub  

What I don't know how to do is, how can I add in the same macro...

1) If a row is being added, how can I also add on the new row, exactly what is above ?2 (?= Column Letter)
2) If 2 rows are being added, how can I add to those 2 new rows exactly what is above ?2 (?= Column Letter)
3) If 3 rows... etc.

4) Also, C5 and D5 are merged. How can I (in this macro) also tell the row addition macro to merge ?5 & (?+1)5?

5) I have a set of formulas on O5, P5, Q5, and R5. When i add rows, I also need the rows to be populated where usually by hand you highlight then, and drag on the bottom right hand corner... EXCEPT that when they get added where the new rows are added (due to the macro), a slight change needs to happen...

This is formula for O5

=IF(Sheet1!J2="","",IF(AND($B5=$B4,$B4=$B3,$N5>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B4&"("&SUM(O$4,$N4,$N3)&")"&".JPG","("&SUM(O$4,$N4,$N3)&")"),IF(AND($B5=$B4,$N5>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B4&"("&SUM(O$4,$N4)&")"&".JPG","("&SUM(O$4,$N4)&")"),IF($N5>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B5&"(1).JPG","(1)"),""))))

If there will be a row addition (a new Row 6), then O6 must be
=IF(Sheet1!J3="","",IF(AND($B6=$B5,$B5=$B4,$N6>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B5&"("&SUM(O$4,$N5,$N4)&")"&".JPG","("&SUM(O$4,$N5,$N4)&")"),IF(AND($B6=$B5,$N6>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B5&"("&SUM(O$4,$N5)&")"&".JPG","("&SUM(O$4,$N5)&")"),IF($N6>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B6&"(1).JPG","(1)"),""))))

Which is similar if you just highlight and drag down, EXCEPT THAT Sheet1!J3 MUST SAY Sheet1!J2. Everything else is good.

Any help is greatly appreciated.

[JOHN H. DAVIS]

Maybe this can help?

Please Login or Register  to view this content.

For the new rows.

[JPWRana]

John, is this supposed to do requests 1) through 5)?

[JOHN H. DAVIS]

I think this only handles 1-3. I'll look at 4-5 in the morn., if no one else helps. I'm going off line now.

[JPWRana]

I got an error at the following line:

Cells(i, "N").EntireRow.Copy Range(Cells(i + 1, "N"), Cells(i + 2, "N"))

[JOHN H. DAVIS]

Can you attach a sample for testing? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[JPWRana]

Sure. Let me clean it up a bit then I'll upload it.

Meanwhile, in seeing how the macro ran, it did only 1). It copied everything over when I didn't want to. It copied over A2, L2 and M2, where I didn't want it to.

[JPWRana]

The macro applied to only 3 different poles in this case.

Pole 27 required 2 row additions (There is a 9 on N31). ON THAT ROW I got an error.

On Poles 52 & 55, only 1 row was needed (N63 & N9 had a value greater than 4 but less than 9).


I highlighted in YELLOW how the macro gave me the results.
I copied those rows just above it and highlighted it in GREEN to show how it needs to look like once the macro does its work.

Note: Columns O - R have that long formula where if I want the row addition (which applied only 3 times in this case), the only change that is needed is the Sheet1'!J## part of it (## = a number). I made that change manually to show how those row addition formulas need to be.

Let me know what you think.

[JOHN H. DAVIS]

Let me know if this works for points 1-3? If so, I'll try to modify for points 4 and 5.

Please Login or Register  to view this content.

[JPWRana]

John, there is mixed success with the updated macro.

1) I no longer get errors
2) Cell ?2 does copy over for 2 and 3 rows.
3) Cell N? does copy over for rows 2 and 3.

What it also does that I don't want it to do is...
1) Copy A? down onto the next cells. It's not a deal breaker but it does throw me off.
2) Copies L? and M? also without issue, but I also don't want that copied.


What it still doesn't do
1) Give me a merged C? with D? (? = same number)
2) The modified formulas from columns O-R.

See my uploaded excel file from post #8 to see how they need to look like.

Thanks again for your help.

[JOHN H. DAVIS]

Hard to do with the links.

Maybe:

Please Login or Register  to view this content.

[JPWRana]

John, this i much much much better/closer to what I need.

Column's O, P, and Q copy over, but not Column R.

Also, when the corresponding formula copies down, I do need for that gigantic formula to tweak such that the first cell it references in that long spaghetti of a formula it keeps it static.

Example: O31 says...

=IF(Sheet1!J28="","",IF(AND($B31=$B30,$B30=$B29,$N31>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(O$4,$N30,$N29)&")"&".JPG","("&SUM(O$4,$N30,$N29)&")"),IF(AND($B31=$B30,$N31>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(O$4,$N30)&")"&".JPG","("&SUM(O$4,$N30)&")"),IF($N31>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"(1).JPG","(1)"),""))))

When the macro adds a row below O31, O32 says
=IF(Sheet1!J29="","",IF(AND($B32=$B31,$B31=$B30,$N32>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"("&SUM(O$4,$N31,$N30)&")"&".JPG","("&SUM(O$4,$N31,$N30)&")"),IF(AND($B32=$B31,$N32>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"("&SUM(O$4,$N31)&")"&".JPG","("&SUM(O$4,$N31)&")"),IF($N32>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B32&"(1).JPG","(1)"),""))))

The formula needs to be tweaked so that O32 is changed from having Sheet1!J29="" to having what O31 has, which is Sheet1!J28="".

Any way of modifying those O-R formulas with that small tweak?

[JOHN H. DAVIS]

This should pick up Column R.

Please Login or Register  to view this content.

For your formula you need to add $ signs

=IF(Sheet1!$J$28=""

[JPWRana]

Yes, you are right, the new macro does pick up Column R, and you are also right, if I add a $ sign it does make it fixed.

However, I only need it to be fixed when I copy O-R to the new row (or 2 or 3), so that the new formulas on the new cells from the new rows on O-R pick up Sheet1!J28. Other than that, Sheet1!J28 isn't supposed to be Sheet1!$J$28="".

Do I need to approach the formula entirely a different way? Or can the macro handle a tweak in formulas O-R ONLY WHEN ROWS ARE BEING ADDED?

[JOHN H. DAVIS]

That's a tough one. I feel you'd have to adjust the formula vrs the macro. I'm a bit afraid to recommend another if statement too such a complex formula, and I'm not well versed in formulas. That's all I can think of?

[JPWRana]

Thanks John. I'll see if I can ask that as a SEPARATE thread question. Maybe a separate macro can fix that part of the O-R column formulas.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.