| 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation Testing through list
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. -- Regards, Tom Ogilvy "Matt Pierringer" wrote in message ups.com... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation Testing through list
On Mar 12, 7:46 pm, "Tom Ogilvy" wrote:
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. -- Regards, Tom Ogilvy "Matt Pierringer" wrote in message ups.com... 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 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. |
| 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 | |||
Linear Mode
