PDA

View Full Version : [SOLVED] VBA to Verify Data



Nick72310
08-23-2016, 09:32 AM
I have a workbook with multiple sheets, anywhere from 500 to a few thousand lines. In column A of each sheet, I have a formula that verifies if data is correct or not (see image for more detail). This data in the workbook gets updated via SQL server each day so this is where the formula in column A comes in handy for me. Over time this workbook has become larger and larger, so now I am looking for an easy way of verifying each sheet with a macro button.

When a button is pressed, I want it either to tell me that I have incorrect data, or give me a list of the row numbers with incorrect data (When the cells formula equals 0 or -1).

16928

p45cal
08-23-2016, 10:55 AM
get your button to call:
Sub blah()
MsgBox IIf([COUNTIF(A:A,"<=0")> 0], "Not OK", "All OK")
End Sub
which isn't strictly the same as it will report Not OK if any cell has a value less than or equal to zero rather than your 'equals 0 or -1'.

p45cal
08-23-2016, 11:20 AM
To get you row info, have your button call:
Sub blah2()
Dim y()
Z = "All well": i = 0
Set Rng = Intersect(ActiveSheet.Columns(1), ActiveSheet.UsedRange)
tr = Rng.Row - 1
x = Rng.Value
ReDim y(1 To UBound(x))
For j = 1 To UBound(x)
If Not IsEmpty(x(j, 1)) And x(j, 1) <= 0 Then i = i + 1: y(i) = tr + j
Next j
If i > 0 Then ReDim Preserve y(1 To i): Z = "Problems on row(s): " & vbLf & Join(y, ", ")
MsgBox Z
End Sub

Nick72310
08-23-2016, 11:51 AM
Thank you p45al!
I modified the code just a bit to speed things up for me.


Sub Verify_Data()
k = 6
Dim wks(1 To 6) As String
'wks(1) = "Sheet1"
wks(2) = "Sheet2"
wks(3) = "Sheet3"
wks(4) = "Sheet4
wks(5) = "Sheet5"
wks(6) = "Sheet6"
For ii = 1 To k
If wks(ii) = "" Then ii = ii + 1
Set Rng = Intersect(Sheets(wks(ii)).Columns(1), Sheets(wks(ii)).UsedRange)
Z = wks(ii) & " has no issues.": i = 0
tr = Rng.Row - 1
x = Rng.Value
ReDim y(1 To UBound(x))
For j = 1 To UBound(x)
If Not IsEmpty(x(j, 1)) And x(j, 1) <= 0 Then i = i + 1: y(i) = tr + j
Next j
If i > 0 Then ReDim Preserve y(1 To i): Z = wks(ii) & " has issues on row(s): " & vbLf & Join(y, ", ")
If i <> 0 Then MsgBox Z

Next ii
MsgBox ("Verification Complete.")
End Sub

p45cal
08-23-2016, 12:31 PM
you need to reset i to 0 inside and at the beginning of the For ii = 1 to k loop.
(I know people use 'issues' a lot recently but I think that that word should be reserved for big things like Israel/Palestine, so-called IS, a country's deficit, starvation, malaria in Africa etc.)

Nick72310
08-23-2016, 01:17 PM
Ahh good catch. Thanks.