Infiinite If/Then scenarios and a macro

[duckyryan]

Hello,

I'm working on a complex problem. I have several distinct headings that each have a number of rows in them and each heading/row is replicated across (4) sheets. For example...

Heading A
row 1
row 2
row 3

Heading B
row 1
row 2
row 3
row 4

I'm trying to create a series of macro buttons that will search for the first unused cell (which for Heading A would be row 4 and for B would be row 5) and insert a new row at the bottom. I've generally come up with this code to do so. It checks the B cell of each row, starting with my row 1, to determine if that row is numbered. If it finds it numbered, it checks the next row, and so on. For 5 rows it wouldn't be difficult. I skipped over B15-B31 to check the first cell that is actually blank, just to test my formula.

=IF(B12="","A12",IF(B13="","A13",IF(B14="","A14",IF(B32="","A32","0"))))

The code worked and spit out "A32". Now I can use that number in my macro as the location where to insert a new row and duplicate that across (4) sheets.

**Question #1 is what formula/function can I use to make excel check every row starting with B12 until it finds a blank row.

Now I want to create a second macro button for Heading B that performs the same function but starts with the first row under heading B.

**Question #2 is what formula I should use to first find the cell for Heading B and then start checking for the first blank row after that to insert my new row? Heading B will never have a definite cell because I may have added 1,2 or 100 new rows in heading A.

Any help is appreciated! Thanks!

Ryan

 

[gmhumphr]

Ryan,

I don't quite understand what you are doing with the data, but, I've got a code snippet below for your consideration that solves your tactical need from question 1. It should illustrate that looping down one by one in a formula is not going to solve your problem, and VBA has faster ways of finding the last cell (start at the last cell in a workbook for a given column [B or otherwise, you provide the input to the code through the message box], and going up until it finds a non-blank cell, then moving one back down to the first blank cell). This is easily expanded upon to meet your additional needs. Know that B12 is your starting point for question 2? Then use that as the lower bound and move upward to find your empty cell starting from B1.

Sub find_last()
Dim scol As String
Dim lrow As Long


'Currently asking for an input, would be a good place to introduce a loop if you can define headers
scol = Application.InputBox( _
    prompt:="Column letter to be searched", Default:="A", Type:=2)


'Given a column (header), find the first empty cell. Msgbox displays it to you, but this could then be passed to further action, like inserting lines
MsgBox Range(scol & Rows.Count).End(xlUp).Offset(1, 0).Address
End Sub