Posts by Maqbool

Maqbool · Dec 9th 2013

Re: Find earliest date for client

Hi

Try the following array formula;

[f]
=LARGE(IF($E1=$B$1:$B$42,$A$1:$A$42),1)
[/f]

Regards

Maqbool

Maqbool · Dec 9th 2013

Re: Macro coding

Hi

Have a look at Pivot Table instead of Macro. I believe it is the best for something like above.

Regards

Maqbool

Maqbool · Dec 9th 2013

Re: Removing (-) dash from a cell

Hi
If you are using my code change the code as follows;

Code

If i > 0 Then 
        Application.EnableEvents = False 
        Target.Offset(0, 2).Value = "" 
        msgbox("Cell C1 is blank" ,vbInformation,"Empty Info"  'change as you want
Else

Regards

Maqbool

Maqbool · Dec 9th 2013

Re: Removing (-) dash from a cell

Hi nitesh_greatie

You cant do that with a formula. Put the following code in change event of the worksheet.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim strDd As String
Dim strCell As String
Dim strSelect As String
Dim strValidation As String


If Target.Column <> 1 Or Target.Row <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
i = Application.WorksheetFunction.Find("-", Target.Value, 1)


If i > 0 Then
    Application.EnableEvents = False
    Target.Offset(0, 2).Value = ""
Else
    Application.EnableEvents = False
    strValidation = Target.Offset(0, 2).Validation.Formula1
    strCell = Left(strValidation, 5)
    strSelect = Range(strCell).Value
    
    Target.Offset(0, 2).Value = strSelect
End If




Application.EnableEvents = True
End Sub

Display More

Regards

Maqbool

Maqbool · Dec 8th 2013

Re: Fix HLOOKUP to work with cells containing formulas

Hi

I cant follow you. Can you please upload a sample workbook with "Expected Result" in column Q. And if you can explain the logic of it, that will be also helpful to those who trying to help you.

Regards

Maqbool

Maqbool · Dec 8th 2013

Re: Removing (-) dash from a cell

Hi

If I understand you correctly, the following formula in B1 will remove all the "-" from the value entered on cell A1.

[F]
=SUBSTITUTE(A1,"-","")
[/F]

Regards

Maqbool

Maqbool · Dec 8th 2013

Re: Removing (-) dash from a cell

Hi

Better upload a sample workbook so someone can easily look into this.

Regards

Maqbool

Maqbool · Dec 8th 2013

Re: Get row number(s) of a match

Hi

try the following Array Formula:
[F]
=IFERROR(LARGE(IF($B$16=$A$2:$A$9,ROW($A$2:$A$9)),ROW(A2)),"")
[/F]

Regards

Maqbool

Maqbool · Nov 11th 2013

Re: Macro to open files using keywords

Hi Miheer:

Sorry - I dont quite understand your query. I assume that, if that particular file is not found in the directory, you need to put "Error" in the next cell. If so, change your code as below.

Code

Sub OPNFile()
    Dim ws As Worksheet
    Dim strpath As String
    Dim strFName As String
    Dim strCash As String
    Dim strCur As String
    Dim strMnth As String
    Dim strFl As String
    Dim rngFund As Range
    Dim xRw As Long
    Dim x As Integer
     
    Set ws = Sheets("Sheet1")
    strpath = "C\Admin\"
     
     
    With ws
        xRw = .Rows.Count
        Set rngFund = .Range("B2", .Range("B" & xRw).End(xlUp))
        For x = 1 To rngFund.Count
            strFName = .Range("B" & x + 1).Value
            strCash = "_Cash_"
            strCur = .Range("C" & x + 1)
            strMnth = .Range("D" & x + 1) & ".xls"
            strFl = strpath & strFName & strCash & "_" & strCur & "_" & strMnth
            If Dir(strFl) = "" Then
                .Range("E" & x + 1).Value = "Error"
            Else
                Workbooks.Open strFl
            End If
             
             
        Next
    End With
End Sub

Display More

Regards

Maqbool

Maqbool · Nov 9th 2013

Re: Getting the last word of string - code issue

Hi

Change the formula part as below:

Code

"TRIM(RIGHT(SUBSTITUTE(B2,"" "",REPT("" "",LEN(B2))),LEN(B2)))"

Regards

Maqbool

Maqbool · Nov 9th 2013

Re: Macro to open files using keywords

Hi

The following code will open the files. I have not applied any conditions or checks. Change the according to your needs.

Code

Sub OPNFile()
    Dim ws As Worksheet
    Dim strpath As String
    Dim strFName As String
    Dim strCash As String
    Dim strCur As String
    Dim strMnth As String
    Dim strFl As String
    Dim rngFund As Range
    Dim xRw As Long
    Dim x As Integer
     
    Set ws = Sheets("Sheet1")
    strpath = "C\Admin\"
     
     
    With ws
        xRw = .Rows.Count
        Set rngFund = .Range("B2", .Range("B" & xRw).End(xlUp))
        For x = 1 To rngFund.Count
            strFName = .Range("B" & x + 1).Value
            strCash = "_Cash_"
            strCur = .Range("C" & x + 1)
            strMnth = .Range("D" & x + 1) & ".xls"
            strFl = strpath & strFName & strCash & "_" & strCur & "_" & strMnth
            Workbooks.Open strFl
            
            
        Next
    End With
     
    
     
     
     
     
End Sub

Display More

Regards

Maqbool

Maqbool · Nov 9th 2013

Re: VBA SumProduct - Issue with number format cells

Hi

Welcome to the forum

Try the following:

Code

Sub SumPrd()


Dim mFormula As String
Dim mCount As Long


Dim mFormula2 As String
Dim mCount2 As Long


Range("A1:C100").Interior.ColorIndex = 0
    For Each Equipment In Range("A1:A100")
    x = x + 1
    wks = Equipment.Offset(0, 1)
    If Not IsNumeric(wks) Then
        wks = """" & wks & """"
    End If
    EquipmentNumber = Equipment.Offset(0, 2)
    
If Equipment <> "" Then
    If wks <> "" Then
        mFormula = "SUMPRODUCT((A1:A100=""" & Equipment & _
                       """)*(B1:B100=" & wks & "))"
        mCount = Application.Evaluate(mFormula)
        If mCount > 1 Then Equipment.Offset(0, 1).Interior.ColorIndex = 50
        Cells(x, 5).Value = mFormula
    End If


    If EquipmentNumber <> "" Then


        mFormula2 = "SUMPRODUCT((A1:A100=""" & Equipment & _
                       """)*(c1:c100=""" & EquipmentNumber & """))"
        mCount2 = Application.Evaluate(mFormula2)
        If mCount2 > 1 Then Equipment.Offset(0, 2).Interior.ColorIndex = 50
        Cells(x, 5).Value = mFormula
    End If
    
End If
Next
End Sub

Display More

Regards

Maqbool

Maqbool · Nov 9th 2013

Re: Macro to open files using keywords

Hi Miheer:

Little bit confused...

Do you want to open files one after another?? how many files you are trying to open at a time?

Can you post a sample workbook of your input?

Regards

Maqbool

Maqbool · Nov 8th 2013

Re: Dynamic Chart, Using Original Table of Data for Multiple Charts AFTER Sorting

1. Create the a range name for source data. In the sample workbook I did it on sheet1.

Quote

Am I using the data from the query as the source data for my graphs

Yes - the query will be the data source. The chart will change according to your criteria. So that only 2 charts can plot in the sheet at a time. One for Test Result 1 and one for Test Result 2. My assumption was you need to create the chart according to the criteria selection.

If you need to create multiple charts at a time you need to create different queries. Each query you can limit the fields that are needed to plot the chart. So you can do it in one sheet. No need to display all the fields in the second sheet. And your original data will be intact and no need to sort each time.

And thank you very much for the willingness to pay. But I dont expect any thing for helping here. Thank you very much.

Best Regards

Maqbool

Maqbool · Nov 7th 2013

Re: Dynamic Chart, Using Original Table of Data for Multiple Charts AFTER Sorting

Hi

Since you need an XY scatter chart.. here is another solution for you. I have created this using Microsoft Query.
To work this file - save this file in a folder called OZGrid on your C drive. You dont have to do any thing on this file.
You can try the following on another dummy file. I have not created any charts since you know how to create it.

1. Create a named range on your original file. Should not refer to table. (see named range - TblData)
2. Select Data from the ribbon then "From Other Sources" from Get External Data tab.
3. Click on Microsoft Query
4. Select Excel Files and Clik OK (screen shot 2)
5. Choose the same File as source file. Query wizard will prompt with the named range
5. Follow the wizard unti the last step. Select View Data or edit query in Microsoft Query (screen shot 2). No need choose criteria option and sort option.
6. Select the criteria as shown on the screen shot 3. (While entering these parameters it prompt.. Enter a value to it)
7. Save the query in a folder and then from File menu select return to Excel.
8. Select a new sheet and place the curson leaving two rows on the top
9. Once the data placed on the sheet then right click and select "Refresh"
10. On prompting for parameters refer to the cells where your criteria resides and select "Use this value/reference for future refresh and Refresh automatically when cell value changes
11. Now when you change the cell values on row 2, the data will get updated and your charts also.

Best Regards

Maqbool

Maqbool · Nov 7th 2013

Re: Dynamic Chart, Using Original Table of Data for Multiple Charts AFTER Sorting

Hi

Can you please clarify the following
1. Do you need to plot only scattered charts? Or any other type will fine with you. We can not plot scattered charts with pivot table.
2. When you use age as a variable - are you entering it like >= age and <= age? or do you want to plot the charts of all ages for disease type or Gene 1 Copy #, (in otherword, your x value will be always all the ages within the selected disease type or Gene1copy#?)

Regards

Maqbool

Maqbool · Nov 6th 2013

Re: Dynamic Chart, Using Original Table of Data for Multiple Charts AFTER Sorting

Hi

Is it possible to see a sample workbook with the expected charts. I will have a look then.

Regards

Maqbool

Maqbool · Nov 5th 2013

Re: Macro to open files using keywords

Hi

It seems that you don't have a file with the fund name you mentioned on the B2.

Quote

"C:\Admin\USD_Cash__1013.xls" not found

From the above line it seems, you left B2 blank. Fund name not mentioned in the B2.

Regards

Maqbool

Maqbool · Nov 5th 2013

Re: Dynamic Chart, Using Original Table of Data for Multiple Charts AFTER Sorting

Hi

If you have properly arranged data table it is very easy to create a pivot table. Keep your cursor on the data table and on the ribbon select Insert and choose Pivot Table. Select the fields you want in the wizard and have a look in the following link.

http://www.contextures.com/

Regards

Maqbool

Maqbool · Nov 5th 2013

Re: Calculating averages on multiple spreadsheet

Hi

Try Pivot Table. By pressing ALT+D+P+A you will get prompt for selecting different ranges from different worksheets. See the below link.

http://http://blog.contextures.com/archives/2010/04/16/how-to-create-an-excel-pivot-table-from-multiple-sheets/

Regards

Maqbool