(VBA) Find rows that meet criteria and perform calculation

[KK Wong]

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.

 

[offthelip]

try this:

Sub test()


lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 3))
Workbooks.Add
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
' copy headers
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]

try this:

Sub test()


lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 3))
Workbooks.Add
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
' copy headers
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]

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

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]

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

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]

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

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
Workbooks.Add ' add a workbook
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
' copy headers
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]

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

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
Workbooks.Add ' add a workbook
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
' copy headers
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]

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]

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]

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 :

Dim inarr () as variant

You don't need to define the size that will happen uatomatically when you load a range into it.