VBA error message that notes row number

[ratchet1369]

Hey all,

I had a VBA code that was working. It would look for an error condition when Ledger = ABC and Company = V3 and would prompt a message box advising the user "Error on row 20".

They now want to look for company V3 or V4 so I changed the IF into a Case to look for multiple items and now my rowcounter says "Error on row 70", which is the end of the range instead of telling me row 20, which is where the error exists.

Old Code
For rowCounter1 = vCompanyCell.Row To TotalsCell.Row - 1
If (CStr(ws.Cells(rowCounter1, vCompanyCell.Column)) = "V3" And vLedger = "ABC") Then
vResponseCode = -2
gCompanyMsg = "- [Error] Do not use V3 in combination with ABC Ledger, see row " & rowCounter1
End If


New Code

For rowCounter1 = vCompanyCell.Row To TotalsCell.Row - 1
If vLedger = "ABC" Then
Select Case compvalues
Case CStr(ws.Cells(rowCounter1, vCompanyCell.Column)) = "V3", "V4": vResponseCode = -2
End Select
gCompanyMsg = "- [Error] Invalid Company for ABC Ledger, see row " & rowCounter1

End If

Any suggestions on how to direct the user to the row in error?

 

[Fluff]

How about

Select Case CStr(Ws.Cells(rowCounter1, vCompanyCell.Column))
Case "V3", "V4": vResponseCode = -2
End Select

 

[ratchet1369]

I'm still getting row 70 in my message box. It's as though it's not stopping when there's an error and noting the error row but going to the last row and reporting that one.

 

[Fluff]

That sounds as though the first instance of ABC is on row 70.
You may also need to move this line

gCompanyMsg = "-   [Error] Invalid Company for ABC Ledger, see row " & rowCounter1

Into the Select case

 

[ratchet1369]

Figured it out. I had to nest the Cases. Row 70 was the end of my range and with ABC being true, it was showing the last cell instead of the cell that met both criteria. I went with the below solution. Works. Unsure if it's best

Select Case vLedger
Case "ABC"
Select Case CStr(ws.Cells(rowCounter1, vCompanyCell.Column))
Case "V3", "V4": vResponseCode = -2
gCompanyMsg = "- [Error] Invalid Company for ABC Ledger, see row " & rowCounter1
End Select
End Select

Not the Message Box returns row 20 as desired.

 

[Fluff]

Glad you figured it out & thanks for the feedback