How to make VBA offset and speed up coding

[Toyo613]

Dear Excel Gods,



I've pieced together this code from information I have found online and the code works perfectly right now. I originally wanted to make this a user form where it would simply add the values to the next free column but that may be out of my league.

To help get this project over with I'd like to make this code work for multiple columns with out copying and pasting the code. I think this would be called doing "offset"?

Right now it works for P1 down.

If D1 = True, then do the code below for columns D:E
If F1 = True, then do the code below for columns F:G
If H1 = True, then do the code below for columns H:I
If J1 = True, then do the code below for columns J:K
ect

I'm VERY new to VBA so please be detailed in your response.


Thank you!

       
If .Address = "$P$1" Then
If LCase(.Value) = "true" Then

' --------------------Beginning of Formatting Section--------------------

Range("P8:Q12,P14:Q16,P22:Q29").Merge Across:=True

   With Range("P8:Q12,P14:Q16,P22:Q29")
        .Borders.LineStyle = xlNone
        For Each v In Array(xlEdgeTop, xlInsideHorizontal, xlEdgeBottom, xlEdgeLeft, xlEdgeRight)
            With .Borders(v)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        Next v
    End With
    
    
      With Range("P17:Q21")
        .Borders.LineStyle = xlNone
        For Each v In Array(xlEdgeLeft, xlEdgeRight, xlInsideVertical)
            With .Borders(v)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        Next v
         For Each v In Array(xlInsideHorizontal)
            With .Borders(v)
                .LineStyle = xlDot
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        Next v
    End With


With Range("P8:P9")
    .Font.Bold = True
    .Interior.Color = RGB(192, 192, 192)
End With


With Range("P10:P11")
    .Font.Bold = True
    .Interior.Color = RGB(204, 255, 204)
End With


With Range("P12")
    .Font.Bold = True
    .Interior.Color = RGB(146, 206, 220)
End With


With Range("P8:Q29")
.HorizontalAlignment = xlCenter
.Font.Name = "Arial"
.Font.Size = 9
End With


' --------------------End of Formatting Section--------------------


' --------------------Beginning of Values Section--------------------


                

With Range("P8").Validation
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
 Formula1:="=SourceType"
 .IgnoreBlank = True
 .InCellDropdown = True
 End With
 
 Range("P8").Formula = "Export"
 
 
 With Range("P9").Validation
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
 Formula1:="=UDB"
 .IgnoreBlank = True
 .InCellDropdown = True
 End With

                Range("P9").Formula = "TCI"

            With Range("P14")
                .Formula = "100"
                .NumberFormat = "$#,##0.00;-$#,##0.00"
                .Font.Bold = True
                .Interior.Color = RGB(204, 255, 204)
            End With
              
            With Range("P15")
                .Formula = "=VLOOKUP(P9,Conversion,3,FALSE)"
                .NumberFormat = "0.00"
                .Interior.Color = RGB(204, 255, 204)
            End With
              
              
            With Range("P16")
                .Formula = "=P14*P15"
                .NumberFormat = "$#,##0.00;-$#,##0.00"
                .Interior.Color = RGB(255, 255, 67)
                .Font.Bold = True
            End With
              

                
                Range("P17").Formula = "0.01%"
                Range("P18").Formula = "3.36%"
                Range("P19").Formula = "3.00%"
                Range("P20").Formula = ".50%"
                Range("P21").Formula = "6.00%"
                
                
            With Range("Q17:Q21")
                .NumberFormat = "$#,##0.00;-$#,##0.00"
            End With
                

                Range("Q17").Formula = "=$P$15*P16"
                Range("Q18").Formula = "=$P$15*P17"
                Range("Q19").Formula = "=$P$15*P18"
                Range("Q20").Formula = "=$P$15*P19"
                Range("Q21").Formula = "=$P$15*P20"
                
                
            With Range("P22")
                .Formula = "=SUM(Q17:Q21)+P15"
                .NumberFormat = "$#,##0.00;-$#,##0.00"
                .Interior.Color = RGB(255, 255, 67)
                .Font.Bold = True
            End With
            
          With Range("P23")
                .Formula = "=$N$5"
                .NumberFormat = "$#,##0.00;-$#,##0.00"
            End With
                
            With Range("P24")
                .Formula = "=(P26-(P22+P23))/P26"
                .NumberFormat = "0.00%"
                .Interior.Color = RGB(255, 255, 67)
                .Font.Bold = True
            End With
            
            
            
             With Range("P25")
                .Formula = "=P26-P22-P26"
                .NumberFormat = "$#,##0.00;-$#,##0.00"
            End With
            
            
            With Range("P26")
                .Formula = "100"
                .NumberFormat = "$#,##0.00;-$#,##0.00"
                .Font.Bold = True
                .Interior.Color = RGB(204, 255, 204)
            End With
            
            With Range("P27")
                .Formula = "=(P29-P26)/P29"
                .NumberFormat = "0.00%"
                .Interior.Color = RGB(255, 255, 67)
                .Font.Bold = True
            End With
            
            
            With Range("P28")
                .Formula = "=P29-P26"
                .NumberFormat = "$#,##0.00;-$#,##0.00"
            End With

            With Range("P29")
                .Formula = "=VLOOKUP(P9,Conversion,3,FALSE)*P26"
                .NumberFormat = "$#,##0.00;-$#,##0.00"
            End With
  
             
            Else
 
                Range("P8:P9").Validation.Delete ' replace with the correct formula
                Range("P8:P29").Formula = ""
                Range("Q17:Q21").Formula = ""
                Range("P46:P69").Formula = ""
                Range("P8:Q29").ClearFormats ' clears all formating

        
End If
End If
        
        
' --------------------End of Values Section--------------------

[Norie]

Is it the exact same code, except the columns, for each of D1, E1, D1... etc.

[Toyo613]

It is the same code however some of the formulas need to offset as well

P:Q Column would be

            With Range("P24")
                .Formula = "=(P26-(P22+P23))/P26"
                .NumberFormat = "0.00%"
                .Interior.Color = RGB(255, 255, 67)
                .Font.Bold = True
            End With

R:S Column would be

            With Range("R24")
                .Formula = "=(R26-(R22+R23))/R26"
                .NumberFormat = "0.00%"
                .Interior.Color = RGB(255, 255, 67)
                .Font.Bold = True
            End With

[Norie]

So it's just the columns that change, including in the formulas?

[Toyo613]

I'm 99% that's correct,

I may be over looking one formula but for the most part that is the idea

[Norie]

Where is the code located and how is it being executed?

[Toyo613]

I've attached the file, very new to VBA's so I just want to make sure I'm being clear

Price Template2.xlsm