Identifying empty cells and inputting correct value in with inputbox

[mtdewrocks]

I would like to loop through all rows of data to ensure the region is filled in. If not, I would like an input box to identify the store number and ask the user to input the correct region, and then have what the user typed fill in the empty region cell.

I.e. the store number is 10 but the region which is two columns to the right is empty. Therefore, the loop would prompt an input box saying "Store 10 region is empty. Please enter the correct region."

I tried something along the following, but it isn't working.

J is the looped row, and column 5 is where the region field is. Column 3 maintains the store number.

If Cells(j, 5) = " " Then ActiveCell.Value = UpdatedREGION
UpdatedREGION = Application.InputBox("ActiveCell.Offset(0, -2)" & "region field is empty. Please insert region as KC, DAL, or CHI.")
Cells(j, 5).Value = UpdatedREGION


Please provide guidance if possible.

 

[Meggesto]

Sub StoreRegion()

Dim LastRoxC As Long
Dim Store As Long
Dim StoreName As String
Dim Region As String

LastRowC = Range("C" & Rows.Count).End(xlUp).Row
'Finds the Last Row Number of the Store Column to know how many iterations of the loop to perform.

For Store = 2 To LastRowC Step 1
'From Row 2 (Assuming Row 1 is a Header Row) to the last row with a store location.

    If Cells(Store, 5) = "" Then
    'If the region that corresponds to the store is blank then
        StoreName = Cells(Store, 3).Value
        'Saves the Stores name as the variable StoreName
        Region = InputBox(StoreName & " region is empty.  Please enter the correct region.")
        'Tells the user the Region for the StoreName we're currently looping through is empty and asks for the correct region.
        Cells(Store, 5).Value = Region
        'Sets the blank region equal to the number input by the user
    End If
Next Store
'Loops back around to the next store

End Sub

I believe this should do what you're looking for. It assumes that your Stores start in C2 and Regions start in E2. It will move through each store and check to make sure the region is not blank. If it is, it will ask the user to input the correct region and then it will move to the next store.

 

[Roderick_E]

How about something like this? This assumes the region is column J and the store is column H. It will look through the worksheet to last populated row. It will only consider if the region is blank but the store column is populated.

lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For x = 1 to lastrow
if trim(cells(x,"j"))="" and trim(cells(x,"h"))<>""  then
[COLOR=#333333]UpdatedREGION = Application.InputBox(cells.(x, "h")" & "region field is empty. Please insert region as KC, DAL, or CHI.")[/COLOR]
[COLOR=#333333]Cells(x, "j").Value = UpdatedREGION
[/COLOR]endif
next x
msgbox "Complete"

 

[mtdewrocks]

Thanks both of you for the help. I tried your suggestion Meggesto and it worked great. I have not tried yours Roderick but I will as I like that it only pops up if the store column is filled in and the region is blank. One other question - what would be the best way to ensure the person only fills in KC, DAL, or CHI? Would it be best to use a userform, or what would be the best approach? Thanks again for helping me as I continue to learn VBA.

 

[mtdewrocks]

Would either of you be able to take a look at my other question regarding looping as well?
http://www.mrexcel.com/forum/excel-questions/947145-skipping-i-multiple-loop-please-help.html

 

[Roderick_E]

Good deal -- A userform would be best, then you could use a listbox with the KC, DAL, CHI -- otherwise you could validate UpdatedREGION with something like
if UpdateRegion<>"KC" and UpdateRegion<>"DAL" and UpdateRegion<>"CHI"

 

[mtdewrocks]

Roderick - could you explain how to use the Listbox - I have created the listbox, but am unsure on how I go about calling it up when the region is empty and also how to get the user selected value to fill into the empty region cell. Thanks for your help.

 

[Roderick_E]

Hi, you can populate a listbox at least 2 ways:

1) Store the list somewhere on a sheet in the workbook (I often have a sheet I hide called "configs") and name the list. For example: Create a sheet called configs. In A1 Have Regions as a header. In A2-A4 have KC DAL CHI. Selected A2-A4 and in the left field next to the formula line, put the word regionlist. Now on the listbox properties; RowSource you can input =regionlist.

2) Before/where you actually have a macro that launches the user form (Userform1.show) use additem to add your regions to the listbox. Exmaple:

Userform1.listbox.additem "KC"
Userform1.listbox.additem "DAL"
Userform1.listbox.additem "CHI"

There are more complex/clean ways of doing this but this helps you understand how the process works.

 

[mtdewrocks]

That helps. Could you possibly help with how that would fit into the InputBox. And maybe an inputbox is no longer appropriate if I go with a listbox - maybe I change it to an msgbox and once the borrower selects okay the listbox pops up asking which region it is in?

This is what I want to happen - it loops through all regions, identifying blank cells - then have it identify the store number to the user and ask them to select the correct region from the box. Then have what the user selected fill into the appropriate region cell.

Any further help is greatly appreciated!