Posts by Xani

Dear royUK

Checked but I am receiving error message as showing in attached photo. The first photo I keep only the second code, the second photo I keep first and second code but error message is showing up.

Please advise.

Dear royUK

Checked and it worked like a charm, just one more question can I change name of file saved to only service group name?

Also is there a possibility to modify data output formatting? for example Row Height 30, Middle Align and Center,

Hello

It been very long time since I want to learn this, I have data and I want to split it into separate workbooks which will be saved in the same folder of workbook

Split of data to be by Service Group "Column D", please advise how can I do that since am not expert in VBA, CODING etc.

Dear All

I want to get formula to calculate duration per days to complete each task.

Reported Date – Actual Finish Date : For Example 3 days and 45 minutes etc..

Regards;

But in A2 there is owner name,

for example owner salman, in the B2 there should be showing 22.

for example owner Mohamed, in the B2 there should be showing 10

I tested formula in A2 but it didn't work.

I checked it .. it gives count wrong and not correct why ?

Hello,

I want to return total queued amount of each owner to its name in order to show the chart, how can I do that. ?

Regards;

Sorry it became duplicate please delete this post

Dear All,

I would like to write a formula that will tell me by showing message if Labor A is conflict with Labor B "They are going on leave and having conflict dates"

How to do that ?

Regards;

Dear All,

I would like to write a formula that will tell me if Labor A is conflict with Labor B "They are going on leave and having conflict dates"

How to do that ?

Regards;

Dear All,

I would like to write a formula that will tell me if Labor A is conflict with Labor B "They are going on leave and having conflict dates"

How to do that ?

Regards;

Thank you , it worked.

Thanks but I want to get the message Match / Not Match .. how can I do that ?

Dear All

I am trying to compare type a column with type b column if they match or not, which formula I should use ...

Regards;

Hello,

I have the following string in CI , and I want to remove the following

Text to be remove: Bahrain International Airport / Buildings and Terminal 1 /

Original Text is : Bahrain International Airport / Buildings and Terminal 1 / Cargo Agents Building / Roof / Roof (Electrical/Electronical Systems)

What should be the formula ?

Dear Alan,

Thank you for your e-mail, I checked but if the date is within two different dates instead of the same date it will give wrong output.

Regards;

Dear Sir, Madam

Please find attached file, I am trying to calculate total duration in Hours between two dates, I use custom field HH:MM and formula Actual Finish - Reported Date.

But why giving wrong output ?

Regards;

Dear Sir, Madam

I am trying to make chart for staff who work as overtime .. I put each staff name and then one column for the total of hours he worked in a year.

The left side of scale showing all 0000 , why is that ? Also if I try to add sum it will not give me correct output in the chart.

Attached file for your information and review.

Regards;

Re: Combine Project Managers into separate sheets?

Dear nyatiaju,

I tested the workbook and it is working perfectly.
Thank you.

Re: Combine Project Managers into separate sheets?

Dear iwrk4dedpr,

I replaced the code as you mention to the following:

Option Explicit
Sub SplitByOwner_v2()
     
     '   Local Variables
    Dim rngData         As Range
    Dim rngNames    As Range
    Dim cell        As Range
    Dim strCName        As String
    Dim strSht      As String
     
     '   Set object references
    Set rngData = Sheet1.Range("A2", Sheet1.Range("A1000000").End(xlUp))
    Set rngNames = Sheet3.Range("A2", Sheet3.Range("A1000000").End(xlUp).Offset(0, 3))
     
     '   Sort To Individual sheets
    For Each cell In rngData
        strCName = cell.Range("H1")
        strSht = Application.WorksheetFunction.VLookup(strCName, rngNames, 3, False)
        If Not SheetExists(strSht) Then
            Sheets("Template").Copy , Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = strSht
        End If
        With Worksheets(strSht).Range("A1000000").End(xlUp).Offset(1, 0)
            .Range("A1") = cell.Range("A1") 'WORKORDER
            .Range("B1") = cell.Range("B1") 'DESCRIPTION
            .Range("C1") = cell.Range("C1") 'WORKTYPE
            .Range("D1") = cell.Range("E1") 'WOSTATUS
            .Range("E1") = cell.Range("H1") 'NAME
            .Range("F1") = cell.Range("V1") 'TARGETST
            .Range("G1") = cell.Range("W1") 'TARGETFIN
            .Range("H1") = cell.Range("X1") 'SCHEDST
            .Range("I1") = cell.Range("Y1") 'SCHEDFIN
            .Range("J1") = cell.Range("Z1") 'ACTST
            .Range("K1") = cell.Range("AA1") 'ACTFIN
            .Range("L1") = cell.Range("AB1") 'WOCREATE
            .Range("M1") = cell.Range("AC1") 'RECID
            .Range("N1") = cell.Range("AE1") 'INCSTATUS
        End With
    Next cell
     
End Sub
Function SheetExists(sName As String) As Boolean
    SheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function
 
Function TransposeArray(Arr As Variant) As Variant
    Dim temp, a As Long, b As Long, i As Long, ii As Long
    i = UBound(Arr, 2): ii = UBound(Arr, 1)
     
    ReDim temp(1 To i, 1 To ii)
     
    For a = 1 To i
        For b = 1 To ii
            temp(a, b) = Arr(b, a)
        Next
    Next
    TransposeArray = temp
     
End Function

But I am receiving this message, highlight in yellow the second code line

Function SheetExists(sName As String) As Boolean
SheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function

What mistake I did do here ?