Posts by EnginerdUNH

With a little trial and error, I think I found a formula for a named reference that will work to replace the A$1:A$9 in the formula to make it dynamic: =OFFSET($A$1,0,0,COUNTA($A:$A)-1,1)

I am working with the following formula that calculates an average value, excluding the first difference: =SUMPRODUCT((A$2:A$10=A2)*(A$1:A$9=A$2:A$10)*N(+C$2:C$10)/(COUNTIF(A$2:A$10,A2)-1)) which works really well when I have a static range that I'm not adding to. What I want to do now is create a dynamic formula that works with a table so that the formula will work regardless of how much data I decide to work with. The problem that I am having is that I am having is I can change A$2:A$10 to Table1[Date] and C$2:C$10 to Table1[Difference] but I can't figure out how to address the range A$1:A$9. Is there a way that I can set up a named range that automatically updates? or use the just incorporate the OFFSET formula into the formula that I'm modifying?

Hi,

I am hybrid Gantt chart of sorts. I'll try to explain what I mean by that . Let's say I have the following test data that I'm working with:

What I want to do is create a hybrid Gantt chart where the tests which have a start date and duration are represented by bars and the tests which only have start dates (meaning they have not completed) are represented by a symbol. I have created the basic Gantt chart structure by creating a stacked bar chart with the test name on the vertical axis and the start date and duration on the horizontal axis with the date set to no fill. I then created a new data series where the tests with durations are represented by N/A and the dates without durations are the start dates. But this is where it stopped working for me...When I first added the new data series, it displayed the data as bars going from the start data and extending all the way to the max value of the date axis. When I tried to change the chart type for the data series to a line chart with data markers, it added a second vertical axis where that axis is all dates. Can anyone help me???

Quote from royUK

Try this

Code

Dim oWb As Workbook
Dim AutoSec As MsoAutomationSecurity
AutoSec = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable


Set oWb = Workbooks("Desktop\Book2.xls").Open
'' your code here
oWb.Close True ''/// saves workbook, change to False if you don't want to save


Application.AutomationSecurity = AutoSec

Display More

Thank you Carim and Roy! Roy, quick question for you...The piece of code that you provided about changing the automation security settings, should that be placed at the very beginning and very end of my code (i.e. before I've initiated the loop to go through each file and just before I'm about to end the sub routine) or should it be placed within the loop?

I am working on a macro that will loop through a series of workbooks, open them, compare their contents to the reference cell in the workbook that the macro works off of, and then do stuff in the main workbook if condition is met. The thing that I'm running into is that workbooks that I'm trying to open using my macro are macro-enabled workbooks and every time a new one is opened by the macro, a prompt comes up to either enable or disable macros. This is ultimately time consuming because I'm having to press either enable or disable every time a new workbook is opened. Is there a way that I can make it so that excel doesn't keep asking me this every time there's a new workbook being opened? either in the settings for excel itself, my macro code, or something else?

Quote from Carim

As far as the generic structure of your Array Formula is concerned ...

You can adapt following formula to your specific situation

Code

=MEDIAN(IF((yourRange>=LowerLimit)*(yourRange<=UpperLimit),yourRange))

Hope this will help

Display More

Thanks Carim! I'm not sure why my formula wasn't working even though I was pressing CTRL + SHIFT + ENTER after constructing the formula. Nevertheless, I tried your formula and for some reason excel liked your way better. Now I actually get what I'm trying to find. Thanks for the help!

,Hi,

I'm working with data where I've been given a table of dates and times corresponding to different events. What I am trying to do now is group those times together to find both the percentage of events that occur during specific time range and then to also find the median event time for that range. For example, I need to find both the number of events occurring between 12:00AM - 5:59AM, 6:00AM to 11:59AM, 12:00PM - 5:59PM and 5:59PM - 11:59 PM. Finding the number of events was easy, I just used a simple COUNTIFS statement to count all times between the bounds of each bin. As for the median time calculation, I'm getting stuck. I tried using the following formula to no avail: MEDIAN(IF(AND(Table1[Time]<=X2,Table[Time]),Table1[Time])) where Table1[Time] represents the entire set of time data in Table1 and X2 and Y2 represent the first bin of time range (12:00AM - 5:59PM) but it kept throwing back what appears to be the median of the entire data set. I was thinking I'm better off trying to come up with a macro that will loop through each of the data points and using and if statement, add them to the correct array to then take the median of. Any insight and help on structure/syntax is greatly appreciated.

