User Posts: Mohammad Shah Miran
0
How to Use Conditional Formatting If Statement Is Another Cell
1

Conditional Formatting is an excellent feature to format your worksheet based on specific criteria. For instance, you can use Conditional Formatting to ...

0
How to Use NPER Function in Excel (3 Relevant Examples)
1

Financial analysts frequently want to know the duration of repayment time while making a business deal. Therefore, if you are a financial analyst, you might ...

0
How to Fill Drop-Down List Cell in Excel with Color but with No Text
1

In some specific cases, you might have to use the spotlight color to make a remark in your Excel Sheet. To make the task more interactive, you can create a ...

0
How to Do Correlation and Regression Analysis in Excel
1

In statistical analysis, performing Correlation and Regression calculations is the popular way to analyze and predict future events. However, there are several ...

0
How to Use DSUM Function with Dynamic Criteria in Excel
1

Excel’s DSUM function is used to extract and sum a set of data from a database. It computes the sum of a set of data that meets certain criteria. In this ...

0
How to Create Stacked Bar Chart with Negative Values in Excel
1

Creating a Stacked Bar Chart is an easy way to depict your data and compare them concisely. This article can help you if you're trying to figure out how to ...

0
How to Use SUMIF and AND Function in Excel (2 Easy Methods)
1

SUMIF and AND are the most frequent functions in everyday life when we work in Microsoft Excel. If you are an Excel enthusiast, then you might already work on ...

0
How to Calculate Variance and Standard Deviation in Excel
1

Both Variance and Standard Deviation are popular terms in statistical analysis. Do you know Excel provides multiple opportunities to calculate those in your ...

0
How to Make Dynamic Drop Down List from Another Sheet in Excel
1

When you work with a large set of data, your data may disperse in several sheets of your Excel file. A dynamic drop-down list can help you to accumulate that ...

0
How to Use Formula to Calculate Car Depreciation in Excel
1

For a number of tasks and uses, including financial analysis to sales forecast, Excel will continue to be a crucial tool for enterprises. For instance, you can ...

0
How to Create Automatic Schedule Generator for Free in Excel
1

Excel is essential to business analysis because it offers a variety of ways to store and analyze data. In addition, it is a practical, cost-effective, and ...

0
How to Make Decision Tree Algorithm in Excel (3 Easy Examples)
1

Suppose you are in a situation where you need to make a certain decision based on some specific condition. This is a probabilistic analysis thus you have to ...

0
How to Create a Networking Catalogue in Excel (with Easy Steps)
1

Networking equipment such as switches, routers, wireless, etc is well-known in the technology industry. To promote the product, companies may often require to ...

0
How to Create a Sales Pipeline Funnel in Excel (with Easy Steps)
1

Certainly, having a clear understanding of the sales pipeline funnel can help you to understand your potential customer’s behavior throughout the purchasing ...

0
How to Make an Effort Estimation Sheet in Excel (4 Easy Methods)
1

Excel plays a vital role in business analysis as it opens a wide range of opportunities to store data and analyze them accordingly. Apart from that, it is a ...

