Validation Testing through list

[Matt Pierringer]

My goal is to be able to have my loop go through a list and check if
any cell in column a is equal to the list, if not it must make a
prompt for the next 7 cells.

If ActiveCell.Value > 1 Then 'Not sure how to make this only column 1
For Each ce In Range("L1:L500" & Cells(Rows.Count, 1).End(xlUp).Row)
'Not sure how to use my named range "Material Numbers" instead of
"L1:L500"
ce.Offset(0, 1) = WorksheetFunction.CountIf(Range("L1:L500"),
ce.Value)
If ActiveCell.Value = ce.Value Then
ActiveCell.Offset(0, 1) = InputBox("Enter data", "Column B")
ActiveCell.Offset(0, 2) = InputBox("Enter data", "Column C")
ActiveCell.Offset(0, 3) = InputBox("Enter data", "Column D")
ActiveCell.Offset(0, 4) = InputBox("Enter data", "Column E")
ActiveCell.Offset(0, 5) = InputBox("Enter data", "Column F")
ActiveCell.Offset(0, 6) = InputBox("Enter data", "Column G")
ActiveCell.Offset(0, 7) = InputBox("Enter data", "Column H")
Exit Sub
Else
Next ce
End If

Thanks any help is appreciated
-Matt

 

[Guest]

You post is pretty confusing. Perhaps you can find something here:

Dim rng as range, cell as Range, bFound as Boolean
set rng = Range(cells(2,1),cells(rows.count,1).End(xlup)
bFound = False
for each cell in rng
If Application.Countif(range("MaterialNumbers"),cell) > 0 then
bFound = True
exit for
end if
Next
if bfound then exit sub
set cell = rng(rng.count + 1)
Cell.Offset(0, 1) = InputBox("Enter data", "Column B")
Cell.Offset(0, 2) = InputBox("Enter data", "Column C")
Cell.Offset(0, 3) = InputBox("Enter data", "Column D")
Cell.Offset(0, 4) = InputBox("Enter data", "Column E")
Cell.Offset(0, 5) = InputBox("Enter data", "Column F")
Cell.Offset(0, 6) = InputBox("Enter data", "Column G")
Cell.Offset(0, 7) = InputBox("Enter data", "Column H")

 

[Matt Pierringer]

You post is pretty confusing. Perhaps you can find something here:

Dim rng as range, cell as Range, bFound as Boolean
set rng = Range(cells(2,1),cells(rows.count,1).End(xlup)
bFound = False
for each cell in rng
If Application.Countif(range("MaterialNumbers"),cell) > 0 then
bFound = True
exit for
end if
Next
if bfound then exit sub
set cell = rng(rng.count + 1)
Cell.Offset(0, 1) = InputBox("Enter data", "Column B")
Cell.Offset(0, 2) = InputBox("Enter data", "Column C")
Cell.Offset(0, 3) = InputBox("Enter data", "Column D")
Cell.Offset(0, 4) = InputBox("Enter data", "Column E")
Cell.Offset(0, 5) = InputBox("Enter data", "Column F")
Cell.Offset(0, 6) = InputBox("Enter data", "Column G")
Cell.Offset(0, 7) = InputBox("Enter data", "Column H")

Thanks Tom, I am sorry about the confusing post... I wasn't exactly
sure how to word it. You got very close for the information I gave
you. The problem I am having now is when it the input box shows up it
will enter the value in the cell, and then I click Ok, but the same
input box pops up. I need to be able to test for a null value before
that pops up which I thought the following code would do the trick:

Dim rng As Range, cell As Range, bFound As Boolean
Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
bFound = False
For Each cell In rng
If Application.CountIf(Range("MaterialNumbers"), cell) > 0 Then
bFound = True
Exit For
End If
Next
If bFound Then Exit Sub
Set cell = rng(rng.Count + 1)
If IsNull(cell.Offset(-1, 1)) = True Then
cell.Offset(-1, 1) = InputBox("Enter data", "Column B")
End If
If IsNull(cell.Offset(-1, 2)) Then
cell.Offset(-1, 2) = InputBox("Enter data", "Column C")
End If
If IsNull(cell.Offset(-1, 3)) Then
cell.Offset(-1, 3) = InputBox("Enter data", "Column D")
End If
If IsNull(cell.Offset(-1, 4)) Then
cell.Offset(-1, 4) = InputBox("Enter data", "Column E")
End If
If IsNull(cell.Offset(-1, 5)) Then
cell.Offset(-1, 5) = InputBox("Enter data", "Column F")
End If
If IsNull(cell.Offset(-1, 6)) Then
cell.Offset(-1, 6) = InputBox("Enter data", "Column G")
End If
If IsNull(cell.Offset(-1, 7)) Then
cell.Offset(-1, 7) = InputBox("Enter data", "Column H")
End If


The code doesn't prompt any input boxes even though the cell does not
contain anything, also I am sure there is an easier way that doing all
of the If statements

 

[Guest]

isnull is pretty useless for testing whether a cell contains a value.

Instead use
isempty instead of isnull.

 

[Matt Pierringer]

isnull is pretty useless for testing whether a cell contains a value.

Instead use
isempty instead of isnull.

Thanks, yeah I kind of forget which vba commands work with which when
switching for Access to Excel and vice versa. Works great though,
pretty amazing that with my very unclear post you were still able to
pretty much figure out what I needed

-Matt

 

[Matt Pierringer]

Thanks, yeah I kind of forget which vba commands work with which when
switching for Access to Excel and vice versa. Works great though,
pretty amazing that with my very unclear post you were still able to
pretty much figure out what I needed

-Matt

Hmm, I am sorry to bother you again, but I have another spin off. I
need to be able to test the same way, but now I only want the user to
have to update Column 4 if Column 1 matches the list and Column 2 has
a value greater than 0. Will this work because they update Column 1
First then proceed to Column 2 if they need to enter it.

Dim rng As Range, cell As Range, bFound As Boolean
Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
bFound = False

For Each cell In rng
If Application.CountIf(Range("MaterialNumbers"), cell) > 0 And
cell.Offset(0, 1) > 0 Then
bFound = True
Exit For
End If
Next
If bFound Then Exit Sub
Set cell = rng(rng.Count + 1)
If IsEmpty(cell.Offset(-1, 3)) Then
cell.Offset(-1, 3) = InputBox("Enter Sell", "Sell")

End If

Does it have to loop through column 2 instead?
Thanks,
Matt

 

[Tom Ogilvy]

If Application.CountIf(Range("MaterialNumbers"), cell) > 0 And
cell.Offset(0, 1) > 0 Then


does the test you describe. I don't see a need for a separate loop in
column 2.

However, my logic did nothing if that if condition was statisfied anywhere
in the data in column 1 (and with our addtion column 2 as well for the same
row).

Your latest description sounds like you want to get input when it is met
however.

 

[Matt Pierringer]

If Application.CountIf(Range("MaterialNumbers"), cell) > 0 And
cell.Offset(0, 1) > 0 Then

does the test you describe. I don't see a need for a separate loop in
column 2.

However, my logic did nothing if that if condition was statisfied anywhere
in the data in column 1 (and with our addtion column 2 as well for the same
row).

Your latest description sounds like you want to get input when it is met
however.

Thats the same thing I had tried, but for some reason it doesn't
work... thats why I was wondering if I would have to loop through
column 2 instead or as well.