Posts by mudraker

Re: Access Report Footer Averages

Will Riley

Thanks for your reply.

It was exactly what I needed

The comment posted on the other forum was not meant to run this forum down or anyone who replied to my thread here, but to explain why I was cross posting my query on that site as well.

I am not always aware of what holidays are being observed in other areas & believe that if no response is received within 20 hours of my last posting, that posting the same question on another forum is acceptable.

If the comment that I have posted on this forum or on http://www.theofficeexperts.com have offended anyone Please accept my apology

Re: Access Report Footer Averages

I know how to get the total sum value
How do I get distinct count of the number of days that appear on the report
Every thing I have tried gives me a total count of the number of entries not the count of the number of days.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]This has now been cross posted

http://www.theofficeexperts.co…ead.php?p=30909#post30909

I am new to creating Access reports

Based on a query I have a report that pulls completed work that I have not been paid for.

The report groups completed work by date
Each completed task has its own Value field
The completed date footer has a field to sum that days $ value

In the Report footer how do I get an average of the daily $ value?

Re: Insert Row & FillDown Range

This thread is now cross posted at http://www.excelforum.com/showthread.php?t=617624

strikez

When cross posting you need to provide the link in your thread on this site to the thread you posted on Excelforum

Re: Calculating Hourly Pay Rate

GeorgS

Your suggested solved my problem

thank you

Re: return value thats offset

Cross Posted

http://www.excelforum.com/show…php?p=1802548#post1802548

Via a form I update an Access 2003 database with various information

The database & form has a field for the length of time a task took to complete. The entry is in h:mm format

Also another field for the payment amount of the completed task in $.cc format.

After the above fields have been filled, I need to populate a 3rd field with the hourly pay rate for the completed task.

The length of time a task takes & the total payment paid various with each task.

How do I calculate the hourly pay rate

The time taken is in a Textbox named tbRouteTime
The Payment amount is in a Textbox named tbValue
The Hourly pay rate goes into a Textbox named tbHourRate

Thanks in Advance for your assistance

This question has now also been posted to vbaexpress

http://vbaexpress.com/forum/sh…d.php?p=106025#post106025

Re: Move Files To Dynamic Folders Based On Cell Value

If cell A1 is blank then macro will continue to loop on the same file. This is because there is no To folder to create.

Try this modified macro. It tests if A1 has a value, If no value displays an alert & macro stops leaving the book open.

As an added precaution I have also added some code to ensure that it is looking at cell A1 from the book just opened and not the book with the macro

Sub MoveFiles()
   Dim sPathFrom As String
   Dim sPathTo As String
   Dim sFile As String
    
    'path must include \
   sPathFrom = "c:\Temp\"
    
   sFile = Dir(sPathFrom & "*.xls")
   Do While Not sFile = vbNullString
      Workbooks.Open sPathFrom & sFile
      With Workbooks(sFile)
         sPathTo = .Sheets(1).Range("a1").Value
         If sPathTo = vbNullString Then
            MsgBox "Expected Path Value Not Found" _
               & vbLf & vbLf _
               & "Macro Can't Continue", vbInformation
         End If
         .Close False
      End With
      If Dir(sPathFrom & sPathTo, vbDirectory) = vbNullString Then
         MkDir sPathFrom & sPathTo
      End If
      Name sPathFrom & sFile As sPathFrom & sPathTo & "\" & sFile
      sFile = Dir(sPathFrom & "*.xls")
     Loop
End Sub

Re: Move Files To Dynamic Folders Based On Cell Value

try this macro

Modify spathfrom as required
Modify range A1 as required

