TRUE and FALSE don’t work within SUM()
I’ve had success with COUNTIFS over a range where he condition is TRUE =COUNTIF(D2:D51,TRUE)
I’ve had success with COUNTIFS over a range where he condition is TRUE =COUNTIF(D2:D51,TRUE)
If you are wanting to just copy the whole column, you can simplify the code a lot by doing something like this: Sub CopyCol() Sheets(“Sheet1”).Columns(1).Copy Sheets(“Sheet2”).Columns(2).PasteSpecial xlPasteValues End Sub Or Sub CopyCol() Sheets(“Sheet1”).Columns(“A”).Copy Sheets(“Sheet2”).Columns(“B”).PasteSpecial xlPasteValues End Sub Or if you want to keep the loop Public Sub CopyrangeA() Dim firstrowDB As Long, lastrow As Long …
Inside the VBE, Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is. This will give you access to the XML Object Library. Updated with fancy pic!
If you dont want to hard-code the cell addresses you can use the ROW() function. eg: =AVERAGE(INDIRECT(“A” & ROW()), INDIRECT(“C” & ROW())) Its probably not the best way to do it though! Using Auto-Fill and static columns like @JaiGovindani suggests would be much better.
Private Sub Workbook_Open() on error goto Oops version = “1.0” Set objHTTP = CreateObject(“WinHttp.WinHttpRequest.5.1”) URL = “<WEB SERVICE>” objHTTP.Open “POST”, URL, False objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)” objHTTP.setRequestHeader “Content-type”, “application/x-www-form-urlencoded” objHTTP.send (“version=” + version) exit sub Oops: ‘handle error here End Sub If you wanted to, for example, change the URL …
You can force an error like #DIV/0! and then use IFERROR, e.g. =IFERROR(1/(1/SUMIFS_formula),””)
If the user clicks Cancel, a zero-length string is returned. You can’t differentiate this from entering an empty string. You can however make your own custom InputBox class… EDIT to properly differentiate between empty string and cancel, according to this answer. Your example Private Sub test() Dim result As String result = InputBox(“Enter Date MM/DD/YYY”, …
If you have powershell available, just paste this into a powershell command prompt: foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator()) { $v = New-Object PSObject for ($i = 0; $i -lt $provider.FieldCount; $i++) { Add-Member -in $v NoteProperty $provider.GetName($i) $provider.GetValue($i) } $v } Credits and more advanced usage: http://dbadailystuff.com/list-all-ole-db-providers-in-powershell
Select your range from cell A (or the whole columns by first selecting column A). Make sure that the ‘lighter coloured’ cell is A1 then go to conditional formatting, new rule: Put the following formula and the choice of your formatting (notice that the ‘lighter coloured’ cell comes into play here, because it is being …
Use the WScript.Shell instead, because it has a waitOnReturn option: Dim wsh As Object Set wsh = VBA.CreateObject(“WScript.Shell”) Dim waitOnReturn As Boolean: waitOnReturn = True Dim windowStyle As Integer: windowStyle = 1 wsh.Run “C:\folder\runbat.bat”, windowStyle, waitOnReturn (Idea copied from Wait for Shell to finish, then format cells – synchronously execute a command)