Browsing All Comments By: Mohammad Shah Miran
  1. Reply Avatar photo
    Mohammad Shah Miran Mar 19, 2023 at 4:59 PM

    Thank you SHASHI, for your query. I think this article might help to solve your problem.
    https://www.exceldemy.com/count-colored-cells-in-excel/#3_Utilizing_GETCELL_4_Macro_and_COUNTIFS_Functions
    You can try to use this method for Conditional Formatted cells also. Further, if you have any confusion or query related to it, please let us know.

  2. Reply Avatar photo
    Mohammad Shah Miran Mar 19, 2023 at 3:03 PM

    Thank you, Jeb, for your query. You can take the API as a String value to your VBA code. Thus you don’t need to put the API value in your Excel sheet. So the existing code given in the workbook here can be modified as follows:

     Option Explicit
    Public Function Driving_Distance(startlocation As String, destination As String)
    Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String, keyvalue As String
    
    keyvalue = "AhFG0hk5nKCcQlk80MRaSk1ZtoYUYsX98BCLWi7p7MKZ - VrzOWptdUwsvj9D3L9F"
    First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
    Second_Value = "&destinations="
    Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"
    
    Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value
    mitHTTP.Open "GET", mitUrl, False
    mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    mitHTTP.Send ("")
    Driving_Distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)
    End Function

    Here we have changed the first two line of the given code. The previous code was as like:

     Public Function Driving_Time(startlocation As String, destination As String, keyvalue As String)
    Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String 

    But we have removed the third argument which represents the API value. Alternately, We declare the variable outside and put the API as a string value.

     Public Function Driving_Distance(startlocation As String, destination As String)
    Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String, keyvalue As String
    
    keyvalue = "AhFG0hk5nKCcQlk80MRaSk1ZtoYUYsX98BCLWi7p7MKZ - VrzOWptdUwsvj9D3L9F" 

    Now here comes to your second question. You have to create a customized Add-in to store your VBA code and therefore allow you to execute the code in any workbook. Hope you get your answer. However, if you have any further query, please let me know.

  3. Reply Avatar photo
    Mohammad Shah Miran Mar 19, 2023 at 12:07 PM

    Thank you, Hossam for your query. I am not sure whether you alter AVERAGEIF with AVERAGEIFS. If so, there should be a difference between them as AVERAGEIF deals with single criterion whereas AVERAGEIFS deals with multiple criteria. However, you can use the AVERAGEIFS function for a single criterion by specifying only one criterion range and one criterion. For example, you have a range of cells A1:A10 that contains numbers and you want to calculate the average of the cells that are greater than or equal to 5. For accomplish your task, you can use the AVERAGEIFS function like this:
    =AVERAGEIFS(A1:A10, A1:A10, ">=5")
    Further if you have any query, please let me know. Thank you.

  4. Reply Avatar photo
    Mohammad Shah Miran Mar 9, 2023 at 3:19 PM

    Thank you VIRGIAL for your query. Yes, you can use the COUNTIF function to count the number of items that have broken the Conditional Formatting threshold value. For doing this, write down the following formula in your desired cell. (eg. D11)
    =COUNTIF(D5:D10, "<12000")
    Here, D5:D10 is the data range for which you want to set your condition and 12000 is the Conditional Formatting threshold value.
    Additionally, the following image can be useful to comprehend the task.
    counting items

  5. Reply Avatar photo
    Mohammad Shah Miran Feb 27, 2023 at 4:20 PM

    Thank you, Greg Erkins, for believing in ExcelDemy.
    Use the following Macro Code to get your desired output.

     Function UrlConstructor(FName, Rng)
    FName = "http://www." & FName & ".com/"
    Dim Arr() As Variant
    ReDim Arr(Rng.Rows.Count - 1)
    For i = 1 To Rng.Rows.Count
        Arr(i - 1) = FName & Rng.Cells(i)
    Next i
    UrlConstructor = Arr
    End Function
    

    To aim the output, I have written the function name UrlConstructor and called friendly name and range of country code as an argument, like the picture given below.
    Url Construction From Friedly Name
    Download the Excel file for your better assistance (URL Construction from Friendly Name.xlsm). Also, let us know if you wish to learn more or have any concerns relevant to it. Good luck!

  6. Reply Avatar photo
    Mohammad Shah Miran Feb 27, 2023 at 3:53 PM

    Thank you for getting in touch with us. Based on your comment, what i understand is that you are interested in creating a schedule to track your maintenance work. I have provided an Excel file (Machine Maintenance Schedule) that outlines the different types of work you will need to perform. However, I would like to confirm whether your MNT1 and MNT2 etc. tasks are repetitive, or if they only need to be completed once every 15 days.
    If you would like to automate this process, you can incorporate a nested for loop to generate the desired output. Additionally, you can develop a sub-function to prevent repetitive values from being generated.
    Further, if you have any questions or concerns about this matter, leave your query here. We are here to help and are at your disposal.

  7. Reply Avatar photo
    Mohammad Shah Miran Dec 29, 2022 at 1:10 PM

    Hello Imran,
    Thanks for the query. What I understand from your comment is that you want to incorporate a VBA code for the formula instead of going to the formula editor. Here are my two cents which might help you in this regard. Look at the dataset attached below.

    After pressing the ALT+F11 short key to open your VBA window, paste the following code in the Module box.

    Save & Close your VBA window. Then, press F8 to open the Macro dialog box and click on Options.

    Create a shortcut key to make the process fast, Crtl+W for instance.

    Now see the output as given below.

    Hope you have got your answer. Good Luck!

    Regards
    Miran
    Excel & VBA Content Developer

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo