Posts by EnginerdUNH

Hi Jeff,

Thank you for your response. I'm not quite sure your suggestion will work for what I'm trying to do though. I don't care about the ones that are empty and the ones that have more than one element are an array (i.e. I only care about the scalar elements). If I do something like:

Dim Possible(1 To 9, 1 To 9) As Variant
   
   Dim L As Long
   Dim i As Long, j As Long
   
   For i = LBound(Possible, 1) To UBound(Possible, 1)
      For j = LBound(Possible, 2) To UBound(Possible, 2)
      
         If Not (IsEmpty(Possible(i, j)) Or InStr(TypeName(Possible(i,j)), "(") > 0) Then
              'scalar, do some stuff here
         End If
      Next j
   Next i

will this work the way I expect it to?

Hi,

Within my code, I have an array called Possible which is an array of type Variant with size (1 to 9, 1 to 9). Each element within the array can either be:

1) empty

2) a single element

3) multiple elements

what I am looking to do is loop though each element within Possible and see if the number of sub-elements in each position is 1 or not. I have tried using the following code but it is giving me an error:

For i = 1 to 9
    For j = 1 to 9
        If UBound(Possible(i,j)) = 1 then
            'do some stuff here
        end If
    Next j
Next i

Thank you, Logit, one question though... if the error occurs, it would be occurring within the function that I want to restart. I don't think I'm able to call a function from itself, am I?

Hi,

I am wondering if it is possible to write an error handler such that when my main body of code calls a particular function, if while the function is running, an error occurs, the code restarts the function.

Bump

Hi,

So I have the following scenario I’m trying to test out:

There is a multi-stage contest with 30 participants. Participant 1 has a goal of finishing the competition in the top 10. The first round of the competition is complete and Participant 1 would like to determine the highest number of points they can obtain in the next round in order to achieve 10th place or higher.

The assumptions are:

Participant 1’s place after round 2 is >10

The scores of all participants in each round are x>=1 and x<=30

Scores must be integer values

Ties are allowed and the next highest place would take skip a place (e.g. 1, 1, 3, etc.)

is this something that is possible using the Solver tool or do I need to do something more robust using formulas/VBA?

Is it possible to use a formula to do what I’m looking for without always having to have the other workbook open to do it? Or would that only be possible with a macro?

Thank you Pecoflyer for your reply! Ok I’ve dabbled at trying to revise my formula and I’ve come up with the following:

=“‘’”&INDIRECT(C1)&”[“&INDIRECT(C2)&”]”&”Sheet Name’”&INDIRECT(cell to grab)

And it’s no longer bringing up the file explorer when I hit enter but now I get a #REF! error. Any ideas what I’m doing wrong??

Hi,

I am wondering if it is possible to use a helper cell to update the file path/name for some linked data that I am working with. Right now I have several cells which are formatted as follows:

=‘File Path[File Name]Sheet Name’!Cell Ref

and I would like to know if there is a way that I can modify this to reference a cell that contains the file path instead. The reason for this is that I am looking to use this file for multiple sets of data which have the same sheet name and cell reference that I would be working with but the file path and file names would obviously change. I was trying to do something like the following:

=‘&C1&”[“&C2&”]Sheet Name’!Cell Reference”

but every time I go to enter that, excel immediately brings up the file explorer for me to select the file I want to link to.

That works out extremely well for me! Thank you!! Also, I realize that I meant to put this thread under the formulas section so thank you also for answering it despite the fact that I put it in the wrong area. The only change that I made with what you sent was I removed the drop down box that you added for year selection since I want to be able to put it other options besides just the years in the first row of the table. I tested it out with entering numbers like 6, 9, 13, etc. and it still works the way I want it to. Thank you again for your help

Hi,

I have an array(1 to 5) variant type variable and am trying to create a macro that will check to see if all spots within the variant array are filled or not. Is there a way that I can accomplish this with a simple if statement like:

if there are no empty spots in array(1 to 5) then

'do some code here

End If

without having to loop through the array every time to check it? Is the only way to accomplish this using something like:

If Not IsNull(array(1)) And Not IsNull(array(2)) And Not IsNull(array(3)) And Not IsNull(array(4)) And Not IsNull(array(5)) Then

'do some code here

End If

Roy,

Thank you for your reply. Please see the stripped down workbook with only the pertinent information included for this question.

I was able to get the following formula to work to sum up all rows in the correct column using SUMPRODUCT and HLOOKUP:

