Expanding a Macro Already Written

[SanFelippo]

Hi,

I have gotten some new requests, and now need to know if it would be possible to add on to the Macro that was written for me in a previous post. Currently, the macro checks for the last row that has an address entered into it in column B, and then makes sure a "Type" is selected in that same row in column F. I was wondering if it would also be possible to expand this.

I would need the Macro to also check and make sure that there are selections made in columns Q, R, S, T, and U if there is an address populated in row B. Columns Q and R are cells that are manually filled in, so it would need to make sure they are not blank, while columns S, T, and U are dropdown lists, so it would need to make sure that none of those are still on the "(Select One)" option.

Would it also be possible to have the Macro call out what is missing then? For example, right now if it is missing Type, the macro calls out which row it is missing the Type in. the Column Names for Q - U are as follows:

Q: Total (Dwelling) Units
R: Multifamily Afforable Units
S: Construction Method
T: Manufactured Home Secured Property Type
U: Manufactured Home Land Property Interest

Here is the original post along with the Macro that was written to satisfy it:

To make this easier to understand, I am going to post some sample data below the question I have. I have two columns of data, one is a column with 15 rows that one would enter addresses into, and then the other is a column called type that has a drop list that contains 4 choices:

(Select One)
Type 1
Type 2
Type 3

What I am looking for is a macro I can run that will look and see if there is an address filled out in Column B, and if there is, go and look to see if a Type was chosen. If a Type was not chosen (AKA the Type is still on the choice "(Select One)", I would like a message box to appear that stating that if an address is filled out in Column B, then a corresponding Type must be chosen, If possible, could the message box also indicate which row the error occurred on?

I know this could be done with like 15 if, then statements, but I figure there has got to be an easier way to do it, so I figured I would ask. I am not looking for this to be run or coded in a Worksheet_Change(ByVal Target As Range) Sub. This will be something I attach to a button that will run it when pressed.

Any help?




So, if the situation below was present and I ran the Macro, I would get the error box popping us telling me that if cell B5 is populated with an address, cell F5 must have a Type Chosen.

Column (B) Address	Column (F) Type
123 Anywhere	Type 1
1234 Anywhere	Type 3
12345 Anywhere	Type 2
123456 Anywhere	(Select One)

<tbody>
</tbody>

*This code was written by a member named Rick, and excel MVP on this forum*

Sub CheckTypeSelected()
  Dim LastRow As Long, RowNums As String
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  RowNums = Replace(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF(LEN(B1:B#)*(F1:F#=""(Select One)""),ROW(B1:B#),"""")", "#", LastRow))))), " ", ", ")
  If Len(RowNums) Then
    MsgBox "If an address is filled out in Column B, then a corresponding Type must be chosen in Column F." & vbLf & vbLf & "These rows have no Type selected: " & RowNums
  End If
End Sub

 

[SanFelippo]

Unfortunately, they told me they want the layout to match the layout of the information they will be using to fill this in, so I can't move any columns around.

 

[L. Howard]

Unfortunately, they told me they want the layout to match the layout of the information they will be using to fill this in, so I can't move any columns around.

Sure, I see their point. I'll give it a go with the coding.

Also, regarding the sequence of filling in the B block rows until full and then over to the Z block rows to continue the entries, I assume row 27 would be after the B block is filled and before starting in the Z block. Is that correct?

Howard

 

[L. Howard]

Also, regarding the sequence of filling in the B block rows until full and then over to the Z block rows to continue the entries, I assume row 27 would be after the B block is filled and before starting in the Z block. Is that correct?

Howard

Additional question on row 27.

Using your normal row entry procedures, when would row 27 be included in a completeness check.

With the B block, say row 4 to row 14 are filled (with or without some Blanks or Select One's). Would row 27 need to be checked even though there are still 4 rows empty in the B block?

Or would row 27 ONLY be used after row 18 is filled in? Or is there any other scenario about row 27?

Howard

 

[L. Howard]

Here is code that skips columns O and AI.

Discard previous code.

Waiting for row 27 info as in post #33 to proceed on that issue.

Howard

Sub NextTab_1()
'// Skips columns O and AI
Dim bRow As Long, LRowB As Long, LRowZ As Long, cCnt As Long
Dim OneRng As Range, c As Range
Dim cc As String
Dim i As Integer, J As Integer, n As Integer, m As Integer
Dim varColsB() As Variant
Dim varColsZ() As Variant
Dim myCheck
   
With ActiveWorkbook.Worksheets("4. Property Information")

If (Range("I20") / 1) > 1 Then
    MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _
           "Review values in Columns I and AE"
           Exit Sub
End If
   For i = 2 To 5
   
      ReDim Preserve varColsB(n)
      varColsB(n) = .Cells(19, i).End(xlUp).Row
      n = n + 1
      
   Next
   
   For J = 26 To 29
   
      ReDim Preserve varColsZ(m)
      varColsZ(m) = .Cells(19, J).End(xlUp).Row
      m = m + 1
      
   Next
   
   LRowB = Application.Max(varColsB)
   LRowZ = Application.Max(varColsZ)
   
   '// Skips columns O and AI
    Set OneRng = Union(.Range("B4:M" & LRowB), _
                      .Range("Q4:U" & LRowB), _
                      .Range("B27:Q27"), _
                      .Range("Z4:AH" & LRowZ), _
                      .Range("AJ4:AN" & LRowZ)).SpecialCells(xlCellTypeVisible)
   
   For Each c In OneRng
   
      If c = "" Or c = "(Select One)" Then
         cc = cc & ", " + c.Address(False, False)
         cCnt = cCnt + 1
      End If
      
   Next
   
   If cCnt > 0 Then
   
      .Range(Mid(cc, 3)).Select
      MsgBox "There are  " & cCnt & _
            " cells with ""Blank"" or ""(Select One)"" in these cell Address':" _
            & vbCr & vbCr & Mid(cc, 3)
     Else
   
      myCheck = MsgBox("All data point are positive." & vbCr & "Unhide Sheets(Lists)?", vbYesNo)
    
    If myCheck = vbNo Then
       '
      Else
        MsgBox "No sheet. Test only"
        'Sheets("(Lists)").Visible = True
        'Sheets("(Lists)").Activate
    End If
   
  End If
End With
End Sub

 

[SanFelippo]

Additional question on row 27.

Using your normal row entry procedures, when would row 27 be included in a completeness check.

With the B block, say row 4 to row 14 are filled (with or without some Blanks or Select One's). Would row 27 need to be checked even though there are still 4 rows empty in the B block?

Or would row 27 ONLY be used after row 18 is filled in? Or is there any other scenario about row 27?

Howard

Row 27 is actually separate from the top two sections. If there was a set order, I would say that row 27 would end up getting filled out last (AKA, after you have filled out as many addresses as need on top, with or without going over to column Z). But 27 is separate in that it absolutely needs to be filled in no matter what.

It doesn't really matter when it gets checked as long as it does in fact get checked. Essentially, if someone wanted to fill out row 27 first, and then go up and fill out the top section, they could do that, as no matter what order they do the sheet in, all of the conditions that allow someone to click next and have it move onto the next tab need to be met in the end.

 

[SanFelippo]

Sorry, I should also mention that in row 27, columns B-O are the ones required to be completed. Column Q in row 27 is the "Property Value" column again, and it is not required to be completed in order to hit next and move on.

 

[L. Howard]

Sorry, I should also mention that in row 27, columns B-O are the ones required to be completed. Column Q in row 27 is the "Property Value" column again, and it is not required to be completed in order to hit next and move on.

Okay, I think I got all that. Thanks.

Howard

 

[L. Howard]

Here is a revised-revised code, discard previous.

Ignores column O and AI and requires filling B column rows before using Z column rows.

The workbook linked in post 18# has this code.

Howard

Sub NextTab_1_BZ()
Dim bRow As Long, LRowB As Long, LRowZ As Long, cCnt As Long
Dim OneRng As Range, c As Range
Dim cc As String
Dim i As Integer, J As Integer, n As Integer, m As Integer
Dim varColsB() As Variant
Dim varColsZ() As Variant
Dim myCheck
   
With ActiveWorkbook.Worksheets("4. Property Information")

  If (Range("I20") / 1) > 1 Then
     MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _
            "Review values in Columns I and AE"
     Exit Sub
  End If

   For i = 2 To 5
   
      ReDim Preserve varColsB(n)
      varColsB(n) = .Cells(19, i).End(xlUp).Row
      n = n + 1
      
   Next
   
   
   For J = 26 To 29
   
      ReDim Preserve varColsZ(m)
      varColsZ(m) = .Cells(19, J).End(xlUp).Row
      m = m + 1
      
   Next
   
   LRowB = Application.Max(varColsB)
   LRowZ = Application.Max(varColsZ)
   
    If LRowB <> 18 And LRowZ > 3 Then

       MsgBox "There are empty rows in B column." & vbCr & vbCr & _
                  "Fill B column rows before using Z column rows.", vbOKOnly + vbCritical, "Bad Row Ahoy!"
      Exit Sub

    End If
   
   
   If LRowB < 19 And LRowZ = 3 Then
  
       Set OneRng = Union(.Range("B4:M" & LRowB), _
                          .Range("Q4:U" & LRowB), _
                          .Range("B27:O27")).SpecialCells(xlCellTypeVisible)
                          
     ElseIf LRowB = 18 And LRowZ > 3 Then
    
      Set OneRng = Union(.Range("B4:M" & LRowB), _
                          .Range("Q4:U" & LRowB), _
                          .Range("Z4:AH" & LRowZ), _
                          .Range("AJ4:AN" & LRowZ), _
                          .Range("B27:O27")).SpecialCells(xlCellTypeVisible)
      
   End If
     
     
   For Each c In OneRng
   
      If c = "" Or c = "(Select One)" Then
         cc = cc & ", " + c.Address(False, False)
         cCnt = cCnt + 1
      End If
      
   Next
   
   If cCnt > 0 Then
   
      .Range(Mid(cc, 3)).Select
      MsgBox "There are  " & cCnt & _
            " cells with ""Blank"" or ""(Select One)"" in these cell Address':" _
            & vbCr & vbCr & Mid(cc, 3)
     Else
   
      myCheck = MsgBox("All data point are positive." & vbCr & "Unhide Sheets(Lists)?", vbYesNo)
    
    If myCheck = vbNo Then
       '
      Else
      'MsgBox "No sheet. Test only"
        Sheets("(Lists)").Visible = True
        Sheets("(Lists)").Activate
    End If
   
  End If
End With
End Sub

 

[L. Howard]

For the column J / K and AF / AG try using Data Validation for the no 0 (Zero) with "Yes".

Select K column rows 4 - 18, then...

> Data Validation > Settings > Allow > Custom > Formula: > =OR(((K4<>0%)*(J4="Yes")),(ISNUMBER(K4))*(J4<>"Yes")) > Error Alert > If the property contains a dwelling, (AF) residential use cannot be 0 (zero). > OK.

I found that when the DV does it work alerting for the zero, that cell looses its "normal" % format and percent values show as hundreds, if percent value was 0 and then 3 was entered, it will show as 300. I have no cure for the individual cells but I found that the column can be General and then use this formula in the total row =SUM(AG4:AG18)/100 and the cell formatted as percentage.


This should take care of No. 6 on the "to do" list and it looks like No. & may be the same drill as the above.

The linked workbook has DV's for the two column K and AG.

I'll ponder the list for next issue.

Howard

 

[SanFelippo]

Awesome. I seriously can't thank you enough. Works great so far.