VBA Help

[Donai]

I am after a macro that will return either "OK" or "ERROR" in D6:D if there is a difference between Previous and Current Count. The data does not have a fixed end range, it will change from day to day.

Excel Workbook
	A	B	C	D
5	Stock	Previous	Current	Check
6	TLS	12	12	OK
7	BHP	3	1	ERROR
8	NAB	45	45	OK
9	CBA	7	7	OK
10	MQB	23	23	OK
11	WES	8	8	OK
Sheet1

 

[Donai]

ok i have never done looping before, so can someone point me to the right direction, i get an error on ELSE

Sub Test()
Dim LastRow, i As Long
 
LastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 6 To LastRow
    If Cells(i, 3) <> Cells(i, 2) Then Cells(i, 4) = "ERROR"
 
    Else
        Cells(i, 4) = "OK"
    End If
 
 Next i
 
End Sub

 

[tusharm]

Why use VBA when a simple Excel IF function will suffice?

In D6 enter the formula =IF(B6<>C6,"Error","OK").

Now, move the cursor over the bottom-right corner of D6 and double-click. Excel will fill D as far down as you have data in C.

I am after a macro that will return either "OK" or "ERROR" in D6:D if there is a difference between Previous and Current Count. The data does not have a fixed end range, it will change from day to day.

Excel Workbook
	A	B	C	D
5	Stock	Previous	Current	Check
6	TLS	12	12	OK
7	BHP	3	1	ERROR
8	NAB	45	45	OK
9	CBA	7	7	OK
10	MQB	23	23	OK
11	WES	8	8	OK
Sheet1

 

[mirabeau]

try

Sub Test()
Dim LastRow, i As Long
 
LastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 6 To LastRow
    If Cells(i, 3) <> Cells(i, 2) Then
         Cells(i, 4) = "ERROR"
    Else
        Cells(i, 4) = "OK"
    End If
 
 Next i
 
End Sub

 

[Donai]

Why use VBA when a simple Excel IF function will suffice?

In D6 enter the formula =IF(B6<>C6,"Error","OK").

Now, move the cursor over the bottom-right corner of D6 and double-click. Excel will fill D as far down as you have data in C.

My data is not fixed therefore i dont want to use formula

 

[Donai]

Awesome, i was 99% there, just had to press enter on the "ERROR" part.

Thanks

try

Sub Test()
Dim LastRow, i As Long
 
LastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 6 To LastRow
    If Cells(i, 3) <> Cells(i, 2) Then
         Cells(i, 4) = "ERROR"
    Else
        Cells(i, 4) = "OK"
    End If
 
 Next i
 
End Sub

 

[mirabeau]

You could also do it like

Sub Test()
Dim LastRow, i As Long
LastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 6 To LastRow
If Cells(i, 3) <> Cells(i, 2) Then Cells(i, 4) = "ERROR" Else Cells(i, 4) = "OK"
Next i
End Sub

 

[Donai]

You could also do it like

Sub Test()
Dim LastRow, i As Long
LastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 6 To LastRow
If Cells(i, 3) <> Cells(i, 2) Then Cells(i, 4) = "ERROR" Else Cells(i, 4) = "OK"
Next i
End Sub

Great, i have learnt something new today. Thanks