check column for nonblank data

**vampylestat** · 10-01-2008, 04:49 PM

Hello everyone,

I'm currently working on an excel project for work, to replace our production tracking to something more current (anything is better than pen and paper!). I am in charge of keeping track of cases of product made on our shift: product code, flavour, bag size, current shift scheduled #s, next shift scheduled #s, total scheduled #s, produced (on our shift), and product on hold. For the most part, I've achieved to get all of it working, but I want to take it one step further, where it is completely automated.

Normally during the dayshift, we will receive an attainment report from the scheduler, and on this schedule, it lists out what amts are scheduled for each code, for the next 3 shifts. We would then manually write down each product code running on our shift, how much we're scheduled to run on our shift, and the next shift. Before the end of our shift, we would write up another production sheet for the afternoon shift, and the afternoon shift would do the same for the midnight shift, and again for dayshift until the scheduler revised the attainment report.

My file consists of 5 worksheets: Days, Afternoons, Midnights, Product, Schedule

Days, Afternoons, Midnights will be where production data is stored throughout each shift.

Product holds records for each type of product we produce.

Schedule holds the scheduled #s for each product scheduled to run for the next 3 shifts.

So finally getting to the point, I would like to check the Schedule worksheet, column C for any non-blank cells, and copy the corresponding product code to the dayshift worksheet starting at A3, and the same for column F for afternoons, and column I for midnights.

Hopefully this makes some kind of sense, so I will attach my file for extra info.

TY for taking the time to read lol

sorry bout the short subject, i keep getting errors if i try to add any more to it

**Leith Ross** · 10-01-2008, 08:44 PM

Hello Vampylestat,

The following macros have been added to the attached workbook. There is one macro per shift named: UpdateShift1, UpdateShift2, UpdateShift3. These will update the "Days", "Afternoons", and "Midnights" worksheets respectively with the scheduled information in columns "C", "F", and "I" on the "Schedule" worksheet.

Code:

Sub UpdateShift(ByVal Shift_Number As Integer)

  Dim Code
  Dim CodeColumn As Variant
  Dim CodeRow As Long
  Dim DstRng As Range
  Dim R As Long
  Dim SchedWks As Worksheet
  Dim ShiftName As String
  Dim ShiftWks As Worksheet
  Dim SrcRng As Range
  Dim Units As Long
  Dim UnitsColumn As Variant
  
    Set SchedWks = Worksheets("Schedule")
    CodeColumn = "A"
    
    Select Case Shift_Number
      Case 1
        ShiftName = "Days"
        UnitsColumn = "C"
      Case 2
        ShiftName = "Afternoons"
        UnitsColumn = "F"
      Case 3
        ShiftName = "Midnights"
        UnitsColumn = "I"
    End Select
    
    Set ShiftWks = Worksheets(ShiftName)
    ShiftWks.Range("A3:A30").ClearContents
    CodeRow = 3
    
       For R = 3 To 30
         Code = SchedWks.Cells(R, CodeColumn)
         Units = SchedWks.Cells(R, UnitsColumn)
           If Units > 0 Then
             ShiftWks.Cells(CodeRow, "A") = Code
             CodeRow = CodeRow + 1
           End If
       Next R
       
End Sub

Sub UpdateShift1()
  Call UpdateShift(1)
End Sub

Sub UpdateShift2()
  Call UpdateShift(2) 
End Sub

Sub UpdateShift3()
  Call UpdateShift(3)
End Sub

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross