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