I am trying to get the following pr 3

[willyboy58]

I am trying to get the following procedure to read a row of information, find the cells in the row that are NOT empty (they will have a number in them) and then shift one column to the right and insert an “L” or an “H” (two separate procedures at this point) in the empty column to the right.

The brief worksheet looks like the following. A2 is the first activecell.

A B C D E F
1 name job 05-01-03 5-02-03
2 Bob 186 6.0
3 Steve 188 6.0
4 Mike 189 6.0
5 Frank 188 6.0

Row 1 is my headings: 2 on down is the employee name, job # and hours. Columns D and F are the empty columns to place an L or H into.

In the procedure below, if the value in the activecell in column B (above) is equal to 186 or 189 then I want an “L” in the empty column next to the hours in the row for that day (Bob and Mike, column D). If it is 188, then I want an “H” (Steve and Frank, column F). When it finishes with one row, it starts the next. I have the code (a different procedure) to count the number of entries across for each row, but I’m not sure how to tell it to go to the next row of info. The following is for the "L" only. I'll write a separate one for the "H".

Sub ActivateNextCellToRightL()
Dim LeaveHours As String
LeaveHours = "L"
ActiveCell.Offset(0, 1).Select
Do While IsEmpty(ActiveCell)
With ActiveCell
ActiveCell.Offset(0, 1).Select
LeaveHours = ActiveCell.Offset(0, 1).Select
End With
Loop
End Sub

The procedure above stops at each cell of info in the row, but it will not move to the next column (empty cell) to put the “L” into and then move across to the next cell. There will be about 30 columns of info. The above is only a small part of a much larger program.

Any help will be greatly appreciated. TIA. Bill

 

[Zathras]

If I have understood you correctly, the following code should do what you want:
[blue]

Option Explicit

Sub UpdateCodes()
Const COL_CODES = "B"
Dim c As Range
  For Each c In Intersect(ActiveSheet.UsedRange, Columns(COL_CODES))
    Select Case c.Value
      Case 186, 189: UpdateRow c, "L"
      Case 188: UpdateRow c, "H"
    End Select
  Next c
End Sub

Private Sub UpdateRow(ACell As Range, ACode As String)
Dim nCol As Integer
  For nCol = 1 To 30 Step 2
    If ACell.Offset(0, nCol) > 0 Then
      ACell.Offset(0, nCol + 1) = ACode
    End If
  Next nCol
End Sub

[/color]

 

[willyboy58]

Zathras,

Thanks for the help, but I can't seem to get your code to work. Nothing happens when I run it.

What does the variable "ACode" stand for? Does it have to be declared?

TIA

Bill

 

[SkipVought]

Hey willie,

Z's code works just fine on your example, form what I can determine.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Hey Guys,

FANTASTIC!!!! Not sure what I was doing wrong, but I got it to work. Excuse me, YOU got it to work. I couldn't even blink before yours was finished doing its thing!!

I have some more coding to do. I have to count the number of entries in a row (days with hours) and then show the totals for work day hours, holiday hours and leave time hours. The info has to appear to the right of the data. The totals should look something like the following:

Name # of # of # of Total
Work Days Leave Days Holidays
Bob 20 2 1 23
Steve 18 0 1 19
Mike 16 3 1 20
Joe 21 0 1 22
Totals 75 5 4 84

I have some code for counting:

