Ask me anything about Microsoft Excel - Page 94 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

Hi, I've used Excel exactly 1 time in my life, but I was wondering if it could be used for what I want to do and how easy or difficult it would be.

I want to verify someone's sportsbetting picks record by checking the line history to see if the bets were available at the same odds they claimed to bet at (or better). I would be getting line histories from http://www.sportsbookreview.com/betting-odds/ - to get the line histories you hover over 3rd column (ex: "MLB Baseball" for MLB), click the "i", then click the "Line History" tab that pops up, and from there you can select the history from different books. I would only need to look up 1 sport from 1 book for my purposes.

In general, ease of scraping depends on how the pages are coded.

Looking at this site specifically, it's tricky. The line history data isn't loaded into the page until you actually click those buttons, so it's not as easy to get into Excel as it would be if the data tables just loaded when the page loaded. I'll look into it a little and let you know what I find.

Quote

06-03-2015 , 12:34 PM

#2327

BleetBleetImaSheep

enthusiast

Join Date: Dec 2014 Posts: 80

I'm trying to create a macro that will automatically pull all of the information from a spreadsheet, and drop it into the correct columns into a template.

E.g: Column "A" in the spreadsheet where the data originates represents client names. I want all of the client names from column A to transfer over to colum D in another template file. At the same time I was Column B in the data spreadsheet to transfer over to column F in the template file.

No calculations happen at all. It's basically a straight copy/paste.

Any help at all is appreciated.

Quote

06-03-2015 , 04:34 PM

#2328

lkasigh

adept

Join Date: Jun 2013 Posts: 953

You don't need to use a Macro. Just open up both at once and link them with formulas. Your formulas will something like this:

='C:\Desktop\[Book2.xlsx]Sheet1'!A1

You can then go to the tab Data-->Update Links to update the links whenever you want (or set it to update automatically).

Quote

06-04-2015 , 12:52 AM

#2329

Phresh

banned

Join Date: May 2006 Posts: 10,251

I have a running section in my spreadsheet to keep track of my hours each day. One cell shows a SUM of all the hours worked in that day. A cell always has date in standard format, B and C are start and end times and D is the total hours worked.

6/3/15 | 6:00 AM | 9:00 AM | 3.00
6/3/15 | 10:30 AM | 12:30 PM | 2.00

How do I have my "TOTAL HOURS WORKED TODAY" cell crosscheck if A column is today's date (which I keep listed in static column F1) and to sum all of the D cells that have the corresponding date?

Quote

06-04-2015 , 12:56 AM

#2330

NxtWrldChamp

Carpal \'Tunnel

Join Date: Feb 2008 Posts: 9,082

You will want to use sum if

=sumif(A:A,(date you want to retrieve),d:d)

if you always have today's date in cell F1 then wherever you want the total hours displayed enter this formula assuming its on the same sheet/workbook

=sumif(a:a,f1,d:d)

Quote

06-04-2015 , 01:07 AM

#2331

Phresh

banned

Join Date: May 2006 Posts: 10,251

I just Googled that and was coming back to edit. I feel like tons of the **** I ask here is just a simple SUMIF. Thanks though, Champ!

Quote

06-05-2015 , 08:07 AM

#2332

vaJAZzled

grinder

Join Date: Jul 2011 Posts: 547

I would like to print multiple tabs in a document where each tab will fit on one page but that all pages are to the same scale.

Which I guess implies that all pages have to be set to the scale of the tab with the most cells in the selected print area.

When I print this currently using "Fit Sheet on one page" it autoscales each tab to a best fit, so those with less cells in the defined print area look blown up relative to others.

Is there a quick way to achieve uniform scaling?

Thanks

Quote

06-05-2015 , 10:23 AM

#2333

CrazyEyez

Carpal \'Tunnel

Join Date: Sep 2003 Posts: 8,669

If my sheet fits on one page at 100% Excel never blows it up when using Fit Sheet On One Page for me. Basically 100% is the max.

