0

So im looking to add this formula =IF(I3<>"",IF(K3=0,1L3,L3K3),0) into all the cells in column M, but the issue im running into is that i am able to add the formula but it will be added to the begining of every entry not in between the lines. Im basicaly just looking to multply Row L3 to K3 to give me an output of every line that is filled out.



Option Explicit

Sub InsertRowsTEST()

    Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim x As Long, y As Long, row As Long, col As Long
    Dim ar, i As Long
    ar = Array(60, 65, 70, 85, 92.5, 100, 125, 175, 250, 300, 400)

    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("Sheet1")
    Set ws2 = wb.Sheets("Sheet2")
 
    Do
        'Retrieve an answer from the user
        'Check if user selected cancel button
        x = Application.InputBox("Number of Lines Per skid", "Number of lines per skid", Type:=1)
        If x = 0 Then Exit Sub
    
        y = Application.InputBox("Skid Number", " What is the skid number", Type:=1)
        If y = 0 Then Exit Sub
        
        'insert rows
        ws1.Rows(2).RowHeight = 35
        ws1.Range("3:3").Resize(x).EntireRow.Insert Shift:=xlDown
        ws1.Range("A3").Value = y
      
        'merge columns with a loop
        For col = 1 To 8
            With ws1.Cells(3, col).Resize(x)
                .Merge
                .BorderAround LineStyle:=xlContinuous, Weight:=xlThin
                .RowHeight = 15.75
                .ColumnWidth = 12
            End With
        Next col
        
        ws1.Range("B3:D3").Resize(x).Interior.Color = RGB(195, 224, 180)
        ws1.Range("E3").Resize(x).Formula = "= (B3 * C3 * D3) / 1728"
        With ws1.Range("F3").Resize(x)
            .Interior.Color = RGB(195, 224, 180)
            .BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        End With
        ws1.Range("G3").Resize(x).Formula = "= F3/E3"
        ws1.Range("H3").Resize(x).Formula = "=LOOKUP(G3,HELP!$A$1:$B$11,HELP!$C$1:$C$11)"
        For col = 9 To 12
            With ws1.Cells(3, col).Resize(x)
                .Interior.Color = RGB(195, 224, 180)
                .BorderAround LineStyle:=xlContinuous, Weight:=xlThin
            End With
        Next
        ws1.Range("M3").Resize(x).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        
        'total skids
        With ws1.Range("N3")
             .FormulaR1C1 = "=SUM(RC11:R[" & x - 1 & "]C11)"
             .Interior.Color = RGB(255, 255, 127)
             .BorderAround LineStyle:=xlContinuous, Weight:=xlThin
             .Font.Bold = True
        End With
        
    Sheets("Sheet2").Select
    Range("A3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=60,Sheet1!$F3,0))"
    Range("b3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=65,Sheet1!$F3,0))"
    Range("c3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=70,Sheet1!$F3,0))"
    Range("d3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=85,Sheet1!$F3,0))"
    Range("e3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=92.5,Sheet1!$F3,0))"
    Range("f3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=100,Sheet1!$F3,0))"
    Range("g3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=125,Sheet1!$F3,0))"
    Range("h3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=175,Sheet1!$F3,0))"
    Range("i3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=250,Sheet1!$F3,0))"
    Range("j3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=300,Sheet1!$F3,0))"
    Range("k3").Formula = "=IF(Sheet1!B3=FALSE,0,IF(Sheet1!$H3=400,Sheet1!$F3,0))"
    Range("A3:K3").Select
    Selection.AutoFill Destination:=Range("A3:K74")
    Range("A3:K74").Select
    
    Sheets("Sheet2").Select
    Range("M3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=60,Sheet1!$N3,0))"
    Range("N3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=65,Sheet1!$N3,0))"
    Range("O3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=70,Sheet1!$N3,0))"
    Range("P3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=85,Sheet1!$N3,0))"
    Range("Q3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=92.5,Sheet1!$N3,0))"
    Range("R3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=100,Sheet1!$N3,0))"
    Range("S3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=125,Sheet1!$N3,0))"
    Range("T3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=175,Sheet1!$N3,0))"
    Range("U3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=250,Sheet1!$N3,0))"
    Range("V3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=300,Sheet1!$N3,0))"
    Range("W3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=400,Sheet1!$N3,0))"
    Range("M3:W3").Select
    Selection.AutoFill Destination:=Range("M3:W74")
    Range("M3:W74").Select
    Range("L3").Select
    
    Sheets("Sheet2").Select
    Range("Y3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=60,1,0))"
    Range("Z3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=65,1,0))"
    Range("AA3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=70,1,0))"
    Range("AB3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=85,1,0))"
    Range("AC3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=92.5,1,0))"
    Range("AD3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=100,1,0))"
    Range("AE3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=125,1,0))"
    Range("AF3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=175,1,0))"
    Range("AG3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=250,1,0))"
    Range("AH3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=300,1,0))"
    Range("AI3").Formula = "=IF(Sheet1!$B3=FALSE,0,IF(Sheet1!$H3=400,1,0))"
    Range("Y3:AI3").Select
    Selection.AutoFill Destination:=Range("Y3:AI74")
    Range("Y3:AI74").Select
    Range("L3").Select
    
    Sheets("Sheet1").Select

      
       'x will always be bigger than zero OR smaller than 24
    Loop While x > 0 Or x < 24
    
    
End Sub

Sample Image

Grod1113
  • 1
  • 1
  • What do you mean exactly by "it will be added to the begining of every entry not in between the lines"? It may be useful to provide sample data and expected outcome. you can upload screencaps to http://imgur.com and link to them, someone with enough reputation will likley embed them for you – cybernetic.nomad Feb 21 '23 at 20:29
  • https://imgur.com/60D8h1J – Grod1113 Feb 21 '23 at 20:54
  • this is the code im using for this '''With ws1.Range("M3") .Formula = "=IF(I3<>false,IF(K3=0,1*L3,L3*K3),0)" End With''' – Grod1113 Feb 21 '23 at 20:57
  • Expand the range `With ws1.Range("M3").Resize(x).Formula`. You can include the `.BorderAround LineStyle:=xlContinuous, Weight:=xlThin` in the same With block. – CDP1802 Feb 21 '23 at 21:02
  • Side note: you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Feb 21 '23 at 22:22

0 Answers0