Sub CountingDaysMain()
Dim DaysCounted As Integer, ColOffset As Integer
Sheets("MAIN-PT&quot.Select
Range("A1&quot.Select
While Not IsEmpty(ActiveCell)
With ActiveCell()
DaysCounted = 0 'I increased the number of columns to 46
For ColOffset = 1 To 46
If .Offset(0, ColOffset) > 0 Then DaysCounted = DaysCounted + 1
Next ColOffset 'I increased the number of columns to 46
.Offset(0, 46) = DaysCounted
.Offset(1, 0).Select
End With
Wend
With Range("A1&quot
.Value = "MAIN"
.Font.Bold = True
End With
Range("A1&quot.Select
ActiveSheet.Name = "MainDays"
End Sub

I have three work sheets (for three shops). How can I clean up the above code to make it shorter, faster and work for all three shops at once?

I also want to AutoFit the columns of info. How do I make Range("A1&quot.EntireColumn.AutoFit select all the columns for autofitting?

Once again, THANKS!!

Bill

The wheels of my progress are turning slowly, but they are turning.

 

[Zathras]

Bill: "ACode" is an example of what Excel calls "arguments" which may be part of a procedure declaration. Other languages use the term "parameters."

You can look in the help file under the topic "Calling Sub and Function Procedures" (Excel 2000 - Microsoft Visual Basic Help). If you plan on doing much more programming, I strongly recommend that you study everything in the help file that pertains.

In this example both "ACode" and "ACell" are required arguments for the procedure "UpdateRow." The UpdateRow procedure is invoked ("called&quot from two places in the "UpdateCodes" procedure: Once with the code value of "L" and once with the code value of "N" according to the value found in column "B." By separating the activities of finding which row to update and determining what to update with in one sub, and doing the updating in another sub, the overall structure of the module allows for simpler code in each sub. It would certainly be possible to put all of the code in one sub with a loop within a loop, but the debugging and maintenance tasks become much harder. If in the future you need to do a similar update for a new job code, all you have to do is add one line of code in the main sub to handle the new case.

Welome to the world of programming.

 

[SkipVought]

Willie,

Thanx for the star, but I really did not do anything that rates being helpful or expert.

Z put together some nice VBA that typifies very good coding techniques along with a fine explaination of wuts happnin'.

Z, I feel better today ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Zathras,

I hate to be picky, especially on a Saturday evening, but ..

Arguments are what are passed to procedures, and
Parameters are what are received by procedures

This has been consistent terminology in my experience from mainframes to PCs, although most people (myself included) use the two terms interchangeably.

Enjoy,
Tony

 

[Zathras]

Tony, I agree that the industry-standard time-honored terminology for the thing is parameter, and I'm not fond of Micro$oft for blurring the vocabulary, but we are working with a self-described "Technical User" here and not a programmer. Since the word "parameter" does not appear anywhere in the help topic that I referenced, I was compelled to constrain myself to the term "argument" in an attempt to avoid further confusion.

Incidentally, in that same help topic we find the term "named arguments." Since the only names for the arguments/parameters are found inside the parentheses in the Sub definition, I think we are stuck with Micro$oft's terminology whether we like it or not.

 

[Zathras]

Bill, if I read you correctly, you want the total of columns "B" thru "AT" recorded in column "AU" (offset 46 from column "A&quot starting in row 3 to the end of the used area. Here is one way to do that:
[blue]

Sub CountingDaysMain()

[green]

' Places the sum of columns B thru AT in column AU for
' all active rows beginning with row 3.

[/color]

  Sheets("MAIN-PT").Select
  With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(47))
    .FormulaR1C1 = "=SUM(RC[-45]:RC[-1])"
    .Copy
    .PasteSpecial xlPasteValues
  End With
  Range("AU1") = "Total"
  Range("AU2") = ""
  With Range("A1")
    .Value = "MAIN"
    .Font.Bold = True
    .Select
  End With
  ActiveSheet.Name = "MainDays"
End Sub

[/color]

 

[willyboy58]

There is a new wrinkle in my procedure. I now have to copy the L or H from the cell next to the hours down to a Total row. I am trying to adapt some code that worked for another part of my overall procedure. After the L and H have been copied, the sheet should look like the following. I have inserted some space b/w the rows to make it easier to read.


A B C D E F
1 name job 5-1-03 5-02-03

2 186 6.0 L
3 188 6.0 H
4 Bob Total 6.0 L 6.0 H

5 188 6.0 H
6 Steve Total 6.0 H

7 189 6.0 L
8 Mike Total 6.0 L

9 188 6.0 H
10 Frank Total 6.0 H

The word “Total” is actually in the same cell as the person’s name. After I have the L and H copied into the total rows, I will then delete column B and the rows for those hours. This will leave;

A B C D E F
1 name 5-1-03 5-02-03

2 Bob Total 6.0 L 6.0 H

3 Steve Total 6.0 H

4 Mike Total 6.0 L

5 Frank Total 6.0 H

'the following code gets caught in a loop

Sub CopyLorHToTotalRow()
Const COL_CODES = "B:AV"
Dim c As Range
Sheets("MAIN-PT&quot.Select
'delete col B. No longer need the 186, 188 or 189 or should
'the column be kept until the procedure is done
Columns("B:B&quot.Select
Selection.Delete Shift:=xlToLeft
'B will now be the first column of hours to look at
Range("B2&quot.Select
For Each c In Intersect(ActiveSheet.UsedRange, Columns(COL_CODES))
Select Case c.Value
Case L: CUpdateTotalRow c, "L"
Case H: CUpdateTotalRow c, "H"
End Select
Next c
Range("A1&quot.Select
Range("A1&quot.EntireColumn.AutoFit
End Sub

Private Sub CUpdateTotalRow(ACell As Range, ACode As String)
Dim nCol As Integer 'since I am only looking for an L or H,
For nCol = 1 To 44 'Step 2 ' should I read every cell across?
If ACell.Offset(0, nCol) > 0 Then
ACell.Offset(0, nCol + 1) = ACode
'the following has to detremine if there is one, two or three cells
'above the total row. There should be one or two rows/cells
'(for the 186 & 188 hours) but there could be three
'(186, 188 & 189) if a mistake was made during timesheet entry.
With Activecell
.Copy
'End With
'With ACode
.Offset(1, 0).Select
.PasteSpecial
End With
End If
Next nCol
End Sub

As always, all input will be greatly appreciated.

TIA

Bill

 

[Zathras]

The tricky part is knowing which coding style to use when. In this case, i would not use the "For Each c in Intersect..." construct. It doesn't quite fit. Instead just call this routine:
[blue]

Option Explicit

Sub UpdateTotalRowCodes()

[green]

' Transfer codes from detail rows to total rows
' Process every odd-numbered column in used range
' beginning with column "C"
' NOTE: Assumes that Row 1 and Column "A" are not empty

[/color]

Dim nColumns As Integer
Dim nRows As Long
Dim nCol As Integer
Dim nRow As Long
Dim sCode As String
  
  With ActiveSheet.UsedRange
    nColumns = .Columns.Count
    nRows = .Rows.Count
  End With
  For nCol = 3 To nColumns Step 2

[green]

    'Processing one column:

[/color]

    For nRow = 2 To nRows

[green]

      'Processing one cell:

[/color]

      If Right(Cells(nRow, 1), 5) = "Total" Then

[green]

        ' This is the total line.  Drop in the code

[/color]

        Cells(nRow, nCol) = sCode
      Else
        If Cells(nRow, nCol) <> &quot;&quot; Then

[green]

          ' Code is here.  Capture for use on total line.

[/color]

          sCode = Cells(nRow, nCol)
        End If
      End If
    Next nRow
  Next nCol
End Sub

[/color]

 

[willyboy58]

Zathras,
I messed up my previous post by leaving out some info. My pivot table is working by giving me the totals for all hours, including the “work” hours (that I failed to include in my previous description). This is good because I need the work hours listed also.

But in the total row for each employee, I do not want any letter whatsoever (L, H or W) next to hours that are for “work”, only for those hours that are for Leave or Holiday, which are marked with an L or H to the right of them. The code I have for marking L or H in the hour rows is working. I’m OK up to this point. Actually, the only hours at this point in the hours rows are either Leave or Holiday. The work hours do appear in the Total row.

Here is what I need now: Only those employees that have an L or H in their hour rows will have the L or H copied to their total rows. Which means there will be many hours in the Total row for each employee that is not marked with an L or H. In the info below, Bob’s hours for 5-1 would be for work (no L or H).

A B C D E F
1 name 5-1-03 5-02-03

2 Bob Total 6.0 6.0 H

3 Steve Total 6.0 H

4 Mike Total 6.0 L

5 Frank Total 6.0 H


My logic is to read across the row, if there is an L or H, then copy the L or H, and drop down to the Total row for that employee (which may not be the first row beneath it) and paste the L or H next to those hours in the Total row. Then, go to the next column(s) in the same row and look for another L or H and do the same. When finished with that row of hours for that employee, then go to the next row, which could still be for the same employee and do the same.

I’ve tried adjusting various pieces of code, including the last one, to do the above, but have not had any success. I apologize for leaving out the “work” hours in my previous posts.

As always, any input will be greatly appreciated.

TIA, Bill

 

[Zathras]

If I understand correctly, all you need to do is insert a line of code after the line where it says

   Cells(nRow, nCol) = sCode

insert a line so it looks like this:
[blue]

   Cells(nRow, nCol) = sCode
   sCode = ""

[/color]
For the next employee, if there is no "L" or "H" code, the sCode variable will still be blank and therefore only a blank will appear on the "Total" line. Actually, that was an oversight. I should have written it that way in the first place.

I didn't include a test for codes other than "L" or "H", so if there are any, now would be a good time to tell me and I can make another quick change to the code.

 

[willyboy58]

Zathras,

EXCELLENT! I have some more to work on to get this project finished. I will take into work what I have so far (with your excellent help!) and see what everyone thinks. I may have some changes (hope not, but you never know).

I'll let you know.

Thanks!!

P.S. I will print out all of what I have so far so I can try to learn some better coding techniques. Some of mine might work, but they are crude.

 

[willyboy58]

Zathras,

They changed some of the requirements.

If the employee worked, then show the number of hours that they worked. This part is OK because the pivot table gives the total for the day. No change needed here.

But, if the employee was off work and also did not receive any Leave or Holiday pay, then in the Total row in the cell where the hours (number figure) would be if they had worked or received L or H pay, place an “X”. This tells the reader that the employee received nothing at all for that day.

For the Leave and Holiday hours, in the Total row, replace the hours number with the L or H copied from the cell just to the right of the hours cell. (The L or H that we pulled from the cell above.) The way I tried to do this was to copy the L or H that has just been placed in the Total row and then offset(0,-1) and then paste over the hour number that is in the cell. I couldn’t get it to work.

A B C D E F
1 name 5-1-03 5-02-03 5-3-03

2 BobTotal 6.0 H 6.0

3 SteveTotal X H 7.0

4 Mike Total L X 5.0

In the table above, Bob worked 6 hours on 5-1 and received Holiday pay on 5-2. Steve received nothing for 5-1, but did receive Holiday for 5-2. Mike received Leave time for 5-1, but nothing for 5-2. All worked on 5-3. If the simplest way to do this is to just copy the L and H from the cell to the right, then that works for me. But I couldn’t figure out how to handle the “X” days.

Thanks for all the help to date. I have one more brief question. In the following procedure, I am wanting to select each different worksheet to run a procedure on, but I can only get the procedure to run on the active sheet.

‘I want to select the worksheets that are named “SATL-PT”, “WYND-PT” and “MAIN-PT” and then run the procedure “UpdateTotalRowCodes” on each individual sheet.

Sub UpdateTotalRowCodesAll ()
Dim Sheet As Worksheet

Application.DisplayAlerts = False

For Each Sheet In Workbooks("Propxfer.xls&quot.Worksheets
If InStr(5, Sheet.Name, "-PT&quot Then
UpdateTotalRowCodes ‘<< procedure name here
End If
Next Sheet

Application.DisplayAlerts = True

End Sub

I can write a separate procedure for each worksheet, but I know that that is very poor coding.

As always, thanks a ton.

Bill

 

[Zathras]

I can't quite follow what you are trying to do with the &quot;X&quot; codes, but here is how to handle your multiple sheet problem:

The programmer's way would be to pass the specific worksheet to the sub as a parameter then use the &quot;with&quot; construct to cause that sheet to be the one used in the sub. However it is just as easy, and probably easier for you to follow, to insert the line of code:

  Sheet.Activate

before calling the UpdateTotalRows procedure. Then after the End If statement, you can re-activate your original sheet if you want to.

 

[willyboy58]

Zathras,

The X means that the employee does not have any hours entered for that day. No Leave, Holiday or work. The X needs to replace the numeric digit (5, 6, 7, etc.) on the sheet. The L and H need to be moved to the left one cell to replace the numeric digit for the Leave or Holiday hours.

Sorry for the confusion. The change was dropped on me yesterday.

TIA. Bill

 

[Zathras]

VBA is not that hard. You should be able to make these kinds of changes yourself. Here's a hint: Where the code above says[green]

   ' This is the total line.  Drop in the code

[blue]

   Cells(nRow, nCol) = sCode

[/color][/color]
you can simply change it to[green]

   ' This is the total line.  Drop in the code

[blue]

   Cells(nRow, nCol-1) = sCode

[/color][/color]

I still don't understand the &quot;X&quot; - if there were no hours, where does the 5, 6, or 7 come from that is to be replaced with the &quot;X&quot;?

 

[willyboy58]

Good point about the X and the hours.

If there are no hours of any type, then the cell needs an X. If there are work hours, then the number of hours should appear in the cell. If there are Leave hours, an L should appear and if there are Holiday hours, an H.

You are correct about being able to figure some of this out. I have written some in the past and have always used the Activecell.Offset method. I wasn't thinking too good the other night.

TIA