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]

The request I have on here is only a small part of what is going into the sheet. I had been working my way through the different pieces that need to go into it, and only when I couldn't figure out how to do something is when I decided to ask on here.


What I meant by the last part of my previous post is do you want me to write up the entire list of logic coding that needs to go into this sheet so you can see if there is maybe a more simple way to go about coding it?

 

[L. Howard]

The request I have on here is only a small part of what is going into the sheet. I had been working my way through the different pieces that need to go into it, and only when I couldn't figure out how to do something is when I decided to ask on here.


What I meant by the last part of my previous post is do you want me to write up the entire list of logic coding that needs to go into this sheet so you can see if there is maybe a more simple way to go about coding it?

Okay, sure. If I can't figure it out myself in this thread, then posting a new thread under a new title is always an option. So, if I struggle getting a solution then posting anew will get many more viewers who are stronger in that particular area than me.

Howard

 

[SanFelippo]

Here is all the logic coding that needs to go into this sheet:

1. At a minimum, the following cells in row 4 and row 27 need to be completed, respectively:

Row 4: B,C,D,E,F,I,J,K,M,Q,R,S,T,U.
Row 27: B,C,D,E,F,K,M,O.

2. If cell I20 is > 100%, an error box must open stating "Funds allocated among properties cannot be greater than 100%".

