Macro Help. How to add row when certain value reached?

[JPWRana]

I need help with a macro that adds a row under the following condition:

From N5:N80, if any of those cells contains a 5 or higher, then add a row below that cell where you have the 5 or higher?

Ex: N26 has a value of 6. Therefore I would want a row below N26. Then N55 has a 7. I want a row added below N55?

If cell value is 5, add 1 row. If cell value is 9, add 2 rows. If cell value is 13 add 3 rows. That's it.

Is this possible?

 

[Smitty]

Welcome to the Board!

How are the values changing? Via formula or manual entry?

 

[JPWRana]

They are changing by Formula.

 

[JPWRana]

Does it matter if values are input by formula or manual entry?

 

[Smitty]

In this case it would be easier if the values were input by hand.


The part that I'm having a hard time wrapping my head around is this:

You could use a Calculate event to evaluate each cell in the range, and insert the number of rows you want. That part is easy. The next part not so much:

1) The range then expands, so do you want to evaluate the expanded range the next time?
2) It's easy to add rows once, but what happens the next time a value changes? If something changes from 13 to 9, do you delete a row to go from 3 to 2? And how do you account for situations where rows have already been added? It could get into a recursive situation where you continuously add rows and get stuck in a loop, so how to you tell it to stop?

 

[JPWRana]

In this case it would be easier if the values were input by hand.


The part that I'm having a hard time wrapping my head around is this:

You could use a Calculate event to evaluate each cell in the range, and insert the number of rows you want. That part is easy. The next part not so much:

1) The range then expands, so do you want to evaluate the expanded range the next time?
2) It's easy to add rows once, but what happens the next time a value changes? If something changes from 13 to 9, do you delete a row to go from 3 to 2? And how do you account for situations where rows have already been added? It could get into a recursive situation where you continuously add rows and get stuck in a loop, so how to you tell it to stop?

The values are input just one time by other formulas. Those formula results are based on a macro that I have that gives me photo hyperlinks for each cell.

In simpler words, the values input by the formula are done ONLY ONE TIME.

Once the macro adds rows at the right places, If MORE ARE NEEDED (this is possible based on my specific criteria and scenarios), then it's ok... I can manually do this, because even a live human being can't know unless all the photo hyperlinks (that is what the value is for... how many photos a widget has) are opened, I don't even know how many rows. What I DO KNOW is that I can't have more than 4 hyperlink photos (meaning a value of 4) without needing another row.

I hope this helps.

 

[Smitty]

OK, then I'd probably add it to the code that you've got now. I've got a meeting in a minute, but I'll see what I can do when I get back.

 

[JPWRana]

OK, then I'd probably add it to the code that you've got now. I've got a meeting in a minute, but I'll see what I can do when I get back.

Thanks

 

[Smitty]

See if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 79 <SPAN style="color:#00007F">To</SPAN> 5 <SPAN style="color:#00007F">Step</SPAN> -1<br>        Debug.Print i<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Cells(i, "N").Value<br>            <SPAN style="color:#00007F">Case</SPAN> 5 <SPAN style="color:#00007F">To</SPAN> 8<br>                Cells(i + 1, "N").EntireRow.Insert<br>            <SPAN style="color:#00007F">Case</SPAN> 9<br>                Range(Cells(i + 1, "N"), Cells(i + 2, "N")).EntireRow.Insert<br>            <SPAN style="color:#00007F">Case</SPAN> 10 <SPAN style="color:#00007F">To</SPAN> 12<br>                Cells(i + 1, "N").EntireRow.Insert<br>            <SPAN style="color:#00007F">Case</SPAN> 13<br>                Range(Cells(i + 1, "N"), Cells(i + 3, "N")).EntireRow.Insert<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[JPWRana]

The macro does what I want, but I have to end up running it separately. I tried to put in Call foo before End Sub but it doesn't work.

How can I combine the macro you gave me with the one I have for creating hyperlinks?

Sub Make_Hyperlinks_for_Photos()
Dim fs, fol, fil, count, i
count = 1
Set fs = CreateObject("Scripting.filesystemobject")
Set fol = fs.getfolder(Range("D84")) 'Change This
For Each fil In fol.Files
    i = Len(fil.Name)
    Do Until Mid(fil.Name, i, 1) = "."
        i = i - 1
    Loop
    Range("A" & count).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fil.Path, TextToDisplay:=Left(fil.Name, i - 1)
    count = count + 1
Next

[B]Call Row_Addition

[/B]End Sub

I changed the name from foo() to Row_Addition()