| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation Testing through list
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation Testing through list
You post is pretty confusing. Perhaps you can find something he
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") -- Regards, Tom Ogilvy "Matt Pierringer" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation Testing through list
On Mar 12, 8:01 am, Tom Ogilvy
wrote: You post is pretty confusing. Perhaps you can find something he 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") -- Regards, Tom Ogilvy "Matt Pierringer" wrote: 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 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation Testing through list
isnull is pretty useless for testing whether a cell contains a value.
Instead use isempty instead of isnull. -- Regards, Tom Ogilvy "Matt Pierringer" wrote: On Mar 12, 8:01 am, Tom Ogilvy wrote: You post is pretty confusing. Perhaps you can find something he 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") -- Regards, Tom Ogilvy "Matt Pierringer" wrote: 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 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation Testing through list
On Mar 12, 10:43 am, Tom Ogilvy
wrote: isnull is pretty useless for testing whether a cell contains a value. Instead use isempty instead of isnull. -- Regards, Tom Ogilvy "Matt Pierringer" wrote: On Mar 12, 8:01 am, Tom Ogilvy wrote: You post is pretty confusing. Perhaps you can find something he 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") -- Regards, Tom Ogilvy "Matt Pierringer" wrote: 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 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 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation Testing through list
On Mar 12, 12:10 pm, "Matt Pierringer" wrote:
On Mar 12, 10:43 am, Tom Ogilvy wrote: isnull is pretty useless for testing whether a cell contains a value. Instead use isempty instead of isnull. -- Regards, Tom Ogilvy "Matt Pierringer" wrote: On Mar 12, 8:01 am, Tom Ogilvy wrote: You post is pretty confusing. Perhaps you can find something he 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") -- Regards, Tom Ogilvy "Matt Pierringer" wrote: 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 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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
| Testing for Cell Validation | Excel Programming | |||
| Validation (Drop down list vs simple text length validation) | Excel Programming | |||
| Validation (Drop down list vs simple text length validation) | Excel Programming | |||
| Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Hybrid Mode
