VBA: To add validation to enter required data

[Stranger8421]

Hey Guys,

Need help. I need to add validation. For example:- If Cust_info is provided they should be one cust_id for that id or else should throw error message D column.

As per the given sheet

Id	Cust_id	Cust_info	Errors
1​	abb	212​
		322​
2​
		215​
3​	abc
		233​
4​
		344​
	sds	323​

Senarios :- 1. For ID 1 (a2) for the Cust_info 212 (C2) and 322 (C3) have abb(b2) as Cust_id so no errors.
2. For ID 2 (a4) for the cust_info 215(C5) the cust_id is not provided either in B4 and B5 So error should be thrown
3. For ID 3 (a6) for the cust_info 233(C7) the cust_id is provided in B6 So no error.
4. For ID 4 (a8) for the cust_info 344(C5) the cust_id is not provided either in B8 and B9 So error should be thrown. and next cust_info 323 the cust_id is provided.

Final should be like

Id	Cust_id	Cust_info	Errors
1​	abb	212​
		322​
2​
		212​	please provide cust_id for this cust_info
3​	abc
		233​
4​
		344​	please provide cust_id for this cust_info
	sds	323​

My Code till rows selection : -

Dim lastrowFinal As Long, rng As Range, i As Long

lastrowFinal = ws1.Range("A:M").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


For i = 4 To lastrowShipSpot

If Cells(i, "A") <> "" Then

rng = Cells(i, "A").End(xlDown).Row - 1

If Cells(i + 1, "A") <> "" Then rng = i
If rng > 10000 Then rng = lastrowShipSpot

------need to code here -------

End If
End If
Next i

Thanks alot for seeing my thread.
Regards,
Joe

 

[Stranger8421]

Hey Guys,

Need help. I need to add validation. For example:- If Cust_info is provided they should be one cust_id for that id or else should throw error message D column.

As per the given sheet

Id	Cust_id	Cust_info	Errors
1​	abb	212​
		322​
2​
		215​
3​	abc
		233​
4​
		344​
	sds	323​

Senarios :- 1. For ID 1 (a2) for the Cust_info 212 (C2) and 322 (C3) have abb(b2) as Cust_id so no errors.
2. For ID 2 (a4) for the cust_info 215(C5) the cust_id is not provided either in B4 and B5 So error should be thrown
3. For ID 3 (a6) for the cust_info 233(C7) the cust_id is provided in B6 So no error.
4. For ID 4 (a8) for the cust_info 344(C5) the cust_id is not provided either in B8 and B9 So error should be thrown. and next cust_info 323 the cust_id is provided.

Final should be like

Id	Cust_id	Cust_info	Errors
1​	abb	212​
		322​
2​
		212​	please provide cust_id for this cust_info
3​	abc
		233​
4​
		344​	please provide cust_id for this cust_info
	sds	323​

My Code till rows selection : -

Dim lastrowFinal As Long, rng As Range, i As Long

lastrowFinal = ws1.Range("A:M").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


For i = 4 To lastrowShipSpot

If Cells(i, "A") <> "" Then

rng = Cells(i, "A").End(xlDown).Row - 1

If Cells(i + 1, "A") <> "" Then rng = i
If rng > 10000 Then rng = lastrowShipSpot

------need to code here -------

End If
End If
Next i

Thanks alot for seeing my thread.
Regards,
Joe

Update code ;

Dim lastrowFinal As Long, rng As Range, i As Long

lastrowFinal = ws1.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


For i = 2 To lastrowShipSpot

If Cells(i, "A") <> "" Then

rng = Cells(i, "A").End(xlDown).Row - 1

If Cells(i + 1, "A") <> "" Then rng = i
If rng > 10000 Then rng = lastrowShipSpot

------need to code here -------

End If
End If
Next i