View Full Version : Retention Report Automation
jdautel
09-09-2016, 08:45 AM
Hey guy's I'm working on creating an automated process of creating retention reports every month. I've been using VBA for about 2 months now and have no prior coding experience. This problem is proving a bit challenging though.
The way it works is like this, there's two columns (one is exported from QuickBooks, the other is from the last months retention report):
xx x
x y
y z
z a
a b
b c
c d
e ee
We want to make the columns match in alphabetical order and include all entries from each column. So it would look something like this:
x x
xx xx
y y
z z
a a
b b
c c
d d
e e
ee ee
I created a code to insert everything from the right column into the left column and it works. But it ignores what needs to be inserted from the left column into the right column, it also runs for freaking ever because it inserts cells down so many times it runs to the 17,000 line in excel (our retention report only has like 300 properties to give you an idea of how inefficient that is)
Option Explicit
Sub RetentionReport()
Dim cell As Range
For Each cell In Range("A2:A242")
If cell.Value = Range(cell.Address).Offset(0, 6).Value Then
Else
Range(cell.Address).Offset(0, 6).Copy
cell.Insert shift:=xlDown
End If
Next cell
End Sub
Any ideas?
Any ideas? As soon as the first insert occurs, there will be no more matching cells so every x cell gets inserted into the xx column.
Issues:
Even though both lists are sorted alphabetically, they are not paired up.
x and xx do not correlate to left and right, like Cell and Cell.Offset(, 6) do. I will assume that x = Cell and xx = the offset.
You haven't said which list is complete. I will assume that both lists contain some values that the other does not.
Assuming that you want Column A to be the complete list: Presorting is not needed.
Sub AddToA()
Dim Cel As Range
Dim Found As Range
'Uncomment next line after testing
'Application.ScreenUpdating = False
For Each Cel in Range(Range("G2"), Cells(Rows.Count, "G").End(xlUp))
Set Found = Range("A:A").Find(Cel)
If Found Is Nothing Then Cells(Row.Count, "A").End(xlUp).Offset(1) = Cel
Next Cel
Application.ScreenUpdating = True
End Sub
jdautel
09-09-2016, 10:26 AM
Hmm, I tried this and it didn't return anything
Can you explain what's going on in it?
I understand that for each cell in the entire G range it is setting the variable found equal to something in range a, I don't understand what the find(cell) function is doing. I also don't understand what the
"If Found is Nothing Then Cells(Row.Count, "A").end(xlup).offset(1)=Cell"
does.
Option Explicit
Sub addtoa()
Dim cell As Range
Dim found As Range
Application.ScreenUpdating = False
For Each cell In Range(Range("G2"), Cells(Rows.Count, "G").End(xlUp))
Set found = Range("A:A").Find(cell)
If found Is Nothing Then
Cells(Rows.Count, "A").End(xlUp).Offset(1) = cell
End If
Next cell
Application.ScreenUpdating = True
End Sub
It doesn't matter which column is updated as long as one of them is updated correctly to put into our retention report. I've also figured out a possibly easier way of doing it: Cutting all the values in column G and pasting them below the values in column A. Then sorting column A alphabetically. Then running a macro removing all duplicate values in column A. That should give us the right answer as well.
I appreciate your help!
Cutting all the values in column G and pasting them below the values in column A. Then sorting column A alphabetically.
Then Excel Menu Data >> Filter >> Advanced Filter >. Filter in place, Unique records only.
I understand that for each cell in the entire G range it is setting the variable found equal to something in range a, I don't understand what the find(cell) function is doing. I also don't understand what the
"If Found is Nothing Then Cells(Row.Count, "A").end(xlup).offset(1)=Cell"
does.
.Find looks for a cell in A with the Value of the "Cell" variable. If it finds one, it sets The Range variable "Found" to be that "A" cell. Not the "A" cell value, but the "A" cell itself. If Found is nothing, then it didn't find a matching value.
Cells(Row.Count, "A") is the cell at the very bottom of the sheet in Column "A".
.End(xlUp) is the same as selecting a cell and pressing Ctrl+ Arrow Up.
.Offset(1) is the cell below the selection
Cells(Row.Count, "A").end(xlup).offset(1)
Means start in the bottom cell of the Column, go up till the first non-empty cell and select the cell under that.
"= Cell" Means make its value the same as the working cell in G.
.Find looks for a Cell (Range) that contains the value searched for. A Search for "B" will find "AbC"
Setting any Variable Type other than a Range with Find, will Raise an Error if the Value is not found.
Works sometimes, but fails Drastically if "xyzABCuvw" is not present
Dim X as String
X = Range("A:A").Find("abc").Value
Find is Case insensitive.
Find defaults to looking at parts of values, can be made to lookat the whole value.
jdautel
09-09-2016, 02:46 PM
So
if found=nothing
is saying if that cell isn't found in column A
then it will put that cell value at the BOTTOM of column A? And then you can just sort accordingly?
So
if found=nothing
is saying if that cell isn't found in column A
then it will put that cell value at the BOTTOM of column A? And then you can just sort accordingly?
Yes.
jdautel
09-12-2016, 09:04 AM
My final code working beautifully. Thanks Sam!
Option Explicit
Sub automatedreport()
Dim cell As range
Dim found As range
Application.ScreenUpdating = False
For Each cell In range(range("C2"), cells(Rows.Count, "C").End(xlUp))
'determine the size of range C and look at each cell within that range
Set found = range("A:A").Find(cell)
'search to check if each cell in range C2:Cwhatever equals any cell in range A
If found Is Nothing Then
'if the cell value can’t be find in x then copy that value to the bottom of column A
cells(Rows.Count, "A").End(xlUp).Offset(1) = cell
End If
Next cell
'Sort column A alphabetically
range("A1") = "Index"
Columns("A:A").Sort key1:=range("A2"), order1:=xlAscending, Header:=xlYes
For Each cell In range(range("A2"), cells(Rows.Count, "A").End(xlUp))
'set the range for A to do a vlookup for new monthly contract values
cell.Offset(0, 1) = Application.VLookup(cell.Value, range("C2:D300"), 2, False)
Next cell
'if there’s a property that cancelled it will have a #N/A value, this replaces all NA’s with a 0
cells.Replace "#N/A", 0, xlWhole
'if there’s any value with a “” value then replace that with a 0 as well
For Each cell In range(range("A2"), cells(Rows.Count, "A").End(xlUp))
If cell.Offset(0, 1) = "" Then
cell.Offset(0, 1) = 0
End If
Next cell
Application.ScreenUpdating = True
End Sub
It looks as if columns(C:D) are identicel to columns(A:B). What's the point ?
jdautel
09-13-2016, 06:50 AM
Not quite snb. You can try the code plugged into a worksheet but basically I am taking all cell values from column C that are unique from column A and adding them to column A. Then I am sorting alphabetically. Next I am using a vlookup to find all the values in column D and placing them with the corresponding column A cell in column B. If it can't find that value in column C, it puts in a 0.
So neither column A or Column C will hold the same values but any value in column C will be included in column A after the macro is run; however, not any value in column A will be included in column C.
jdautel
09-13-2016, 06:50 AM
The best way is to just run the code yourself as an example. I'm terrible at explaining haha
But it still don't see the point in doing this.
Next I am using a vlookup to find all the values in column D
If found Is Nothing Then _
cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(,2) = Cell.Resize(,2)
Next Cell
'Sort A:B
End Sub
jdautel
09-13-2016, 07:36 AM
It's adding the new values from column C into Column A
Then it's sorting
Then it's finding the new values of monthly income from each.
This way it includes everything from column C and column A
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.