Looping macro with input message box receiving runtime error 91

[OMDhaf]

I have written vba code to hide columns when a range of cells do not match a specific initial. This piece of the code works. I want to expand the code with a message box to ask for input (Please enter initials?). Once initials entered run the below code based on the initials entered. Then ask Do you have additional initials? Based on that answer (Yes) either run the loop again or (No) stop the macro. This is my first time attempting to write code so thanks in advance for your help.

The loop should look at the initials entered, search the first cell and either hide the column or leave it open and then move to the next cell....etc...


Sub InputMessageMacroWithDo()



' this one works well to hide columns based on AS only currently using <> =
Dim rRange As Range
Dim rCell As Range
Dim ans As String
Dim myCheck As String
Dim initials As String

Set rRange = Sheets("Sheet1").Range("C25:<wbr>CK25").Cells

ans = MsgBox("Do you have Supervisor initials to enter?", vbYesNo)
If ans = vbNo Then Exit Sub
Do
initials = InputBox(Prompt:="Enter supervisor initials", title:="Supervisor Initials")
If UCase(rCell.Value) = initials Then rCell.Columns.EntireColumn.<wbr>Hidden = False
ans = MsgBox("Copy and paste the unhidden columns into a new tab. Label the tab with the Supervisors name. Do not press OK until you have completed the copy and paste. Once completed press OK.")
myCheck = MsgBox("Do you have more Supervisor initials to enter?", vbYesNo)
If myCheck = vbNo Then Exit Sub
Loop While myCheck = vbYes


End Sub

 

[JLGWhiz]

Hi OMDhaf, Welcome to MrExcel forum,
You have a couple of problems with this code.
1. rCell is not initialized so it will throw an error as soon as that line attempts to execute.
2. The myCheck message box tells the user to copy a column to a new workbook before clicking OK, which Excel will not allow to happen. While the message box is displayed, Excel is frozen.
3. The comparison of the initials from the InputBox to rCell(wherever it is on row 25) probably will be hit and miss since the code only allows the comparison to be between 'initials' and whatever is in cell C25. There is nothing in the code to advance the comparison range across the row for each 'initials'.

You might want to think about this one a little bit more and make some modifications to your logic.

 

[OMDhaf]

Thank you. I was able to initialize my rCell, remove completely the msgbox and use the copy and paste vba. I did get some additional help on having it read each cell in the row25 and not just the first cell. Thanks again.