IsnUmeric with range

[makis1023]

Hello all,

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?

Thank you!

 

[Joe4]

What is the difference if I put the code in sheet module and not standard module?

The sheet module is mostly for automated "event procedures"; which is code that runs automatically upon some even happening, like the update or selection of a particular cell. It will default to only look at the sheet that the module is in, and I don't think you can access that code from other sheets.

For code that you want to call yourself, especially code that spans multiple sheets, you should put it in a general "standard" module, which can be seen and referred to from any sheet.

Best practice is to only put automated event procedure code in the Sheet and ThisWorkbook modules.

 

[makis1023]

The sheet module is mostly for automated "event procedures"; which is code that runs automatically upon some even happening, like the update or selection of a particular cell. It will default to only look at the sheet that the module is in, and I don't think you can access that code from other sheets.

For code that you want to call yourself, especially code that spans multiple sheets, you should put it in a general "standard" module, which can be seen and referred to from any sheet.

Best practice is to only put automated event procedure code in the Sheet and ThisWorkbook modules.

Thank you for the info! I am gettin better and better!!!