3. Cannot enter a comma (,) or semicolon ( in any row of the Street Address columns. If there is one entered, an error box must open stating "please make sure to enter a single address in each row and remove any commas in the addresses listed." Coding that removes them would work too.

4. If an address is entered in column B or Z, the following columns in the same row as that address cannot be blank:

Address entered in Column B: C,D,E,F,I,J,K,M,Q,R,S,T,U.
Address entered in Column Z: AA,AB,AC,AD,AE,AF, AG,AH,AJ,AK,AL,AM,AN

5. If B4 is the only address cell that has text in it, then I4 must = 100%. If there is an address entered in more than one address cell in column B, then I4 opens up an allows free form text to be entered (AKA, so that the correct percentage allocated to that address can be entered.

6. If J or AF = "Yes", then columns K and AG cannot = 0. If 0 is entered, an error message should open stating "If the property contains a dwelling, residential use cannot be 0.00%."

7. If Construction Method in column S or AL = "Site-Built", then columns T-U and AM-AN must default to "Not Applicable" respectively. If Construction Method in column S or AL = "Manufactured Home", then columns T-U and AM-AN must be highlighted light blue and are then required to be completed.

8. Restrict cells in Row 20 so they can not be changed from what is already in them.

9. Highlight the highest percentage amount that resides in Column I, cells 3-18 yellow. Do the same fro Column AE, cells 3-18.

10. Highlight any "Yes" that resides in Column K, cells 3-18 Yellow. Do the same for Column AF, cells 3-18.

11. Highlight any Percentage greater than 50% in Column K, cells 3-18 yellow. Do the same for Column AG, cells 3-18.

 

[L. Howard]

Okay, pretty good laundry list.

For No. 1:
For the rows 4 through 18 you have code in the linked workbook that requires all those columns to be completed, no "blanks" or "(Select One)",
if there is anything in B C D column of a row. That is to say, the code uses the first three columns of the row to determine if it is a viable row to judge for completeness throughout the other required columns E,F,I,J,K,M,Q,R,S,T,U.

However, the code includes column O. Is that okay?
The code in the linked workbook only goes to column R, easily changed with a single character change in the code.

And you want row 27 included in the "completeness" check. (Noting that row 27 includes column O and has a column Q).

Also, would standard practice be to use say, rows 4 through 8 completed with 9 through 18 unused? In other words, use only as many rows from 4 on down as needed? The rest remain unused and ignored by the code because there is nothing in either of the columns B C D.

For No. 3:
You already have code in the linked workbook that does that for columns B to R. Do you want to restrict that to B C D?
The pop up message occurs, maybe need to rewrite the sentence.

A step by step description of how you would normally use this sheet could be helpful. The process of using the sheet is probably pretty plain, but explaining it would leave me with what a completed sheet would look like just before you click the "Next" button to evoke the "code of correctness".

I'll ponder the remaining list and see what I can do.

Howard

 

[L. Howard]

I have made some changes to the codes. The workbook in the post #17 link has these codes installed.
-comma/semicolons remove
-I20 100% limit
-B-E & Z-AC columns and row 27 address and data completeness check.

Howard


In the sheet module of sheet "4. Property Information"

Private Sub Worksheet_Change(ByVal Target As Range)

If (Range("I20") / 1) > 1 Then

  MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _
         "Review values in Columns I and AE"
End If

If Intersect(Target, Range("B4:D18,Z4:AB18")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

If InStr(1, Target, ",") > 0 Or InStr(1, Target, ";") > 0 Then

   Application.EnableEvents = False
   With Target
    .Replace What:=",", Replacement:="", LookAt:=xlPart
    .Replace What:=";", Replacement:="", LookAt:=xlPart
   End With
 
   Application.EnableEvents = True
 
  Else
 
   Application.EnableEvents = True
   Exit Sub
 
End If

 MsgBox "Comma's or Semi-Colon's removed from " & Target.Address(False, False)
 
End Sub

In a standard module

Sub NextTab_1()
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")   For i = 2 To 5
   
      ReDim Preserve varColsB(n)
      varColsB(n) = .Cells(18, i).End(xlUp).Row
      n = n + 1
      
   Next
   
   For J = 26 To 29
   
      ReDim Preserve varColsZ(m)
      varColsZ(m) = .Cells(18, J).End(xlUp).Row
      m = m + 1
      
   Next
   
   LRowB = Application.Max(varColsB)
   LRowZ = Application.Max(varColsZ)
   
   Set OneRng = Union(.Range("B4:S" & LRowB), .Range("Z4:AN" & LRowZ), _
                      .Range("B27:Q27")).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]

Howard,

For No. 1:
For the rows 4 through 18 you have code in the linked workbook that requires all those columns to be completed, no "blanks" or "(Select One)",
if there is anything in B C D column of a row. That is to say, the code uses the first three columns of the row to determine if it is a viable row to judge for completeness throughout the other required columns E,F,I,J,K,M,Q,R,S,T,U.

I believe it requires completeness throughout the other required columns if just Column B has anything entered into it. However, the very first row (row 4) needs all of those corresponding columns I listed filled in regardless. Basically, the person using this workbook is going to get a version where there is nothing filled out at all to start. There are in reality like 8 other tabs that need to be filled out too in this workbook which is why there is a "Next" Button. There needs to be at least one of those rows filled out (aka row 4) before they can hit next. There is the possibility that there are other properties that could be used to help secure the loan they are trying to get, which is why there are more rows to enter the address information for them if there are additional properties (rows 5 through 18), but those are just additional and there if they are needed.

Column Z is just the start of more additional rows for address information in the event rows 4 through 18 that column B is a part of are all filled out and there are more to be entered. So basically, Column Z would also have the same logic coding that says if anything in column Z is filled out, then the corresponding completeness check must also be run for the columns I listed in # 4 of the process I typed up. (I am not sure if your code checks column Z or not)

However, the code includes column O. Is that okay?

Column O (Property Value) is not required to be completed to hit next.

And you want row 27 included in the "completeness" check. (Noting that row 27 includes column O and has a column Q).

In Row 27, B,C,D,E,F,K,M, and O need to be completed. Columns I, J, and Q do not need to be completed.

Also, would standard practice be to use say, rows 4 through 8 completed with 9 through 18 unused? In other words, use only as many rows from 4 on down as needed? The rest remain unused and ignored by the code because there is nothing in either of the columns B C D.

Yes, you are only going to use as many rows as you have addresses. If you filled up every row (4-18) and have more addresses to enter, you would move over to where Column Z is and the start using those. If you start using the rows that include Column Z, then the same completeness check needs to be applied to columns AA,AB,AC,AD,AE,AF, AG,AH,AJ,AK,AL,AM,AN.

You already have code in the linked workbook that does that for columns B to R. Do you want to restrict that to B C D?
The pop up message occurs, maybe need to rewrite the sentence.

They asked me to make it just check the address cells (Column B, rows 4-18 and column Z, rows 4-18 for commas and semi-colons, so restricting it to just Column B and Column Z would be giving them exactly what they want, but I don't think it's necessary if it'll take a lot more work.

 

[L. Howard]

Have you downloaded the workbook with the link posted in Post #17?

If not, try it and see how it works.

Howard

 

[SanFelippo]

It gets difficult because my work computer doesn't allow me to access dropbox, but I was able to get the workbook from home and send it over to myself. A lot of it seems to work great so far! I do have a few notes:

2. If cell I20 is > 100%, an error box must open stating "Funds allocated among properties cannot be greater than 100%".

If the percentage does go over 100%, the message box does pop up, but if everything else is correct on the sheet, I am still able to hit the next button and have it move on to the next tab even with the percentage being greater than 100%. Would there be a way to prevent the next button from moving on until the percentage is fixed?

1. At a minimum, the following cells in row 4 and row 27 need to be completed, respectively:

Row 4: B,C,D,E,F,I,J,K,M,Q,R,S,T,U.
Row 27: B,C,D,E,F,K,M,O.

The completeness check seems to be checking and flagging an error if there is nothing filled out at all in Column Z, Row 4. Column Z and the columns following it are just additional cells to be used if all of the rows (4-18) that include Column B are filled up and there are still more addresses to be entered. The error box shouldn't pop up if nothing is filled out at all in Row 4, Column Z if it wasn't meant to be used. For example, if there was only one address, it would have gone in Row 4, Column B, and so long as the rest of the required columns in Row 4 (up until Column U) are filled in and Row 27 is filled in, everything would be fine.

 

[SanFelippo]

Also, correct me if I am wrong, but I believe this bit of code in the Module needs to be changed?

It currently reads as this:

Set OneRng = Union(.Range("B4:S" & LRowB), .Range("Z4:AN" & LRowZ), _
                      .Range("B27:Q27")).SpecialCells(xlCellTypeVisible)

but I believe it should be this in order to include both T and U in the completeness check?:

Set OneRng = Union(.Range("B4:[COLOR=#ff0000][B]U[/B][/COLOR]" & LRowB), .Range("Z4:AN" & LRowZ), _
                      .Range("B27:Q27")).SpecialCells(xlCellTypeVisible)

And this is just a shot in the dark, but if I wanted to not include column O and AI in the completeness check, how would you do that?

 

[L. Howard]

It gets difficult because my work computer doesn't allow me to access dropbox, but I was able to get the workbook from home and send it over to myself. A lot of it seems to work great so far! I do have a few notes:



If the percentage does go over 100%, the message box does pop up, but if everything else is correct on the sheet, I am still able to hit the next button and have it move on to the next tab even with the percentage being greater than 100%. Would there be a way to prevent the next button from moving on until the percentage is fixed?



The completeness check seems to be checking and flagging an error if there is nothing filled out at all in Column Z, Row 4. Column Z and the columns etc. etc.

For the over 100% check:
In the standard module make this change to the beginning of NextTab_1 and give it a test.
The > 100 will be checked "when it happens" during entry AND will prevent the Next code from running if not fixed.

Find the code line For i = 2 To 5 and from there UP replace with this.

Sub NextTab_1()
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

' rest of the code is below, only need to change the above and delete this line

For the false selection in the column Z block problem, I am looking at some code change to fix that.


This is correct for columns T & U...

Set OneRng = Union(.Range("B4:[COLOR=#ff0000][B]U[/B][/COLOR]" & LRowB),

For the non-check of columns O and AI, can you move them to columns V and AO where they would be outside the completeness check range? If not I will see if I can make some code change to the completeness check range.

Howard