Quote from Fluff13

If the values are numbers you can use COUNT rather than COUNTA, if the values are text use

=LOOKUP(2,1/(E2:E10<>""),E2:E10)

COUNT is exactly what I needed!! I realized after I posted this that I posted under the VBA section of the forum rather than the excel formulas section but thank you for providing me the answer that I needed, Fluff13.

I'm sure I'm not the first person to have this question but I haven't been able to find anything online to help me solve my problem. I have a table of data and I need to pull the data from the last non-blank row in one of the columns in my table. Sounds simple enough but that's where you would be wrong. Normally I would be fine using =INDEX(Table1[Column X],COUNTA(Table1[Column X])) to find the last non-blank row of data. The problem with is that the column that I need to pull data from in the table that I'm working with isn't truly blank. Instead, there's a formula which is telling it to check for a certain condition, if that condition is met then spit out a value else the value in that cell is "". Even though the value in that cell may be "", since there is a formula which tells excel how to populate that cell, when using INDEX and COUNTA, excel treats the cell as if it's not blank and returns the wrong answer. What I need to figure out is how to find the last row which contains something other than "". For example, let's say I have the following table:

In the example table, let's say I need to pull data from the "Sum" column. In the example, the sum column would be populated with =IF(SUM(Data 1:Data 3)>0,SUM(Data 1:Data 3),""). If I were to use =INDEX(SUM,COUNTA(SUM)) then excel would return "" because April 2020 shows "" and is not truly blank because there is a formula inside the cell. What excel SHOULD be returning is March 2020's value of 13.

After a little hunt...and an accidental mouse click...I have found where worldometers is keeping their data for the historical charts!!! For anyone who is interested, the data can be accessed by telling your macro to look through worldometers HTML for any script tags. I have used the following which gives me access to the data that I need

Sub test2()
    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLScripts As MSHTML.IHTMLElementCollection
    Dim HTMLScript As MSHTML.IHTMLElement
    
    IE.Visible = True
    IE.navigate "https://www.worldometers.info/coronavirus/country/us/"
    
    Do While IE.readyState <> READYSTATE_COMPLETE
    
    Loop
    
    Set HTMLDoc = IE.document
    Set HTMLScripts = HTMLDoc.getElementsByTagName("script")

    colnum = 1
    For Each HTMLScript In HTMLScripts
        If HTMLScript.innerText <> "" Then
            txt = Split(HTMLScript.innerText, ",")
            For i = 0 To UBound(txt)
               Cells(i + 1, colnum) = txt(i)
            Next i
            colnum = colnum + 1
        End If
        Debug.Print HTMLScript.innerText
    Next HTMLScript
End Sub

All I have left to do now is just clean it up a bit more.

Hi Alan, first of all, thank you for your reply! I looked into using a Power Query (something I had never heard of before Saturday) as it was a technique that was posted on another forum. Unfortunately, a Power Query won't work for what I am trying to do. The Power Query tool will only grab tables from the given website which is ok if all you want to do is look at the current numbers. In my case, I care about all the historical data which unfortunately is changing every couple of days it seems so the single day historical data that you may have gotten for a week ago, probably isn't correct anymore, unless all you're doing is just refreshing and looking at the current day and then you don't care about where you were on other days. What I need to be able to do is find a way to get the historical data off of the charts listed below the tables of the current day's and yesterday's data which as far as I can tell, they only provide you with a few sources of where the data comes from but not the data that makes up the charts and I have not been able to figure out how to extract the data from the charts themselves.

Hi,