=SUMPRODUCT(HLOOKUP(G2,P1:U13,{2,3,4,5,6,7,8,9,10,11,12,13},TRUE)

Where G2 is the value used to pick the correct column and P1:U13 is the lookup table. But when tried to make the stuff in curly brackets dynamic by entering the following into a helper cell

=IF(B2<N2,”N,”,””)&IF(B2<N3,”3,”,””)&IF(B2<N4,”4,”,””)&IF(B2<N5,”5,”,””)&IF(B2<N6,”6,”,””)&IF(B2<N7,”7,”,””)&IF(B2<N8,”8,”,””)&IF(B2<N9,”9,”,””)&IF(B2<N10,”10,”,””)&IF(B2<N11,”11,”,””)&IF(B2<N12,”12,”,””)&IF(B2<N13,”13,”,””)

And then changing the static part of my original formula to “{“&LEFT(M19,LEN(M19)-1)&”}” but now I’m getting #REF! error.

Hi,

Let’s say I have a column like the following:

I need to develop a formula that will sum all rows whose date in column A is greater than today for the column that I specify in a given cell.

Well I think I solved the issue of the INDIRECT formula...turns out I needed to format it like INDIRECT("'"&Ref Cell&"'!Range")

Hi,

I have a spreadsheet with multiple sheets that each contain a table of data which contains a series of dates and other criteria with which I want to do a comparison. The basic structure of the columns is as follows: date, criteria1,criteria2, criteria3,desired value column.

I am trying to create a formula that will pull the value from each table that corresponds to the closest date less than or equal to the date specified that meets the desired value in the columns for criteria1, criteria2, and criteria3. I though about using the SUMPRODUCT formula since it will make all values that don't meet one or more of the criteria equal to zero with the structure =SUMPRODUCT(('Sheet Name'!A2:A20<=date desired)*('Sheet Name'!B2:B20=criteria1 value)*('Sheet Name'!C2:C20=criteria2 value)*('Sheet Name'!D2:D20=criteria3 value)*'Sheet Name'!E2:E20)

My concerns with this formula are:

1) What happens if there are multiple date values that meet the criteria values provided for criteria1, criteria2, criteria3? How do I tell the formula to only give me the most recent value for this subset?

2) The above example formula is not dynamic and I attempted to modify this by using the INDIRECT formula since I have the sheet names listed within cells on the sheet I am trying to use the above formula but excel kept throwing a "#REF!" error. It would also not work when I tried to use the INDIRECT formula to reference a single cell rather than a range. Does this have anything to do with whether or not the sheet name has a space in it?

I also thought about using a combination of the INDEX and MATCH formulas as follows {=INDEX('Sheet Name'!E2:E20,MATCH(1,('Sheet Name'!A2:A20<=date desired)*('Sheet Name'!B2:B20=criteria1 value)*('Sheet Name'!C2:C20=criteria2 value)*('Sheet Name'!D2:D20=criteria3 value))} again replacing the Sheet Name callout with a dynamic reference to to the cell containing the sheet name using the INDIRECT formula.

I have been working VBA to scrape website HTML code using the Microsoft HTML Object Library and the Microsoft Internet Controls Library. Now that support for Internet Explorer is being phased out, I am trying to switch my code over to scrape Google Chrome using the Selenium Type Library which is an open source download. The website I am trying to pull data from into excel has the following line of HTML code that I am interested in assigning to a variable:

<div jsname="iXWWee" class="enWFYd KDN9Hf" style="left: 39px; display: block; transform: translate3d(85px, 0px, 0px);">Feb 4, 2021</div>

I have been able to successfully assign a value to a variable using the following lines of code

Private ch As Selenium.ChromeDriver
Sub Test()
    Set ch = New Selenium.ChromeDriver
    
    ch.Start , "https://www.google.com"
    ch.Get "https://www.google.com/search?q=us+vaccine+tracker&oq=us+&aqs=edge.0.69i59l2j69i57j69i60l4.2010j0j1&sourceid=chrome&ie=UTF-8"
    
    Dim Element As Selenium.WebElement
    Set Element = ch.FindElementByCss("div[class='enWFYd KDN9Hf']")
    Debug.Print Element.Attribute("innerHTML")
end

But what you will notice is that the the HTML code I provided has innerHTML which changes based on where your cursor is on the screen. The code above works but it only pulls the last instance where the innerHTML value is "Aug 3, 2021". What I want is something that will pull all of the data and not just the last instance. I tried to test it by changing the second to last line of code to Set Element = ch.FindElementByCss("div[class='enWFYd KDN9Hf'] [innerHTML='Feb 4, 2021']") but when I run the subroutine, I get an error saying that this element can't be found. I have also tried changing the last three lines as follows but it still is only pulling the last instance of this div tag.

Dim Elements As Selenium.WebElements
Set Elements = ch.FindElementsByCss("div[class='enWFYd KDN9Hf']")
For Each Element in Elements
    Debug.Print Element.Attribute("innerHTML")
Next Element

Am I not setting up my Css selectors correctly? Anybody that is familiar with how to use Selenium with VBA, I would greatly appreciate your help.

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???