VBA to find the first value in a column greater than or equal to 1, then deduct a value from that cell

[Mr2017]

Hi

Is there a way to use VBA to find the first value in a column that's greater than or equal to 1, then deduct a value from that cell?

Eg I have a list of Numbers in column D in Sheet 1.

In the simple example below, the title 'Numbers' is in cell D1, and the values 0, 1, 2, and 3 are in cells D2, D3, D4 and D5, respectively.

I'd like to run a Sub which finds the first value greater than or equal to 1 then deducts the value in cell A1 in Sheet 2 from the cell found in column D in Sheet 1.

In this case, the value in cell A1 in Sheet 2 is 1.

I'm sure this is possible, but would be grateful for your thoughts?

TIA

			Numbers
			0​
			1​
			2​
			3​

 

[tico_ocit]

Try this.

Sub findone()

Dim lastrow_1 As Integer
Dim varray As Variant
Dim a As Long

lastrow_1 = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
varray = Sheets("Sheet1").Range("D1:D" & lastrow_1).Value

For a = 1 To UBound(varray, 1)
    If varray(a, 1) >= 1 Then
        ActiveSheet.Cells(a, 4).Value = ActiveSheet.Cells(a, 4).Value - Sheets("Sheet2").Range("A1").Value
        Exit Sub
    Else
    End If
Next
End Sub

 

[Mr2017]

Hi @tico_ocit

Thanks for the prompt response to this question.

I tried to run the code above, but I got a 'Run time error: 13. Type mis-match'

And the code stopped at this line:

ActiveSheet.Cells(a, 4).Value = ActiveSheet.Cells(a, 4).Value - Sheets("Sheet2").Range("A1").Value

Do you know why that may be?

Thanks in advance.

 

[Fluff]

Do you have a header in D1?
If so try

For a = 2 To UBound(varray, 1)

 

[Mr2017]

Yes, there was already a header in D1.

And yes, that change to the code you suggested above made it work!

Amazing!!!

Thank you both!!

 

[Mr2017]

Thanks @tico_ocit and @Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.