I have been tracking all of the COVID 19 data for the US and my particular state since very early this year and especially as of recently, I have found it to be very time consuming because it seems like every couple of days, the reported number of total cases, active cases, and deaths for the country will change for days that were previously reported making it a hassle to have to go back and confirm the data for every day I've already entered. I am using two different websites to get my data, https://www.worldometers.info/coronavirus/country/us/ for my US historical data and https://covidtracking.com/data/state/connecticut for my particular state's historical data. Coming up with the code to pull up the website for my state, find the historical data table and pull the data was relatively easy because they have the data set up exactly as that, in tables.

Worldometers on the other hand, has a single table at the top of the page which has two hyperlinks or tabs basically which show the current day and previous day's data for each state. For the historical data, the site displays the data on a series of interactive charts which allow the user to view the highlighted day's data by hovering over the data point with their cursor. Because this is not a table, I have having trouble with trying to figure out how to extract the individual data points like I was able to do from the table in my state's historical data. I have provided a screenshot which shows both how the data is displayed when a user hovers over a particular data point as well as the respective HTML code for that data point. I'm not sure if there is a way to scrape all of the data together and then I can go through and organize/analyze it how I want to or if I am better off trying to scrape each individual point separately. I am relatively new to HTML scraping so I apologize in advance if I don't say something as clear as you would like and I can gladly try and explain things differently.

Wow Bosco thank you so much!! That is exactly what I was looking for. My last question though is the formula you gave me assumes that all I have is 10 data points and that the data never changes size. The data I'm working with is in a table that can change in size if more information is added. I modified your formula for my purposes to reference the Table[Date] and Table[Difference] instead where you mention A2:A10 and C2:C10 in your example. My question for you is how do I modify the formula to handle the A1:A9 when the table changes in size?

Quote from bosco_yip

Try...………………...

1] In C2, formula copied down :

=IF(A2=A1,B2-B1,0)

2] In D2, formula copied down :

=AVERAGEIFS($C$2:$C$10,$C$2:$C$10,"<>0",$A$2:$A$10,A2)

Display More

Bosco, as I stated in my original post, I cannot use the formula you are proposing for column C. I must calculate the difference between the current and previous value for all data points not just the ones which occur on the same day. The question was how to arrange a formula which will allow me to calculate the daily average of those differences, excluding the first value for each day.

Hi,

I am working with data that has a a series of dates and then data for each date. I then take each number from the data column and take the difference between each value and the value in the row above it. What I am looking to do is now take the average of those differences based on the corresponding date but I need to exclude the first difference for each date from the average since that difference is based on the difference between data on two days. I know I could just use a formula which would skip calculating a difference if the dates don't match and then calculate an average using the AVERAGEIF function but I need to keep the rows with differences between dates for another formula later on. Below is a table of dummy data showing what I would expect for the averages column. Please note that as I have shown below, the formula needs to be dynamic meaning that each day may a different number of data rows to average compared to the previous day or next day.

Quote from iwrk4dedpr

Are you still having issues?

Hi Barry, yes I am. Maybe you can help??

