Posts by jvalente

jvalente · Jul 5th 2018

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

jvalente · Jan 29th 2018

Hi All,

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

Code

Dim Sh As Worksheet
Dim WhatTable As String
Dim SQLTable As OLEDBConnection


Set Sh = ThisWorkbook.Sheets("Leads Last 24 Months")
Sh.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False


ProgressBar.Caption = "Progress - " & "Refreshing Leads Table"


DoEvents


Set Sh = ThisWorkbook.Sheets("Events&Tasks Last 12 Months")
Sh.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

Display More

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?

jvalente · Jan 25th 2018

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.

Code

Sub DictionaryCountifs()


Dim i As Long
Dim Lastrow As Long
Dim Lastrow2 As Long
Dim TheArray() As Variant
Dim ERPIDnDPC As String
Dim Dict As Object
Dim Sh As Worksheet
Dim Sh2 As Worksheet
Dim Key As Variant
Dim SumIt As Long


Set Dict = CreateObject("Scripting.Dictionary")
Set Sh = ThisWorkbook.Sheets("Sales Calculation")


'Countif


Sh.Activate ' sheet must be activated


ReDim TheArray(i) 'clear TheArray


Lastrow = Sh.Range("A" & Rows.Count).End(xlUp).Row
    
TheArray = Sh.Range("A2:A" & Lastrow).Value 'what we are loading


'Check what we loaded in a loop
'For i = 1 To UBound(TheArray) ' loop between to the upper bound of the array
'
'    Debug.Print TheArray(i, 1) 'check items loaded in the array
'
'Next i


Dict.RemoveAll


'Load Values into Dictionary


For i = 1 To UBound(TheArray)
    
    'item                       'key
    
    Dict.Item(TheArray(i, 1)) = TheArray(i, 1)


Next i


' Loop through the dictionary


For Each Key In Dict


    Debug.Print Key, Dict(Key)


Next Key


' Compare to Another List


Lastrow = Sh.Range("Q" & Rows.Count).End(xlUp).Row
 
TheArray = Sh.Range("Q1:Q" & Lastrow).Value 'what we are loading


' Loop to check


For i = 1 To UBound(TheArray)


    Debug.Print TheArray(i, 1)
        If Dict.Exists(TheArray(i, 1)) Then 'if they key exists then
            
            MsgBox ("Yes")
            
            Dict.Item(TheArray(i, 1)) = SumIt + 1
        
        End If


Next i


' Loop through the dictionary


For Each Key In Dict


    Debug.Print Key, Dict(Key)


Next Key

Display More

jvalente · Jan 17th 2018

Quote from gijsmo

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:

Code

Dim rRange As Range
Dim AC As Long '< dummy variable to represent column AC


AC = 29


With Application
.ScreenUpdating = False 'speed up processing by turning off screen updating
.DisplayAlerts = False
End With


'set up a range to have formulas applied
With Sh
Set rRange = .Range(.Cells(30, AC), .Cells(Lastrow, AC))
End With
'apply the formula to the range
rRange.Formula = "=COUNTIFS('Opps CDMapp'!C[-17],'2. Potential Analysis'!RC[514],'Opps CDMapp'!C[-24],""<>Get Revenue"",'Opps CDMapp'!C[-24],""<>Closed Lost"",'Opps CDMapp'!C[-24],""<>Cancelled"",'Opps CDMapp'!C[-26],'2. Potential Analysis'!RC[-25])"
'write back just the value to the range
rRange.Value = rRange.Value


With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

Display More

Thank you very much!

I have learnt another way of doing things in VBA.

This is much much faster

Thanks again

jvalente · Jan 16th 2018

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:

Code

' Formula for Open Opps (non array)


For k = 30 To Lastrow


    Sh.Range("AC" & k).Value = "=COUNTIFS('Opps CDMapp'!C[-17],'2. Potential Analysis'!RC[514],'Opps CDMapp'!C[-24],""<>Get Revenue"",'Opps CDMapp'!C[-24],""<>Closed Lost"",'Opps CDMapp'!C[-24],""<>Cancelled"",'Opps CDMapp'!C[-26],'2. Potential Analysis'!RC[-25])"


 Sh.Range("AC" & k).Value = Sh.Range("AC" & k).Value 'hardcode