But yeah if you wanted all the same scale you'd have to find out the smallest scale of the other pages then make everything match I guess.

Quote

06-08-2015 , 10:07 AM

#2334

Jbrochu

banned

Join Date: Jan 2005 Posts: 16,054

Having trouble with a simple macro.

The line below throws a runtime error 13 type mismatch.

Code:

           If Target.Value = "" Then

The cell it's checking for empty is formatted as a number.

The strange thing is the code works totally fine in the worksheet I originally tested it in. In that test worksheet I also made sure that the relevant cells are formatted as numbers.

Here is the entire sub. Any ideas?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("D31:D38")
    
    ' check if the changed cell is in the range of concern
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
           
           ' test if the cell is empty
           If Target.Value = "" Then
           
           ' if it is empty change the value of the cell to its 
           ' left to "None"
           Range(Target.Address).Offset(, -1).Value = "None"
           
           End If
       
    End If
End Sub

Quote

06-08-2015 , 02:07 PM

#2335

CrazyEyez

Carpal \'Tunnel

Join Date: Sep 2003 Posts: 8,669

I believe that happens if more than one cell is selected. It can't evaluate Target.Value if Target.Cells.Count > 1.

So either:

a) do nothing if attempting to change multiple cells (If Target.Cells.Count > 1 Then Exit Sub)

b) loop through each cell in Target and evaluate/handle independently

Quote

06-08-2015 , 02:24 PM

#2336

Jbrochu

banned

Join Date: Jan 2005 Posts: 16,054

It's not just that, because it works fine* (if I only change 1 target cell) if I change the code to,

Target.Value = 0

The code also works fine with = "" in a different worksheet within the same workbook.

*You are correct though, even in the worksheet where it works fine, if I change multiple target cells at once it throws the same runtime error. So it will need to be fixed to loop through regardless. Thanks for pointing that out.

Quote

06-08-2015 , 03:47 PM

#2337

ibavly

2010 PSOP Champion

Join Date: Feb 2010 Posts: 24,513

There's also an isempty function in vba

Quote

06-08-2015 , 04:21 PM

#2338

jh1711

No freakin title????????

Join Date: May 2012 Posts: 3,743

Quote:

Originally Posted by Jbrochu

It's not just that, because it works fine* (if I only change 1 target cell) if I change the code to,

Target.Value = 0

Cliffs: Use Target.Text.

