# (VBA) Find rows that meet criteria and perform calculation

#### KK Wong

##### New Member
Hello there, I have a list of companies with corresponding revenue in 2019 and 2020. There are some companies with 0 revenue, and some with blank cell. My goal is to use VBA to identify all the companies with revenue > 0 in 2019 or/and 2020. And then compare the revenue in both years. Then, paste the result (which contains 1) company name, 2) revenue in 2019, 3) revenue in 2020, and 4) the revenue difference) to another workbook. However, I am not sure how to do it. I suppose I need to save it to a collection?

Take the picture below for example, Row 10, 18, 25-27, 31, 34, 39, 42-43, should be identified, because they have value in 2019 or/and 2020. Then, in this collection of rows, calculate the difference (i.e. 2020 revenue - 2019 revenue). Finally, I would like to paste the result to another sheet. ### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### offthelip

##### Well-known Member
try this:
VBA Code:
``````Sub test()

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 3))
outarr = Range(Cells(1, 1), Cells(lastrow, 3))
indi = 7
For i = 7 To lastrow
If inarr(i, 2) > 0 Or inarr(i, 3) > 0 Then
For j = 1 To 3
outarr(indi, j) = inarr(i, j)
Next j
indi = indi + 1
End If
Next i
For i = 1 To 6
For j = 1 To 3
outarr(i, j) = inarr(i, j)
Next j
Next i
Range(Cells(1, 1), Cells(lastrow, 3)) = outarr

End Sub``````

#### KK Wong

##### New Member
try this:
VBA Code:
``````Sub test()

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 3))
outarr = Range(Cells(1, 1), Cells(lastrow, 3))
indi = 7
For i = 7 To lastrow
If inarr(i, 2) > 0 Or inarr(i, 3) > 0 Then
For j = 1 To 3
outarr(indi, j) = inarr(i, j)
Next j
indi = indi + 1
End If
Next i
For i = 1 To 6
For j = 1 To 3
outarr(i, j) = inarr(i, j)
Next j
Next i
Range(Cells(1, 1), Cells(lastrow, 3)) = outarr

End Sub``````
Hello, thank you for your input. I can understand your codes, but when I run the macro, there is only one new workbook with blank output. How should I output the result?

#### offthelip

##### Well-known Member
The last line should write the data out to the workbook. I suggest putting a breakpoint on the line:

VBA Code:
``For j = 1 To 3``
and check that the if statement is getting triggered.
Can you post the actual code that you have got

#### KK Wong

##### New Member

The last line should write the data out to the workbook. I suggest putting a breakpoint on the line:

VBA Code:
``For j = 1 To 3``
and check that the if statement is getting triggered.
Can you post the actual code that you have got
Hi! The if statement is triggered. What do you mean by the actual code? I just copied your code and ran it. The run is smooth, but after the run, nothing comes out except a new workbook with no result.

#### offthelip

##### Well-known Member
I have annotated the code with comments. I sugest you use debug to check on a step by stpe basis what values you are getting. Have a look at inarr and outarr on the locals window to check what is in them. Check what lastrow value is
VBA Code:
``````Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' find the last row in column A
inarr = Range(Cells(1, 1), Cells(lastrow, 3)) ' load columns A , B and C into a variant array for input data
outarr = Range(Cells(1, 1), Cells(lastrow, 3)) ' load varaint array outarr with a blank values, same size as the inut data
indi = 7 ' set the start row for copying
For i = 7 To lastrow ' setup loop from the start row to the lastrow
If inarr(i, 2) > 0 Or inarr(i, 3) > 0 Then ' check if col B or col C in the input dat is greater than 0
For j = 1 To 3 ' if so copy all three columns to the output
outarr(indi, j) = inarr(i, j) ' copying input data in variant array to the ouput array
Next j
indi = indi + 1  ' increment the output data  index to the next row
End If
Next i
For i = 1 To 6
For j = 1 To 3
outarr(i, j) = inarr(i, j)
Next j
Next i
Range(Cells(1, 1), Cells(lastrow, 3)) = outarr ' write the output array bac to the worksheet/

End Sub``````

#### KK Wong

##### New Member

I have annotated the code with comments. I sugest you use debug to check on a step by stpe basis what values you are getting. Have a look at inarr and outarr on the locals window to check what is in them. Check what lastrow value is
VBA Code:
``````Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' find the last row in column A
inarr = Range(Cells(1, 1), Cells(lastrow, 3)) ' load columns A , B and C into a variant array for input data
outarr = Range(Cells(1, 1), Cells(lastrow, 3)) ' load varaint array outarr with a blank values, same size as the inut data
indi = 7 ' set the start row for copying
For i = 7 To lastrow ' setup loop from the start row to the lastrow
If inarr(i, 2) > 0 Or inarr(i, 3) > 0 Then ' check if col B or col C in the input dat is greater than 0
For j = 1 To 3 ' if so copy all three columns to the output
outarr(indi, j) = inarr(i, j) ' copying input data in variant array to the ouput array
Next j
indi = indi + 1  ' increment the output data  index to the next row
End If
Next i
For i = 1 To 6
For j = 1 To 3
outarr(i, j) = inarr(i, j)
Next j
Next i
Range(Cells(1, 1), Cells(lastrow, 3)) = outarr ' write the output array bac to the worksheet/

End Sub``````

Thank you for your explanation. I just checked the code. The lastrow and inarr are fine.

I think the problem should be the outarr. I checked the initial values of the outarr. It was identical to those of the inarr. If I am understanding it correctly, the VBA is being processed all through one workbook only. I suppose the outarr values should be loaded in the newly added workbook so as to load blank values?

#### offthelip

##### Well-known Member
It sounds as though the after the workbook add instruction , you still have the original workbook selected. When I run this code on my machine, after the workbook add code the new workbook is selected on sheet1
I suggest you put a breakpoint after the workbook add and run the code to that point, then look to see what workbook and sheet is active in the excel window.

#### KK Wong

##### New Member
It sounds as though the after the workbook add instruction , you still have the original workbook selected. When I run this code on my machine, after the workbook add code the new workbook is selected on sheet1
I suggest you put a breakpoint after the workbook add and run the code to that point, then look to see what workbook and sheet is active in the excel window.
Hello, I finally figured out why it happened. I pasted the code in the specific worksheet ("Sheet1"). I should have pasted it in "ThisWorkbook" instead. Thank you!

I have a follow-up question. If I would like to keep Option Explicit, how do I write to dim the inarr and outarr as? To my understanding, it is an array of string, dobule, and double. E.g. For lastrow, it is "Dim lastrow as Integer".

#### offthelip

##### Well-known Member
Hello, I finally figured out why it happened. I pasted the code in the specific worksheet ("Sheet1"). I should have pasted it in "ThisWorkbook" instead. Thank you!
I hadn't thought about that, I find it is usually best to put all code into a standard module unless there is a psecific reason to put it in a worksheet or workbook . As you have found out, putting in the worksheet stops certain things working.
how do I write to dim the inarr and outarr as? To my understanding, it is an array of string, dobule, and double.
You have basically answered the question, for something to hold a string and a double it must a variant type, and as you said is it an array so :
VBA Code:
``Dim inarr () as variant``
You don't need to define the size that will happen uatomatically when you load a range into it.

• KK Wong

Replies
3
Views
84
Replies
1
Views
72
Replies
3
Views
192
Replies
6
Views
267
Replies
8
Views
198

### Forum statistics

1,129,404
Messages
5,636,088
Members
416,897
Latest member
YAFI ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back