Sub MoveFiles()
   Dim sPathFrom As String
   Dim sPathTo As String
   Dim sFile As String


   'path must include \
   sPathFrom = [COLOR="Red"]"c:\Temp\"[/COLOR]
   
   sFile = Dir(sPathFrom & "*.xls")
   Do While Not sFile = vbNullString
      Workbooks.Open sPathFrom & sFile
      sPathTo = Range("[COLOR="red"]a1[/COLOR]").Value
      Workbooks(sFile).Close False
      If Dir(sPathFrom & sPathTo, vbDirectory) = vbNullString Then
         MkDir sPathFrom & sPathTo
      End If
      Name sPathFrom & sFile As sPathFrom & sPathTo & "\" & sFile
      sFile = Dir(sPathFrom & "*.xls")
   Loop
End Sub

Re: Vba Code To Open Cd Rom Device

abbeville

I certainly don't mind what you do to the code and it is not mine - I found it on the net

Re: Keep Autofilter Custom Dialogue Box Open

Your question does not make sense as the filter options that is open is for the one column only & only closes once you select cancel or ok. Excel can not filter the list until the ok button is pressed. To filter on multiple columns you then need to select the list for each column in turn

Re: Finding Last Used Column

Reafidy

You are correct it is wrong

should be as you suggested

'find last used column in row 1
iLastColumn = Cells(1, columns.count).End(xlToLeft).Column

Re: Finding Last Used Column

Here is a few different ways to get last row & column

'find row before next blank cell in column A
iLastRow = Range("A1").End(xlDown).Row


'find last used row in column A
iLastRow = Cells(Rows.Count, "a").End(xlUp).Row


'find last used row on sheet
iLastRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row


'find column before next blank cell in row 1
iLastColumn = Range("A1").End(xlToRight).Column


'find last used column in row 1
iLastColumn = Cells(Columns.Count, "a").End(xlToLeft).Column


'find last used column on sheet
iLastColumn = Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column


'set variable to last used cell
Dim rLC As Range
Set rLC = ActiveSheet.UsedRange


Set rLC = ActiveCell.SpecialCells(xlLastCell)

Re: Restrict Fields To Only Text Without Numbers (excel)

From the Data Menu >
Select Validation >
Settings Tab >
From the Allow dropdown list select Custom >
In the Formula box enter =NOT(ISNUMBER(A1:A5))
Change cell range as required
Select Input Message Tab >
Enter Title & Message if special message required
Select Error Alert Tab >
Enter Title & Message if special message required

Re: Extracting Part Of A String From A Longer String

try

myVar = Right(MyOtherVar, 10)

Re: Search Cell For Text

Try Instr or even InstrRev commands. InstrRev not available pre Excel 2000

If InStr(1, Cells(r, 6).Value, "Bismarck") > 0 Then

Re: Macro Code Error To Paste Text

When copying & pasting values there is a much quicker way to do it

Try

Dim lLR As Long
With Sheets("Database")
   lLR = .Cells(Rows.Count, "b").End(xlUp).Row
   .Range("b" & lLR, "k" & lLR).Value = Range("B4:K4").Value
End With

Re: Counting Cells Within Vba

Try

Private Sub Trigger_Box_1_Change()
    
    Windows("Admin Skills Matrix.xls").Activate 'puts focus on correct spreadsheet
     Sheets("Skills Matrix").Select 'puts focus on correct page
     
    Select Case WorksheetFunction.CountIf(Range("a1:a10"), 0)
    Case 0
         
        Trigger_Box_1b.Text = New_Staff_Name
         
    Case Else
         
        Trigger_Box_1a.Text = New_Staff_Name
         
    End Select


End Sub

Re: Format Result Of Date Difference To 2 Decimal Places

this function is the same as Gizzmo posted with a modification that takes into account the number of days in the month of date 2 similar to what Reafidy has already posted. but as I was working on it when he replied I thought I will still add my 2 cents worth

Function DateDiff(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer


M = ((Year(Date2) - Year(Date1)) * 12) + (Month(Date2) - Month(Date1))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
End If
DateDiff = M & Format(D / Day(DateSerial(Year(Date2), Month(Date2) + 1, "01") - 1), ".0#")
End Function

Re: Display Records Randomly

Duplicate posting

http://www.ozgrid.com/forum/showthread.php?t=67596