Next k

Display More

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.

Code

' Formula for Open Opps (array)


  Lastrow = Sh.Range("TV" & Rows.Count).End(xlUp).Row


  ReDim TheData(Lastrow) ' clear the array and set size


  TheData() = Sh.Range("TW30").CurrentRegion.Value 'the values you're searching against


  Set MyRange1 = Sh2.Range("L:L") 'values in table to search for and return
  Set MyRange2 = Sh2.Range("E:E") 'values in table to search for and return
  Set MyRange3 = Sh2.Range("C:C") 'values in table to search for and return


  k = 30


For i = LBound(TheData) To UBound(TheData)


    Sh.Range("AC" & k).Value = WorksheetFunction.CountIfs(MyRange1, TheData(i, 2), MyRange2, "<>Get Revenue", MyRange2, _
    "<>Closed Lost", MyRange2, "<>Cancelled", MyRange3, TheData(i, 3))


    k = k + 1


Next i

Display More

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

jvalente · Aug 22nd 2017

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:

Code

Sub GMAILNEW()




Dim MyHTML_Element As IHTMLElement
Dim MyURL As String


'On Error GoTo Err_Clear


MyURL = "https://www.gmail.com"
Set MyBrowser = New InternetExplorer
MyBrowser.Silent = True
MyBrowser.navigate MyURL
MyBrowser.Visible = True


' Loop until Internet Explorer is ready


Do


Loop Until MyBrowser.readyState = READYSTATE_COMPLETE


' Set HTMLDOC as my Browser


Set HTMLDoc = MyBrowser.document


For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
 
    Debug.Print oHTML_Element.Name


Next


' Input User Name


HTMLDoc.all.identifier.Value = "[email protected]" 'Enter your email id here
HTMLDoc.all.identifierNext.Click ' click the next button after the email is entered


' Loop until Internet Explorer is ready


Do


Loop Until MyBrowser.readyState = READYSTATE_COMPLETE


' Set HTMLDOC as my Browser


Set HTMLDoc = MyBrowser.document


For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
 
    Debug.Print oHTML_Element.Name


Next


HTMLDoc.all.identifier.Value = "[email protected]" 'Enter your email id here
HTMLDoc.all.Password.Value = "" 'stuck here......

Display More

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

jvalente · May 25th 2017

Re: Check if Other Workbooks have been changed and SHEETS within

Quote from Carim;792897

Hello,

Why don't you turn on the history to Track Changes ...?

see https://www.gcflearnfree.org/e…k-changes-and-comments/1/

Hope this will help

Display More

My workbooks won't be shared and aren't the same workbook.

jvalente · May 25th 2017

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

Code

Sub MonitorWorkbooks()


' Set our Objects


Set Wb1 = ThisWorkbook
Set Sh = Wb1.Sheets(1)


Application.DisplayAlerts = False ' turn off can't find file alert
Application.ScreenUpdating = False ' turn off screenupdating


' Saved or not Loop


Lastrow = Sh.Range("A" & Rows.Count).End(xlUp).Row


On Error Resume Next


'Paste Formula Down


