(VBA) Find rows that meet criteria and perform calculation

KK Wong

New Member
Joined
Dec 17, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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.

1613369213936.png
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,809
Office Version
  1. 2010
Platform
  1. Windows
try this:
VBA Code:
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

New Member
Joined
Dec 17, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
try this:
VBA Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,809
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 17, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 23, 2017
Messages
1,809
Office Version
  1. 2010
Platform
  1. Windows
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
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
 
Solution

KK Wong

New Member
Joined
Dec 17, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,809
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 17, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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".
 

Attachments

  • Solution.PNG
    Solution.PNG
    7.6 KB · Views: 2

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,809
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,252
Members
416,963
Latest member
samfuge

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top