Macro Question

goofy78270

Well-known Member
Joined
May 16, 2007
Messages
555
Just as a warning, I am looking to do something weird. I currently have a worksheet that I track pricing changes in. From month to month, I highlight changes for different products based on the investor within a specific color so a second party can ensure that all changes made are correct..

What I would like to do is to have the sheet look at the first adjustment within the list and copy the same values down for each adjustment after that. This isnt so bad seeing as how I can simply say the such and such = the first cell.

The tricky part comes in that I would first like to have the formats copied down to all the following adjustments along with the values.
The second tricky part comes with if the first product is deleted due to whatever reason, I would like to account for this and have the sheet set the next in line as the top and have all others follow it.

I hope that this makes sense. If not, maybe I can upload a workbook for you to see.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,


Seems unclear to me.
Can you clarify with an example "in progress", so in fact 3 or 4 little tables + explanation
something like
1. before
2. adjustments
3. expected results

tools to display tables: colo's HTMLmaker (see bottom page) or "Table-It" (see my signature)

kind regards,
Erik

personal opinion: I hate to download or open external documents. Also the thread becomes worthless when file is gone.
 

goofy78270

Well-known Member
Joined
May 16, 2007
Messages
555
Table 1 - before

   A       B          C                D                                             
 1 Product Empower ID Rate Sheet Order Description                                   
 2 C00     200        1                Escrow Waiver                                 
 3 C00     27         2                LTV 65.01-95.00% & CLTV 90.01-95.00%          
 4 C01     23         1                40 Year Amortization                          
 5 C02     200        1                Escrow Waiver                                 
 6 C02     125        2                Investment Property & LTV <=75.00%            
 7 C02     133        3                Investment Property & LTV 75.01-80.00%        
 8 C02     141        4                Investment Property & LTV 80.01-90.00%        
 9 C02     27         5                LTV 65.01-95.00 &CLTV90.01-95-C02/C08/C18     
10 C02     35         6                LTV 70.01-80.00% & Cash Out Refi-C02/C18 only 
11 C02     49         7                LTV 80.01-85.00% & Cash Out Refi-C02/C18 only 
12 C02     59         8                LTV 85.01-90.00% & Cash Out Refi-C02/C18 only 

Price Adjustments

[Table-It] version 06 by Erik Van Geit


Table 2 - Adjustments
   A          B                                             
 1 Empower ID Description                                   
 2 23         40 Year Amortization                          
 3 27         LTV 65.01-95.00% & CLTV 90.01-95.00%          
 4 35         LTV 70.01-80.00% & Cash Out Refi-C02/C18 only 
 5 49         LTV 80.01-85.00% & Cash Out Refi-C02/C18 only 
 6 59         LTV 85.01-90.00% & Cash Out Refi-C02/C18 only 
 7 125        Investment Property & LTV <=75.00%            
 8 133        Investment Property & LTV 75.01-80.00%        
 9 141        Investment Property & LTV 80.01-90.00%        
10 200        Escrow Waiver                                 

Sheet1

[Table-It] version 06 by Erik Van Geit

Table 3 - After
   A       B          C                D                                             
 1 Product Empower ID Rate Sheet Order Description                                   
 2 C00     200        1                Escrow Waiver                                 
 3 C00     27         2                LTV 65.01-95.00% & CLTV 90.01-95.00%          
 4 C01     23         1                40 Year Amortization                          
 5 C02     200        1                Escrow Waiver                                 
 6 C02     125        2                Investment Property & LTV <=75.00%            
 7 C02     133        3                Investment Property & LTV 75.01-80.00%        
 8 C02     141        4                Investment Property & LTV 80.01-90.00%        
 9 C02     27         5                LTV 65.01-95.00 &CLTV90.01-95-C02/C08/C18     
10 C02     35         6                LTV 70.01-80.00% & Cash Out Refi-C02/C18 only 
11 C02     49         7                LTV 80.01-85.00% & Cash Out Refi-C02/C18 only 
12 C02     59         8                LTV 85.01-90.00% & Cash Out Refi-C02/C18 only 

Sheet2

[Table-It] version 06 by Erik Van Geit


I would like to update table 2 once and have it feed table 1 so that all changes made to table 2 are mimiced to table 1 as seen in table 3
Note: This should be highlighting and not text color that references the changes but I hope you get the idea.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hi,

So basically you are checking the "Empower ID" in Table 2. If the cell background is not the default, color all rows with same "Empower ID" accordingly.

I would suggest to use autofilter to make the code quick. Try this:
Code:
Option Explicit

Sub test()
Dim LR As Long
Dim EmpowerID As Range
Dim cnt As Long

    With Sheets("Price Adjustments")
    .Cells.Interior.ColorIndex = xlNone
    
    LR = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    
        On Error Resume Next
        
        For Each EmpowerID In Sheets("sheet1").Range("A2:A" & Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
            If EmpowerID.Interior.ColorIndex <> xlNone Then
            .Range("B1:B" & LR).AutoFilter Field:=1, Criteria1:=EmpowerID
            cnt = 0
            cnt = .Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).Count
            If cnt Then Intersect(.Range("B2:B" & LR).EntireRow, .Columns("A:D")).Interior.ColorIndex = EmpowerID.Interior.ColorIndex
            End If
        Next EmpowerID
        
    .AutoFilterMode = False
    
    End With

End Sub
kind regards,
Erik
 

goofy78270

Well-known Member
Joined
May 16, 2007
Messages
555
There are also other cells within the row that i would like to copy over such as comments, old price, new price, and such. Whould I add these cells = the cells from price adjustment(Sheet 2) before the cnt = 0 line?

Also, Is the Empower ID defined as a range?

Last, Could you explain what you are doing in the find
Code:
   LR = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

could you first provide feedback before asking additional questions?
does it work for you too?
 

goofy78270

Well-known Member
Joined
May 16, 2007
Messages
555
Yes, this works. I had to try and figure out a couple of things like the field of the autofilter. Since I filter the whole top row, I had to change the value to 2 instead of 1.

Although I figured out the jist of the routine, I am still unsure what the find is actually doing, to me, it is simply getting a row count. Is that correct?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
the FIND line is finding the last cells row
it might be interesting to lookup the VBAhelp to see the entire syntax explained

best regards,
Erik
 

goofy78270

Well-known Member
Joined
May 16, 2007
Messages
555
After further review of this code, it does not seem to copy the cells across, I believe this is because it was not mentioned earlier.

How would I go about adding in the copy of the cells from worksheet 2 to worksheet 1 without overwriting the product code in column A? I would like to Columns B:F from worksheet 1 to Columns C:G
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

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