Sh.Range("C2:C" & Lastrow).Formula = "=""\""&A2&""\""& B2&""\""&B2&"".xlsx""" ' past down formula to find location of file


For k = 2 To Lastrow    'loop to store the name of where the file is located


    NamesLocation(k) = Sh.Range("C" & k).Value
    
    SavedLocation = "N:\Salesforce Guidance & SPC\" & NamesLocation(k)
    
    LastSaved = FileDateTime(SavedLocation)
    
    Sh.Range("D" & k).Value = LastSaved
    
      ' Check to see if it has changed
    
    Set Wb2 = Workbooks.Open(Filename:=SavedLocation) ' open the workbook
    
        For Each Sheets In Wb2.Sheets ' loop between sheets to find if they have been changed
        
            'needs something here to check if sheet has been changed since last login
            
            Debug.Print Sheets.Name
        
        Next Sheets
        
    Wb2.Close savechanges:=False ' close the workbook
        
        If Err.Number <> 0 Then
         
            Sh.Range("D" & k).Value = "File Not Present"
        
        End If
    
Next k


On Error GoTo 0 ' reset error catching


'k = 1 / 0 ' cause an error


Application.DisplayAlerts = True ' turn off can't find file alert
Application.ScreenUpdating = True ' turn off screenupdating


End Sub

Display More

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

jvalente · Feb 6th 2017

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.

Code

Sub GetMissingCBSSIDs()


Dim i As Long
Dim AppsAmt As Double
Dim CBSSID As String
Dim Sh As Worksheet
Dim Sh2 As Worksheet
Dim RGMatches As Range
Dim Lastrow As Long
Dim IDBroker As Variant
Dim NewIDBroker As Variant


' Create dictonary


Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
    
' Get worksheet


Set Sh = ThisWorkbook.Worksheets("Association Table")
Set Sh2 = ThisWorkbook.Sheets("Test")


' Vlookup Using Scripting Dictionary


Lastrow = Sh.Range("A" & Rows.Count).End(xlUp).Row


dict.RemoveAll ' clear the dictionary
                                                    
' Looping to store values in dictionary


NewIDBroker = Sh.Range("a1").CurrentRegion.Value 'load our values from Test
        
For i = 2 To Lastrow


    dict.Add key:=NewIDBroker(i, 1), Item:=NewIDBroker(i, 1)


Next i


'Looping to compare


IDBroker = Sh2.Range("a1").CurrentRegion.Value


Lastrow = Sh2.Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To Lastrow


    If dict.exists(IDBroker(i, 1)) Then ' we found the item
    
        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)
  
        Else:
              
              IDBroker(i, 1) = dict.Item(IDBroker(i, 1)) ' not found add this value to my items
            
    End If


Next i


'Dim key As Variant
'For Each key In dict.keys
'    Debug.Print key, dict(key)
'Next key


'Print the values to sheet


Dim lrow As Long, k As Variant


lrow = Sh2.Range("A" & Rows.Count).End(xlUp).Row + 1


For Each IDBroker In dict.keys
       Sh2.Range("A" & lrow) = dict(IDBroker) ' add to bottom of original list
        lrow = lrow + 1
Next
    
End Sub

Display More

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

Code

dict.exists(IDBroker(i, 1)) Then ' we found the item
    
        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)

jvalente · Feb 3rd 2017

Re: VBA Scripting Dictionary Vlookup help please

Thanks for your comments. I'm still learning this type of code. I'll get back to reading online material.
Have a good day

jvalente · Feb 3rd 2017

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.

Code

Sub GetMissingCBSSIDs()


Dim i As Long
Dim AppsAmt As Double
Dim CBSSID As String
Dim Sh As Worksheet
Dim Sh2 As Worksheet
Dim RGMatches As Range
Dim Lastrow As Long
Dim IDBroker As Variant
Dim NewIDBroker As Variant


' Create dictonary


Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
    
' Get worksheet


Set Sh = ThisWorkbook.Worksheets("Association Table")
Set Sh2 = ThisWorkbook.Sheets("Test")


' Vlookup Using Scripting Dictionary


Lastrow = Sh.Range("A" & Rows.Count).End(xlUp).Row


dict.RemoveAll ' clear the dictionary
                                                    
' Looping to store values in dictionary


NewIDBroker = Sh.Range("a1").CurrentRegion.Value 'load our values from Test
        
For i = 2 To Lastrow
    
    dict.Item(NewIDBroker(i, 1)) = NewIDBroker(i, 1)


Next i


'Looping to compare


IDBroker = Sh2.Range("a1").CurrentRegion.Value


Lastrow = Sh2.Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To Lastrow


    If dict.exists(IDBroker(i, 1)) Then ' we found the item
    
              IDBroker(i, 1) = "not new"
  
        Else:
              IDBroker(i, 1) = dict.Item(IDBroker(i, 1)) ' not found
   
    End If


Next i


'Dim key As Variant
'For Each key In dict.keys
'    Debug.Print key
'Next key


'Print the values to sheet


Dim lrow As Long, k As Variant


lrow = 2
For Each IDBroker In dict.keys
        Sheets("test").Cells(lrow, 4) = IDBroker
        Sheets("test").Cells(lrow, 5) = dict(IDBroker)
        lrow = lrow + 1
Next
    
End Sub

Display More

jvalente · Oct 3rd 2016

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:

Code

'Code above


On Error GoTo ErrCatcher:


ErrCatcher:


Runmacro = MsgBox("No DATA present for " & MonthName(themonth(i)) & " " & theyear(i) & "." & vbNewLine & vbNewLine & "Do you wish to continue?", vbYesNo)


If Runmacro = vbYes Then


    Err.Clear ' clear the error message
    
Else: Exit Sub


End If


'Code for vlookup


On Error Resume Next

Display More

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

Luckily I found On Error Goto -1.

Code

ErrCatcher:


Runmacro = MsgBox("No DATA present for " & MonthName(themonth(i)) & " " & theyear(i) & "." & vbNewLine & vbNewLine & "Do you wish to continue?", vbYesNo)


If Runmacro = vbYes Then


    Err.Clear ' clear the error message
    
    On Error GoTo -1 ' reset error trapping and allow it to continue below (must be used for the vlookup below to work)


Else: Exit Sub


End If


'Code for vlookup


On Error Resume Next

Display More

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?

jvalente · Sep 14th 2016

Re: Sumif with Offset

Quote from NBVC;777376

Did you check that may possibly have automatic calculation set off?

Go to Formulas tab, then click Calculation Options and make sure Automatic is checked.

Another version of the formula that should be less volatile:

=SUMIF($B$2:INDEX($B$2:$B$100000,COUNTA($B$2:$B$100000)-4),"Actual",D2:INDEX(D2:D100000,COUNTA(D2:D100000)-4))

although, if your D range happens to be different than the B range, you could get errors... perhaps reference column B in both ranges for size of range.

e.g.

=SUMIF($B$2:INDEX($B$2:$B$100000,COUNTA($B$2:$B$100000)-4),"Actual",D2:INDEX(D2:D100000,COUNTA($B$2:$B$100000)-4))

Display More

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

jvalente · Sep 14th 2016

Hi All,

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

Code

=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

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

jvalente · Aug 31st 2016

Re: Adjacent Column Conditional Formatting not working on every cell!

OMG,

So simple! It works now

Thanks for that cheers

jvalente · Aug 31st 2016

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?

jvalente · Jul 5th 2016

Re: Date Ranges won't group in filter

The multiply by 1 worked a treat.

Thanks!

jvalente · Jul 5th 2016

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.

jvalente · Jul 4th 2016

Hi All,

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

Code

Columns("D:D").Select
    Selection.texttocolumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=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

.NumberFormat = "dd/mm/yyyy"

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

This is super frustrating.

jvalente · Jun 17th 2016

Re: Vlookup between different workbooks with Error Handling

Thank you so much! It worked!

Does that mean if I don't reset the error object (with Err.clear) it will just return:

Code

sh.Range("U" & row).Value = sh.Range("A" & row).Value

Posts by jvalente

How to hide "downloading file" box from VBA

Refreshing multiple OLEDBConnections in Excel Worksheet

Countif using VBA Scripting Dictionary

Countif using Loop vs Array

Countif using Loop vs Array

Login to GMAIL using VBA

Check if Other Workbooks have been changed and SHEETS within

Check if Other Workbooks have been changed and SHEETS within

VBA Scripting Dictionary Vlookup help please

VBA Scripting Dictionary Vlookup help please

VBA Scripting Dictionary Vlookup help please

Error Handling Confused

Sumif with Offset

Sumif with Offset

Adjacent Column Conditional Formatting error

Adjacent Column Conditional Formatting error

Date Ranges won't group in filter

Date Ranges won't group in filter

Date Ranges won't group in filter

Vlookup between different workbooks with Error Handling