[align=left][COLOR=#000000][FONT=Arial]Set HTMLInputs = HTMLDoc.getElementsByTagName("input")[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]   For Each HTMLInput In HTMLInputs[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]       Debug.Print HTMLInput.className, HTMLInput.ID[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]   If HTMLInput.className = "form-control input-lg" And HTMLInput.ID = "form-email" Then[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]       HTMLInput.Value = "XXXXXXXX"[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]   End If[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]   If HTMLInput.className = "form-control input-lg" And HTMLInput.ID = "form-password" Then[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]       HTMLInput.Value = "*****************"[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]   End If[/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial]Next HTMLInput[/FONT][/COLOR][/align]

The issue that I'm having now is that when I run the code fully, I get no output from the debug.print line. When I run it line by line, however, the code spits out exacly what it should and does exactly what I need it to do.

Ok so I've figured out how to get the username and password into the correct input boxes on the site using the following code. The problem that I'm experiencing now is that the code works when i step through it using F8 and the fields are populated with the right info but when I try to run the code completely without using F8, I get a "Object variable or With block variable not set" error. Can anyone tell me why I'm getting this error?

[align=left][COLOR=#000000][FONT=Arial][SIZE=13px]'Prompt user to enter in their login info and login to website[/SIZE][/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial][SIZE=13px]   Set HTMLUser = HTMLDoc.getElementById("form-email")[/SIZE][/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial][SIZE=13px]   HTMLUser.Value = "xxxxxxxxx"[/SIZE][/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial][SIZE=13px]   Set HTMLPWord = HTMLDoc.getElementById("form-password")[/SIZE][/FONT][/COLOR][/align]
    [align=left][COLOR=#000000][FONT=Arial][SIZE=13px]   HTMLPWord.Value = "**********"[/SIZE][/FONT][/COLOR][/align]

Hi,

I am new to HTML web scraping but let me try to summarize what I'm trying to do real quick before I get into my problem. I need to be able to perform the following steps on a website called marketingscents.com using VBA:
1. Load the webpage
2. Click the login button
3. Enter username and password
4. Click login button
5. Access needed information

I have been able to perform the first 2 steps using the following code

'navigate to marketing scents
    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLAs As MSHTML.IHTMLElementCollection
    Dim HTMLA As MSHTML.IHTMLElement
    Dim HTMLInputs As MSHTML.IHTMLElementCollection
    Dim HTMLInput As MSHTML.IHTMLElement


    IE.Visible = True
    IE.navigate "http://marketingscents.com/"
   
    Do While IE.readyState <> READYSTATE_COMPLETE
       
    Loop
   
    'navigate to login page
    Set HTMLDoc = IE.document
   
    Set HTMLAs = HTMLDoc.getElementsByTagName("a")
   
    For Each HTMLA In HTMLAs
        'Debug.Print HTMLA.getAttribute("classname"), HTMLA.getAttribute("href")
        If HTMLA.getAttribute("classname") = "header__button button button--secondary button--large button--login" And HTMLA.getAttribute("href") = "https://www.marketingscents.com/index?page=login-new" Then
            HTMLA.Click
            Exit For
        End If
    Next HTMLA

Where I am getting stuck is on Step 3. I have combed through the HTML provided below and determined that the username and password fields that I need are input boxes.

<html lang="en"><head><base href="https://www.marketingscents.com/usercontent/65307188/homepage/">


    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Login - Marketing Scents</title>
    <link href="/usercontent/65307188/homepage/favicon.ico" rel="shortcut icon" type="image/x-icon">
    <link href="http://fonts.googleapis.com/css?family=Open+Sans:300italic,400italic,600italic,700italic,400,300,600,700" rel="stylesheet" type="text/css">
    <link href="media-login/css/bootstrap.min.css" rel="stylesheet">
    <link href="media-login/css/style.css" rel="stylesheet">
   
        <!-- GA Tracking Code -->
   
    <script src="https://connect.facebook.net/en_US/fbevents.js" async=""></script><script src="https://www.google-analytics.com/analytics.js" async=""></script><script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','https://www.google-analytics.com/analytics.js','ga');


  ga('create', 'UA-91740248-1', 'auto');
  ga('send', 'pageview');


</script>
   
    <!-- End GA Code -->
   
      <!-- Facebook Pixel Code -->
<script>
  !function(f,b,e,v,n,t,s)
  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?
  n.callMethod.apply(n,arguments):n.queue.push(arguments)};
  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';
  n.queue=[];t=b.createElement(e);t.async=!0;
  t.src=v;s=b.getElementsByTagName(e)[0];
  s.parentNode.insertBefore(t,s)}(window, document,'script',
  'https://connect.facebook.net/en_US/fbevents.js');
  fbq('init', '1720644064910435');
  fbq('track', 'PageView');
</script>
<noscript>&lt;img height="1" width="1" style="display:none"
  src="https://www.facebook.com/tr?id=1720644064910435&amp;ev=PageView&amp;noscript=1"
/&gt;</noscript>
<!-- End Facebook Pixel Code -->
   
   
  </head>
  <body>
    <div class="alert alert-header alert-danger" id="js-page-message">
      <div class="container">Something went wrong. Please try again, or <a href="javascript:openBrWindow('/index/lookup','_blank','height=420,width=350,status=no,menubar=no,location=no,scrollbars=yes');">reset your password</a>.</div>
    </div>
    <div class="container">
      <div class="panel">
        <div class="panel-header">
          <img class="logo" alt="" src="/usercontent/65307188/homepage/media-new/img/logo-color.png">
        </div>
          <form id="js-login-form" role="form" action="//www.mscbackoffice.com/index/login" method="post" data-csrf-added="1">
<input name="CSRFToken" type="hidden" value="ko733ujmr1v5rdee599u4d756gbp4oj4o3g8b0ai9sah2oi1v6dhd8bg8ubv1vbp-q2DKtcs2WwFCs5kfYCTTOYi+pDQVIjumO0uEL0+thTc=">


  <input name="login_page" type="hidden" value="login-new">
          <div class="form-group">
            <label for="form-email">Member ID</label>
            <input name="username" class="form-control input-lg" id="form-email" type="text" placeholder="Enter Member ID">
          </div>
          <div class="form-group" style="margin-bottom: 10px;">
            <label for="form-password">Password</label>
            <input name="password" class="form-control input-lg" id="form-password" type="password" placeholder="Enter Password" value="">
          </div>
          <div class="form-group remember-me">
                   <a class="pull-right" href="javascript:openBrWindow('/index/lookup','_blank','height=420,width=350,status=no,menubar=no,location=no,scrollbars=yes');">Forgot Password?</a>
            <input name="remember_me" id="remember_me" type="checkbox" value="1">
              <label for="remember_me">Remember Me</label>
          </div>
          <button class="btn btn-lg btn-primary btn-block" id="js-login-btn" type="submit" value="Login">Log in</button>
        </form>
      </div>
    </div>
 
 
  <script src="media-login/js/jquery-1.11.1.min.js"></script>
  <script>
   
  function openBrWindow(theURL,winName,features) {
    window.open(theURL,winName,features);
  }
 
  </script>
</body><!-- Minify and concatenate when dev is done --></html>

However, I can't even get my VBA to print the name of all the input fields on the page using Debug.Print. I am using the following code.

'Prompt user to enter in their login info and login to website
    Set HTMLInputs = HTMLDoc.getElementsByTagName("input")
    For Each HTMLInput In HTMLInputs
        Debug.Print HTMLForm.getAttribute("name")
    Next HTMLInput

Can anyone tell me what am I doing wrong???

I was able to figure out what was going wrong with my code. I fixed it to the following:

Sub DateTest()
    'calculates the number of days a current action has been active based on today's date and the date provided in the Current Action Start column (column D)
    lastrow = Range("A" & Rows.Count).End(xlUp).Row 'find last row which contains any data
    Dim TodayDate As Variant
    TodayDate = Date 'what is today's date? used for determining number of days active
    For j = 2 To lastrow 'iterate through all rows with data to determind days active
        If Range("D" & j).Value = "N/A" Then 'if no current action is active
            Range("E" & j).Value = "N/A"
        Else 'if there is at least one date in the Current Action Start column
            DateStr = Split(Range("D" & j).Value, Chr(10)) 'create an array of all dates in current action start date column for current row
            DateVal = ""
            DaysActive = ""
            For k = LBound(DateStr) To UBound(DateStr) 'iterate through DateStr array
                DateVal = TodayDate - DateSerial(Year(DateStr(k)), Month(DateStr(k)), Day(DateStr(k)))
                DaysActive = DaysActive & Chr(10) & DateVal
            Next k
            DaysActive = Right(DaysActive, Len(DaysActive) - 1)
            Range("E" & j).Value = DaysActive
        End If
    Next j
End Sub