Search results

  1. V

    Is there a better way?

    yeah i read through the whole thing and reduced all the optional perameters to nothing. I only have what:=rsLeave![DoD ID]. but like i stated i think there is a problem with how I am ranging the month breakout using the if statement for Mrng.
  2. V

    Is there a better way?

    yeah i did do that before i commented out that line and just made it simple with what its looking for.
  3. V

    Is there a better way?

    Yeah I know I took that part out in the actual working line.
  4. V

    Is there a better way?

    Your code didn't quite work but I removed all the excess and it is working kinda again. I get an error "Object Variable or with block variable not set" and that highlights the rownum = rRange.row. I think it has something to do with how my range for each month is set up Mrng variable. Dim...
  5. V

    Is there a better way?

    so the whole immediate window thing with the ? terms doesn't work I get no answers nothing happens. I had to put a watch on the variable and look into that for the values. I have realized yesterday that rownum as was stated doesn't work since it just goes down the row 1 by 1 until the end and...
  6. V

    Is there a better way?

    So I have gone back through and made adjustments. I am not getting any errors anymore yet it is not quite doing what i want. rownum = 4 Do While rsLeave.EOF = False initDate = rsLeave![Start Date] rownum = rownum + 1 i = DateDiff("d", rsLeave![Start Date]...
  7. V

    Is there a better way?

    what is happening is the excel document is shading in cells but instead of finding the range that I have set for each month the shading starts at "A1" and just goes down the spreadsheet. Some reason the range that I have set in variable "Mrng" is not being followed. Also I am getting the...
  8. V

    Is there a better way?

    I am sorry that I have frustrated you
  9. V

    Is there a better way?

    While Not rsLeave.EOF rownum = rownum + 1 If Month(rsLeave![Start Date]) = 1 Then Set Mrng = xlSh.Range("CR4", "DV230") ElseIf Month(rsLeave![Start Date]) = 2 Then Set Mrng = xlSh.Range("DW4", "EX230") ElseIf Month(rsLeave![Start Date]) = 3...
  10. V

    Is there a better way?

    'Set months title With xlSh.Range("D1", "AH1") .Font.Bold = True .Font.Size = 12 .VerticalAlignment = xlVAlignCenter .HorizontalAlignment = xlVAlignCenter .MergeCells = True .Interior.ColorIndex = 37 .Value = "October" End With...
  11. V

    Is there a better way?

    ok yeah that works for the ones that go over a year but I think the main issue I am going to run into is if someone takes leave from say january 25 to february 14 being 1 month a sheet shading the proper cells is causing an error. I know your going to hate me but I went back to a single...
  12. V

    Is there a better way?

    crap no that's not going to work either since its checking on the current month and not the month tab that it is adding leave to.
  13. V

    Is there a better way?

    I think i might have found what is really causing the problem. The first leave in the RS spans a few months so it cannot be put into the individual month sheets. rownum = 1 While Not rsLeave.EOF rownum = rownum + 1 If (Month(rsLeave![Start Date])) <...
  14. V

    Is there a better way?

    that was a failed attempt, I thought maybe the action required looking at the whole workbook instead of the sheets but alas that didnt work either got "Invalid procedure or argument" I also tried to put that whole loop up into the individual month creations but that was bad too. Oh I also...
  15. V

    Is there a better way?

    I did try that one still get the same error. After trying the ?wsNew(Month....... in the immediate window and then trying to slowly type out the code the pop up window doesn't pop up at the beginning when i hit .cells so I think it might be something in that opening series.
  16. V

    Is there a better way?

    rownum = 1 While Not rsLeave.EOF rownum = rownum + 1 For i = 0 To DateDiff("d", rsLeave![Start Date], rsLeave![End Date]) initDate = rsLeave![Start Date] xlWB(MonthName(initDate)).cells(rownum, Day(initDate + i)).BackColor = 4...
  17. V

    Is there a better way?

    next piece: Select Case wsNew.Name Case "January", "March", "May", "July", "August", "October", "December" wsNew.Range("D2:E2").Autofill Destination:=wsNew.Range("D2:AH2"), Type:=xlFillSeries With wsNew.Range("D1", "AH1")...
  18. V

    Is there a better way?

    sorry I thought I had put the whole code in here, but here is an update (have to do multiple posts it exceeds 10000 characters: Private Sub cmdT2T_Click() Dim xlApp As Object Dim xlWB As Object Dim wsNew As Object Dim rsRoster As DAO.Recordset Dim rsLeave As DAO.Recordset...
  19. V

    Is there a better way?

    Macro has .themeColor = xlThemeColorAccent6 for green I used wsNew (Month) instead of worksheets(month) because wsNew is what I have set as worksheets in the VBA code. I dont know what more to put in here to help the experts help me. rownum = 1 While Not rsLeave.EOF rownum...
  20. V

    Is there a better way?

    intellisense doesn't pop up at either decimal. All of the worksheets are named by their respected month not a number. I also tried to replace .backcolor with .interior.colorindex and still got the same error.
Back
Top Bottom