When you change the code you are assigning something to the Value property. Its type is Variant (that's vba speak for whatever). When you use the equal sign in an if clause it's interpreted as a comparison operator. In that situation the types on both sides have to be the same. You can also use cstr(Target.Value) to cast the cell value to a string.

Both

Code:

If cstr(Target.Value) = "" Then

and

Code:

If Target.Text = "" Then

should work. You can decide what version, and what reason is more intuitive for you.

Quote

06-08-2015 , 04:33 PM

#2339

Jbrochu

banned

Join Date: Jan 2005 Posts: 16,054

Quote:

Originally Posted by ibavly

There's also an isempty function in vba

Tried this and nothing happens,

Code:

If IsEmpty(Abs(c.Value)) = True Then MsgBox c.Value

Switched to this,

Code:

If IsEmpty(Abs(c.Value)) = False Then MsgBox c.Value

And a blank message box pops up.

So for some reason when I have an empty cell* it evaluates that empty is false, even though it appears to be empty when the value is displayed in MsgBox.

*I'm assuming that a freshly deleted cell should be empty?

PS - my code looks a little different than the first example because I needed to change how I was accessing values in order to build in the looping required in case more than one cell is deleted at a time.

Quote

06-08-2015 , 04:35 PM

#2340

Jbrochu

banned

Join Date: Jan 2005 Posts: 16,054

Quote:

Originally Posted by jh1711

Code:

If cstr(Target.Value) = "" Then

and

Code:

If Target.Text = "" Then

should work. You can decide what version, and what reason is more intuitive for you.

Thanks. I need to go to school but I'll try that later tonight.

Quote

06-09-2015 , 12:51 PM

#2341

Jbrochu

banned

Join Date: Jan 2005 Posts: 16,054

jh1711 nailed it -- thanks! Also thanks to CrazyEyez for noting the need to loop in case multiple cells were changed at once.

Here is the code that works:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("D31:D38")
    
    ' check if the changed cell is in the range of concern
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
           
        For Each c In KeyCells
          
            ' if cell is blank change value of column C in same row to "None"
            If CStr(c.Text) = "" Then Range((c.Address)).Offset(, -1).Value = "None"

        Next
       
    End If
End Sub

Quote

06-17-2015 , 04:07 AM

#2342

Mr.mmmKay

Drugs are bad...

Join Date: May 2011 Posts: 20,363

Is it normal that this takes minutes on a workbook with 9 sheets? And can i make it go faster?

Code:

Option Explicit

Public Sub format()
    Dim cell As Range
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
      For Each cell In Range("E6:YE300")
        cell.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" 
        Next cell
    Next ws
End Sub

Quote

06-17-2015 , 05:02 AM

#2343

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Why are you looping through each cell?

Range.NumberFormat should work just fine and not take ages.

The way you are doing it now does take forever due to a couple of things:
- ScreenUpdating = True
- Each cell is done individually
- You loop through a gazillion cells

Quote

06-17-2015 , 05:05 AM

#2344

Mr.mmmKay

Drugs are bad...

Join Date: May 2011 Posts: 20,363

Thank you, didn't know about range.NumberFormat

Quote

06-17-2015 , 05:46 PM

#2345

Phresh

banned

Join Date: May 2006 Posts: 10,251

This one is tricking me. I'm trying to use an IFAND (I think) to reference a few things. I'm trying to notify myself when I can go to an article I posted online and link a similar article into it.

I want E column to check C column for "posted" and also if the related ID in D is posted. So E2 should be thinking, "Is ID 33 posted? If yes, check if there's a related ID and if that's also posted, put in YES."

Quote

06-17-2015 , 06:13 PM

#2346

CrazyEyez

Carpal \'Tunnel

Join Date: Sep 2003 Posts: 8,669

There's no IFAND function; you need to put the AND within the IF.

=IF(AND(C2="posted",VLOOKUP(D2,A:C,3,0)="posted"), "YES","NO)

Quote

06-17-2015 , 06:23 PM

#2347

BobJoeJim

Tournament Director

Join Date: Jan 2006 Posts: 11,200

Quote:

Originally Posted by CrazyEyez

There's no IFAND function; you need to put the AND within the IF.

=IF(AND(C2="posted",VLOOKUP(D2,A:C,3,0)="posted"), "YES","NO")

FYP (a closing quotation mark was missing from your otherwise excellent answer)

Quote

06-17-2015 , 06:35 PM

#2348

Phresh

banned

Join Date: May 2006 Posts: 10,251

Thanks to both of you! That's what I meant by IFAND. I often talk in ******ed terms to describe ****. I still need to learn Excel's order of operations for writing the formulas. Thanks again.

Quote

06-17-2015 , 10:12 PM

#2349

KB24

veteran

Join Date: Apr 2008 Posts: 3,465

I have a problem:

Say first worksheet has a set of numbers. 2nd worksheet is a subset of 1st worksheet. I need to know what in the first worksheet is NOT in 2nd worksheet. Vlookup doesn't seem to work for this. But I am an excel noob. Can someone tell me the best way to tackle this? Thanks!

Quote

06-17-2015 , 10:55 PM

#2350

lkasigh

adept

Join Date: Jun 2013 Posts: 953

Quote:

Originally Posted by KB24

Maybe MATCH or COUNTIF would help. But your question is a bit vague. What is the exact format of your data and what is the output you are trying to achieve?

Quote

Page 94 of 120

First

44 74 84 89 90 91 92 93 94 95 96 97 98 99 104 114

Last

Post Reply Subscribe

...

Page 94 of 120

First

44 74 84 89 90 91 92 93 94 95 96 97 98 99 104 114

Last