I have the following code that works great. Because it will be big enough I want to choose only the numbers from E or J etc and not have to make choise (ie E34:E66. J15:J33)
Sub BuildInvoiceAll() Dim ws As Variant, arr1 As String, arr2 As String, arr3 As String, arr4 As String, arry As Variant Dim i As Long, j As Long, nr As Long Dim cell As Range, f As Range Dim Descript As String Application.ScreenUpdating = False 'Set array of worksheet names to copy from ws = Array("AUDIO", "LIGHTS", "HOISTS - TRUSS - DRAPES", "DISTRO - CABLES - MISC") 'cells to AUDIO sheet arr1 = "E:E, J:J" 'cells to LIGHTS sheet arr2 = "E13:E34, J13:J59, E36:E59, E73:E89, J73:J82, J84:J91, E91:E98, J93:J101, E100:E109, J103:J113" 'cells to HOISTS sheet arr3 = "E13:E28, K13:K37, E30:E40, E42:E52, E67:E91, K67:K85, E106:E123, K106:K119, K121:K129, E127:E137" 'cells to DISTRO sheet arr4 = "E13:E35, K13:K50, E37:E50, E64:E116, K64:K88, K92:K108, K111:K120, E131:E148, K131:K148, K150:K159," & _ "E152:E180 , K163:K188 , K190:K203 , E184:E216 , K207:K238 , K240:K249 " arry = Array(arr1, arr2, arr3, arr4) nr = 14 Sheets("PROFORMA DRYHIRE").Range("A15:C70").ClearContents For i = LBound(ws) To UBound(ws) 'Loop through all shees in the array For Each cell In Sheets(ws(i)).Range(arry(i)) 'Loop through all cells in the multirange If cell > 0 Then 'See if anything entered in pieces Descript = cell.Offset(0, -3) 'get description from column B With Sheets("PROFORMA DRYHIRE") Set f = .Range("A15:A70").Find(Descript, , xlValues, xlWhole) If Not f Is Nothing Then nr = f.Row Else nr = nr + 1 If nr > 70 Then MsgBox "Rows are full" Exit Sub End If End If .Cells(nr, "A") = Descript 'Populate values in PROFORMA sheet .Cells(nr, "B") = cell 'get pieces from column E .Cells(nr, "C") = cell.Offset(0, -1) 'get price p/d from column D End With End If Next cell Next i Application.ScreenUpdating = False End Sub
Can this be done?
Note! This is also posted on mrexcel Forum https://www.mrexcel.com/board/…meric-with-range.1196052/