Posts by jvalente

    Hi All,


    I have some code that is opening a secure file in a hidden folder (this data is sensitive and all I need VBA to do is perform calculations on it).


    The code works fine, however the downloading file box from excel actually shows the files path.


    How can I remove that?


    I've the following at the start which is not solving this problem


    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False

    Hi All,


    I have several OLEDB connections in my worksheet which I'm trying to refresh via VBA.



    My code is as abovefor one table. The problem is I get an error that says something along the lines of "Unexpected Error", "Exception from HResult: 0x800A03EC"


    I'm not sure what this means.


    I then get an option to send a "Frown"


    Is there something I'm missing in my code above?

    Hi All,


    I'm new to VBA scripting dictionary and I'm close to solving this.


    Basically I have one list on one sheet, and I want to compare it to another and count how many instances of the number occur (countif).


    This is what I have so far.


    Looping in Excel is always going to be slow. With the snippet of information provided above, you should be able to speed this up by writing directly to the range you want to update eg:



    Thank you very much!


    I have learnt another way of doing things in VBA.


    This is much much faster


    Thanks again

    Hi All,


    I am creating a sheet where I'll need a couple of SUMIFs and COUNTIFs, so I'm focused on getting the fastest possible way of doing this.


    I have this which works:



    This works fine, but is fairly simplistic. So I thought I'd try an array (a bit of a novice at arrays), to make it faster.



    To my surprise, it takes the same amount of time for both. Am I doing something wrong?

    Hi All,


    Using VBA to interact with IE is a new goal for me. I'm probably at an intermediate level with VBA now. So I'm more experienced but in no way a guru.


    I'm trying to learn how to automate VBA and IE.


    My code is stuck at the following:



    In the loop I can see the element "password" but for the life of me HTMLDoc.all.Password.Value =.... keeps erroring out for me.


    Any help or good reference material to get my head around elements would be great!


    Cheers

    Hi All,


    My boss has asked me to create some code to check if some workbooks have been updated (not too hard). But he's also asked me to check if the sheets within those workbooks have been changed (this I don't know).


    The code I have written so far



    So my code to check workbooks is easily done.


    I've setup the structure for opening the workbooks, and I've setup looping between the sheets in the workbook. But I'm lost on what to do from there ('needs something here to check if sheet has been changed since last login).


    Maybe I don't even need to open the workbooks?


    I have a quick easy solution in my mind, which is to have a close and save date in each sheet from those foreign workbooks. Then my code just needs to find that date and grab it.


    But perhaps there is an easier way?


    Thanks all

    Re: VBA Scripting Dictionary Vlookup help please


    Hi All,


    I've figure it out.


    StephenR, I want to use a dictionary because I'm doing this over thousands of records and using other methods tends to be slow.


    I'll try re-iterating my problem:


    I have a list 'association table' with a list of references and then I have another list of references ('test').


    Whatever I don't find from association table in 'test' add to the bottom of test.



    The solution was to remove common identifiers between both lists and leave only unique items to both lists


    Code
    1. dict.exists(IDBroker(i, 1)) Then ' we found the item
    2. dict.Remove (IDBroker(i, 1)) ' I don't want this added to my items (I only want to capture the new data that I need to add to my other list)

    Hi All,


    I am having trouble with some code I've started writing.


    I have two lists, both with references. I basically want to vlookup from one sheet (complete list) to the other sheet (incomplete list). Whatever isn't found in the incomplete list, add from the complete list.


    Only add what's missing not everything! (What my code does - a combination of the missing and incomplete)


    What I am getting is instead of the dictionary writing out just the new references I have to add to the incomplete list, it's adding both the elements together in the dictionary (what the final result will be).


    I'll repeat it again, hard to explain I guess.


    I don't want my dictionary to return the complete list, only the new items I need to add.


    Hope that makes sense.


    Hi All,


    I have a vlookup that worked previously but today didn't get up to there because of an error.


    I used the below initially to capture and control the error:



    Err.Clear didn't seem to clear the error. My vlookup would return a 1004 error.


    Luckily I found On Error Goto -1.



    This then fixed it. I'm confused because I'm used to having Err.Clear working, but in this case it didn't.


    My vlookup does have:


    On Error Resume Next preceeding the vlookup


    Any ideas?

    Re: Sumif with Offset




    You're an absolute champion!


    Thanks very much, your first formula gave me the same issue as my formula.


    But your second formula worked a treat and is exactly what I wanted.


    Thank you

    Hi All,


    I have a formula as below. It's a dynamic range sumif


    Code
    1. =SUMIF(OFFSET($B$2,0,0,COUNTA($B$2:$B$100000)-4,1),"Actual",OFFSET(C2,0,0,COUNTA(C2:C100000)-4,1))


    Column B has four tags, "Actual, Forecast, Variance, Variance %",


    Then in each column after B, I have number and dollar values.


    I want to add them with a dynamic range as above. Interestingly the formula works for the first column (C), but when I drag across the formula to the next columns, the number doesn't update and I get the same value as Column C total.


    If I go into Column D formula:


    Code
    1. =SUMIF(OFFSET($B$2,0,0,COUNTA($B$2:$B$100000)-4,1),"Actual",OFFSET(D2,0,0,COUNTA(D2:D100000)-4,1))


    Make it an array then, un array it, the value updates and I get the value I want.


    Why doesn't it automatically update and is there a better way to do this?

    Hi All,


    Driving me crazy this..but I've got a bunch of cells in Column O called "Manually Update"


    I want the adjacent column in G to highlight a colour when "Manually Update" is found.


    So I picked formula under conditional formating and wrote after selecting all of Column G:


    =O2 = "Manually Update"


    But it seems to highlight only a few examples and not all....



    SUPER FRUSTRATING.


    Any ideas?

    Re: Date Ranges won't group in filter


    Yeah they are, which is why I tried using a text to columns method. This works when doing it manually, but after recording it and using that, it doesn't want to budge oddly enough.


    I'll give your method a go.

    Hi All,


    I have a unique problem that usually a text to columns macro fixes as per below.



    Code
    1. Columns("D:D").Select
    2. Selection.texttocolumns Destination:=Range("D1"), DataType:=xlDelimited, _
    3. TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    4. Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    5. :=Array(1, 1), TrailingMinusNumbers:=True


    My problem is as below (please refer to problem jpg). The group dates won't group with the other dates.


    I've tried everything from copy and paste special as values


    Then changing the date format to


    Code
    1. .NumberFormat = "dd/mm/yyyy"



    But nothing[ATTACH=CONFIG]69595[/ATTACH]


    This is super frustrating.