|vba for cut and paste is too slow|
You hardly ever need to use .Select for anything - you can access an
objects properties directly without selecting it first. i.e. :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And Target.Value <> "" Then
Application.EnableEvents = False '// Prevent infinite loop
Application.EnableEvents = True '// Re-enable events
|Out of stack space when using recursion in function|
If you remove the IsEmpty lines, Empty cells will be treated as 0. If
you wish to leave them blank and sort around them you will need to
impliment additional logic.
Your second loop needed to be adjusted. As it stood, the first time
The first loop would give:
1 100 8 9 9 50 100 500 1000
Then the second loop would get to 1 > 100 and exit sub.
BUT... its best not to simply remove the ex
|TransferText export to CSV not working, but TransferSpreadsheet to XLSX working|
The answer to this was that there was an error '3441' "Text file
specification field separator matches decimal separator or text
delimiter", which was only visible when trying to run it in the
Apparently, when you use a non-English OS this is a problem with the
default setting for the delimiters.
To solve it, an Export Specification has to be provided. However,
|Outlook 2013 rule with Run a Script to Update subject and forward mail|
You are looking for InStr not Contains.
Private Sub ForwardEmailIndeed_test()
' Open a mailitem first
Dim curritem As mailitem
Set curritem = ActiveInspector.currentItem
Sub ForwardEmailIndeed(Item As Outlook.mailitem)
Dim myForward As mailitem
Set myForward = It
|Deleting all Records after a specific string is found in one record in one field|
Excel doesn't have tables, it has spreadsheets. Maybe that's
nitpicking, but it's a pet peeve of mine when people refer to Excel as
As for the problem at hand, you can import the data using a Primary
Key. Stick it into a field called PKey. This will allow you to find
where the record is that you want to delete.
Then, do something like this:
Dim db as Database
Dim rec as Record
|excel inputbox vba cancel function|
You need to check if LSearchValue is empty. Use this do loop
Do While LSearchValue <> 0
LSearchValue = InputBox("Please enter a value to search for. Enter
a zero to indicate finished" & _
"entry.", "Enter Search value")
If LSearchValue = "" Then
Exit Do '<~~ Or Exit Sub if you want to terminate the sub
ElseIf LSearchValue <> 0 Then
iHowMany = iH
|Issues with Runtime Error '2185' while using combo box to search dynamically on continuous form|
Googling 'runtime error 2185' yields results about an error that
occurs when you're trying to access a control's properties without the
control having focus (would have been nice to include the error
message in your question).
It seems weird that the control wouldn't be having focus, since it's
that control raising the Change event you're handling here.
So I'd just wrap the code in an error hand
|Open excel file in outlook share group mail box|
You get to a shared mailbox like this.
"You can use the mailbox owner's display name, alias, or email address
when resolving the recipient."
Dim objOwner As Outlook.Recipient
Set objOwner = MyNameSpace.CreateRecipient("email address")
If objOwner.Resolved Then
|VBA Run-Time Error When Trying to set cell value to forumla|
Since your Function is named WorksheetFormula try replacing
cell.Formula = formula_1 with WorksheetFormula = formula_1
Public Function WorksheetFormula(ByVal target As Range,
ColumnHeader As String, formula_1 As String, columnFormula As String,
cell As Range)
columnFormula = target.Offset((14 - target.Row), 0).Value
Select Case collumnFormula
Case "% Discount"
|How to stop a sub which calls upon multiple subs|
Instead of using Exit Sub Throw a custom exception and catch it in
Dim MyMsg as Variant
MyMsg = msgbox("Do you wish to continue?", vbYesNo)
If MyMsg = 7 then 'If user clicks "No"
Throw new MyException
Catch ex as MyException
|Adding calculated field using VBA|
Looking at your code, you haven't added the calculated field to the
pivot table as a data field.
You need to add the following line of code after you've created the
.PivotFields("TOGrowth%").Orientation = xlRowField
|MS Project and VBA IFF FORMULA|
You were close. The formula you need is this:
IIf([Number2]=1,[Duration4],[Duration5]). Note that the function is
called Inline If, therefore the abbreviation is IIf, not IFf.
|Getting Numerics from end of a String in VBA|
Function onlyDigits(s As String) As String
Dim retval As String
Dim i As Integer
retval = ""
For i = Len(s) To 1 Step -1
If Mid(s, i, 1) Like "#" Then
retval = Mid(s, i, 1) & retval
onlyDigits = retval
|VBA - Add different ships on the worksheet|
This isn't exactly what you asked for but maybe this van get you
going. For this code you will need to place a simple button somewhere
and press it if you selected the cell you want to place your ship.
Dim length As Integer
Dim color As String
length = InputBox("Please give the length of your ship")
color = InputBox("Please give the color of your ship")
|Excel VBA Combo Box List|
use Year() and Date() to get the current year and then loop to add the
CurrentYear = Year(Date)
For yr = 2010 To CurrentYear
|isnumeric VBA what am i doing wrong here with this function?|
If I understand correctly, this should work:
Dim RX As Range
Set RX = Application.InputBox(prompt:=" Enter the range for X",
If WorksheetFunction.Count(RX) <> Application.CountA(RX) Then
MsgBox (" Please use only numbers")
COUNTA and COUNT are worksheet functions that you can use in VBA.
COUNTA returns the number of non-blank cells. COU
|How to fill the shape with color under a given condition|
You need to access OLE objects and their properties a bit differently
from the way you work with regular shapes on slides.
Instead of TextBox1.Text, use
|How to add not equals to in VBA|
I would not advise to anyone to operate on "Active" objects in VBA. It
is really tricky.
Try to assign all your Workbooks, Worksheets and Ranges to actual
Objects. Like for example:
Dim wb as Workbook
Set wb = ActiveWorkbook 'good to use it in button code.
Dim ws as Worksheet
Set ws = wb.Sheet(1)
Dim rng as Range
Set rng = ws.Range("A1")
You should not get an error if you make sure that Obje
|MS Access use VBA to Delete a Module from access file|
From Ozgrid.com - How to delete a code module
This code will remove a code module.
Dim vbCom As Object
MsgBox "Hi, I will delete myself "
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:= _
So you need a reference to Visual Basic For Applications Extensibility
and also you need to Trust A
|Calculate rental Costs in excel-vba|
You need to make this easier , not "smarter"
Just add more columns!
Then you have 3 simple rules (english code)
if days >= 30 then
price = Col K 'lookup for car
days = days - 30
if days > 7 then
price = price + ((days - 7) * Col J) 'lookup for car
days = days - (days - 7)
if days > 0 then
price = price + hlookup( days, C:I .....)
|Access VBA giving incorrect results when comparing two numbers|
Man... I can't believe I didn't see this at first. I tested your code
and got similar results as you. After looking at it closer I noticed
that the if statement was wrong. In short you need to use Option
Explicit and this error would have been caught. Option Explicit
ensures that all variables are declared. In not an error is thrown.
You statement contains a null
If checkDigit <> calculat
|Excel VBA format a series of months with commas and a "and" at the end|
Starting with data like this:
Run this code:
Dim wks As Worksheet
Dim rng As Range
Set wks = Worksheets("Sheet4")
Set rng = wks.Range("B3:M3")
Dim count As Integer
count = WorksheetFunction.CountA(rng)
If count < 1 Then Exit Sub
Dim result As String
For Each cell In rng
If Not IsEmpty(cell) Then
result = result & c
|Exporting Received Email daily Tally from Outlook to File using Excel VBA|
While in development remove "On Error GoTo" to more easily see the
lines with errors.
You need not focus on the current errors until you can process all the
Private Sub LoopFolders_Test()
Dim olApp As Outlook.Application
Dim objNS As Outlook.Namespace
Dim myolItems As Folder
Dim Start As Date
Dim EndTime As Date
|Run-time error 91|
The meaning of the error is:
91 - Object variable not set
It is obtuse the chain of objects involved. This is a Recorded Macro
that does the type of thing you are trying to do. If in doubt unwind a
complex statement into simplier ones.
Also always do a Msgbox Vartype(whatever):Msgbox
IsEmpty(whatever):msgbox IsNull(whatever). This allows you to check
assumptions on what something is.
|In batch file, how to get focus on a specific window (opened file)|
You can't. If you wanted to use vbscript or jscript you could do what
you want in a command prompt in an unreliable way (but it will work
Excel has it's own forms.
Put column headers in a row. Put selection in same row. Alt + D, O.
Plus you can make Excel only allow entries on some cells, like a
Right click cell, Properties, Protection, Unlock. Then Alt + T,
|VBA Adding Pivot Fields From Array|
Remove the last line of your code then click record macro and add the
fields manually. You should be able to clean up the recorded code
easily and then add it to what you already had. Hope that helps
|Outlook VBA - Msg.SaveAs "Path" issue|
Firstly I ma not sure why you have 5 If statements with the same
condition. Wjy not roll them into one?
Secondly, You are calling Move, then try to us the original message.
You cannot do that - the old item is gone. You need to use the new one
retruned by Move:
If InStr(1, Msg.Subject, Tracking) > 0 Then
set Msg = Msg.Move(olConc)
Msg.SaveAs Diretorio & "" & "Ca
|VBA record will not update with the correct information|
Your code uses lstAllGroups.ListCount to access the list box rows by
index number. However, .ListCount starts counting from 1 and the list
box row index values start from 0. So that means the equivalent of
lstAllGroups.Column(1, lstAllGroups.ListCount) refers to a row which
does not exist in the list box.
Here is an Immediate window example which hopefully clarifies the
situation. My list box
|How use the combobox keydown event without selecting the listed item|
Now having built my own sheet and worked with these ideas, ironically
Application.EnableEnable only helps in certain situations because the
Combobox_Change() event still fires with events disabled (or seems to
be the case, at least). The basic idea that I found involved
manipulating the KeyCodes and setting flags. My example below involves
using a ComboBox called TempCombo and runn
|VBA script using contains not working|
You should be using InStr.
If InStr(1, rpt.field3.Text, "99", vbTextCompare) Then
rpt.field2.visible = false
rpt.field2.visible = true
See MSDN InStr Function documentation for more information
|Sum variables in VBA|
Did you maybe mean ... ?
Select Case UCase(TimeType)
tempTime = WorksheetFunction.Sum(strSplit(2), strSplit(5),
strSplit(8), strSplit(11), strSplit(14), strSplit(17), strSplit(20),
strSplit(23), strSplit(26), strSplit(29), strSplit(32), strSplit(35),
Anyway, the problem is that you're summing also strSplit(38) which is
1721 even if you wrote in your sample which i
|Calculate in Function|
You can put an IF condition:
teg = Formuletekst
IF teg = "/" Then
TT = 500/50
TT = 500*5
For MS-Excel, you can use an empty cell (e.g A15) to put the formula
and can return the same to TT. Something like this:
teg = FormuleTekst
TT = "=" & 500 & teg & 50
ActiveSheet.Range("A15").Value = TT
TT = ActiveSheet.Range(
|Recurring Email with Voting buttons VBA|
Is Outlook running at the time your code is executed? Try to add the a
call o Namespace.Logon:
Set olApplication = WScript.CreateObject("Outlook.Application")
set olNS = olApplication.GetNamespace("MAPI")
Set olMail = olApplication.CreateItem(0)
|Automation error when getting ReadyState of InternetExplorer object|
Set ie = New InternetExplorerMedium
Set ie = New InternetExplorer
or, for late binding:
Set ie = CreateObject("InternetExplorer.Application")
This makes the error go away.
I'm not sure why you would use InternetExplorerMedium in the first
place. Quoting the small print in the documentation:
Windows Internet Explorer 8. On Windows Vista, to create an
|unable to find record in recodset|
You should do like this because i think you are not getting any
records in recordset
if not rcs.EOF then
rcs.FindFirst "ItemNo = " & itemNo 'error here, runtime error
'operation is not supported for
this type of object
If rcs.NoMatch = True Then
Can you try usi
|open folder, manipulate files in excel, save excel files in new directory|
Why don't you use File System Object.
Dim fso As Scripting.FileSystemObject
Dim sFile As Scripting.File
Dim subFldr As Scripting.Folder
Dim wbName As String
Dim fldrPath As String
Dim fname As String
Dim fldrDesc As String
Dim wbTxt As Workbook
Dim ws As Worksheet
Dim wbDesc As Workbook
fldrDesc = "C:UserYourdestinatio
|why can't i programmatically copy a locked .mdb but i can copy it through explorer?|
Microsoft explains it pretty simply in their KB article.
- Create a module and type the following lines in the Declarations
Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long
- Type the following procedure:
Sub CopyFile(SourceFile As String,
|Code is Crashing when items are not found|
Set a range variable and search for the string
Test the range if it found something
If the range is found, format with your format conditions
Dim strSearch As String
Dim rngTemp As Range
strSearch = "Implementation Record Type: ^Parent Record"
On Error Resume Next
Set rngTemp = Columns(1).Find(What:=strSearch, LookAt:=xlPart)
On Error GoTo 0
If Not rngTemp Is Nothing Then
'Your format co
|How do I check for the presence of a valid email (digital) signature attached to an Outlook MailItem (email message)?|
Outlook always represents signed/encrypted messages as regular
IPM.Note MailItem objects. It goes as far as returning a fake IMessage
MAPI interface from the MailItem.MAPIOBJECT property.
You can see this in OutlookSpy - select a signed message, click
IMessage button on the OutlookSpy ribbon. PR_MESSAGE_CLASS will be
IPM.Note. Select the PR_ENTRYID property, right click, select