Macro Question

[goofy78270]

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.

 

[goofy78270]

Any ideas?

 

[erik.van.geit]

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]

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]

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:

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]

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

   LR = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row

 

[erik.van.geit]

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

 

[goofy78270]

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]

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]

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