If cell value starts with a specific set of numbers, replace data
Use the LEFT() function, as shown below: lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If LEFT(c.Value,3) = "614" _ Or LEFT(c.Value,3) = "626" _ Or LEFT(c.Value,3) = "618" _ Or LEFT(c.Value,3) = "609" _ Or LEFT(c.Value,3) = "605" Then...
Editing a query in another DB
vba,ms-access,access-vba,ms-access-2010
Use OpenDatabase to return a DAO.Database reference to your remote database. Then you can access a saved query via its QueryDefs collection. Here is an example from the Immediate window: set db = OpenDatabase("C:\share\Access\Database1.mdb") Debug.Print db.QueryDefs("Query1").SQL SELECT dbo_foo.bar, TypeName(bar) AS TypeOfBar FROM dbo_foo; db.QueryDefs("Query1").SQL = "SELECT d.bar, TypeName(d.bar) AS TypeOfBar"...
Excel VBA (via JavaScript) - Moving a Sheet to a new location
javascript,excel,vba,excel-vba
This should do it: newSheet.Move(null, iWorkbook.Sheets("Payable")) ...
PowerPoint VBA Macro Gives Run Time Error 448: Named argument not found
Replace Type:= with Orientation:=
Referencing a new inserted column Excel VBA
You can use Indirect() For example 'AUA Summary'!$D$9 can be written as INDIRECT("'AUA Summary'!$D$9") This way even when the columns move, it will refer to the same cell. The other way is to use Index For example D9 in Excel 2007+ can be written as INDEX(1:1048576,9,4) or INDEX(INDIRECT("1:" & ROWS(A:A)),9,4)...
embed object and display as icon in word document?
Okay here is start Option Explicit Sub AddFileIcon() SendKeys "%F{TAB 4} {TAB 5}" Dialogs(wdDialogInsertObject).Show End Sub Now you Create Button :-) and call the Macro Here is help full Links VBA SendKeys Word Dialog Boxes ...
How to create a date using strings in vba?
vba,ms-access,access-vba,ms-access-2003
DateSerial should make this easier. Give it values for year, month, and day in that order, and it will give you back the corresponding date as a Date/Time value. for dt = DateSerial(Year(Date), 1, 1) to DateSerial(rs!dan, 2, 2) msgbox dt Next ...
Simple VBA Select Case statement not working, Why not?
It will enter the "too short" portion of your case statement if Lenn is a string. Try forcing it to be an integer before assigning it by adding this to the top of your code: Dim Lenn As Integer ...
VBA - .find printing wrong value
SOLUTION: needed to change the function for finding the values under the headers '(8) 'Get the Values from columns with specified headers Function GetValues(ch As Range, Optional vSplit As Variant) As Scripting.Dictionary Dim dict As Scripting.Dictionary Dim dataRange As Range Dim cell As Range Dim theValue As String Dim splitValues...
Excel VBA - ShowAllData fail - Need to know if there is a filter
If you use Worksheet.AutoFilter.ShowAllData instead of Worksheet.ShowAllData it will not throw the error when nothing is filtered. This assumes that Worksheet.AutoFilterMode = True because otherwise you will get an error about AutoFilter not being an object. Public Sub UnFilter_DB() Dim ActiveS As String, CurrScreenUpdate As Boolean CurrScreenUpdate = Application.ScreenUpdating Application.ScreenUpdating...
Best way to return data from multiple columns into one row?
Please consider this VBA script to resolve your inquiry: Sub LookupOuput() Dim OrderNumberColumn As Range Set OrderNumberColumn = Worksheets("batches").Range("B2:B1384") Dim LookUpRange As Range Set LookUpRange = Worksheets("OrderLvl").Range("C:DL") Dim cell As Range Dim FindResult As Range For Each cell In OrderNumberColumn If Not cell.Value2 = Empty Then Set FindResult = LookUpRange.Find(what:=cell.Value2)...
Errors 91 and 424 when iterating over ranges in Excel VBA
I think the error is because, as mentioned in the comments, that your "for each" isn't being used correctly. Try this: Dim cel Set nonZeroes = Range(Cells(1, 1), Cells(10, 1)) ' You need to set the range to search through here. For Each cel In nonZeroes question = isTouching(cel.Value, firstfeat)...
VBA - do not grab header in range
The problem lies in the GetValue function. When there is no value below the header, the range selection ends up selecting the empty cell plus the heading above it. You have also not properly implemented the If Len(v) = 0 Then from a previous post. You have added it in...
Loop Up rows until Value DOES NOT equal Continued
This should work... For j = 0 To SourceCell.Row - 1 If .Cells(SourceCell.Row - j, 3).Value <> "continued." Then wsFHA.Cells(RowCounter, 7).Value = .Cells(SourceCell.Row - j, 3).Value Exit For End If Next j And to add in further search terms replace the main code loop with the following code... Dim SourceCell...
iteration (for-loop) ms Access with past value
vba,for-loop,access-vba,iteration,recordset
Following our discussion in Comments, I have updated your complete code to what it should be. Again, I don't have an Access database handy to test it but it compiles and should work: Sub vardaily() Dim db As Database Dim rs As DAO.Recordset, i As Integer, strsql As String Dim...
read variable excel data into variant with the same format
excel,vba,excel-vba,variables,data-structures
You could force test to be an array with only one cell, if the last column is B or less : ' Define Last Column with a value LastCol = Sheets("Filter").Cells(20, Sheets("Filter").Columns.Count).End(xlToLeft).Column Col_Letter = Split(Cells(1, LastCol).Address(True, False), "$")(0) If LastCol <= 2 Then ReDim test(1 To 1, 1 To 1)...
Msgbox With If statement
You could declare a String, and construct the string to display the characters. Something like, Dim tmpRes As String If Len(res1) > 0 Then _ tmpRes = tmpRes & res1 & vbCrLf If Len(res2) > 0 Then _ tmpRes = tmpRes & res2 & vbCrLf If Len(res3) > 0 Then...
MS Access VBA: UBound & LBound functions returning subscript out of range error on class array
Your problem is in the line: Public Property Get ignoreDifferencesInDatabaseComparisonForFields() As String() ignoreDifferencesInDatabaseComparisonForFields = pIgnoreDifferencesInDatabaseComparisonForFields ' ^ correct typo here ^ End Property When I fixed this, your code compiled, and it ran properly without the Subscript out of range error....
Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In
excel,vba,excel-vba,user-defined-functions
You need to use Application.Caller. This will return the value in cell A1 of the sheet the function is entered to: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Cells(1, 1) End Function This will return the name of the calling sheet: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Name End...
ByRef Argument type mismatch on just changing the variable position in declaration
It is as simple as this : Dim ArrayLength As Integer, _ IDvariable As Integer, _ PreArrayLength As Integer, _ IDComparisonResult As Integer Because when you use Dim, you have to specify for each variable what type is it, the previous code was declaring the first three as Variant and...
using activecell and offset in VBA
Try: ActiveCell.Offset(1,1).select ActiveCell is already a range object, and you don't need to use Range() to turn it into range.
Compare two data sets in different sheet and print difference in other sheet using excel VBA?
Here is the code that will compare sheet1 and sheet2(corresponding cells ) and according wite the correct value or Mismatch based upon the result into sheet3. Sheet1 and sheet2 wil have same number of rows and columns and the headers be same so you can keep them as it is...
Extract text which begin with the same two characters and end in a numeric character
regex,vba,excel-vba,user-defined-functions,udf
Public Function GetCode(data As String) As String startpos = InStr(data, "WP") If startpos = 0 Then startpos = InStr(data, "MD") fisrtNumPos = 0 For i = startpos To Len(data) If fisrtNumPos = 0 And LastNumPos = 0 Then If IsNumeric(Mid(data, i, 1)) Then fisrtNumPos = i End If Else If...
Finding position of a particular string containing cell in Excel
One way: Sub qwerty() lastrow = 10 For x = 2 To lastrow If InStr(Sheets("Sheet1").Cells(x, 3), "TFMODE") > 0 Then MsgBox Sheets("Sheet1").Cells(x, 3).Address(0, 0) End If Next x End Sub ...
Using excel as inputfile in vba (scripting - non MS OFFICE)
I think this will also work for Visual Basic [for Applications]; I tried it in Excel VBA and Word VBA: In VBA Development Environment, select Tools, References. In the list that now appears, check the Object Model you need. In your case you will want to check the Microsoft Excel...
Copying certain columns VBA
This line copies the entire row: Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow You will need to change EntireRow to just copy the columns you want, probably something like: Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).Range(.Cells(1,2),.Cells(1,3)) Hope this helps, I can't check this right now....
Exit Sub And Call another Sub
but you're so close! sub1 . . If x=y Then Call sub2 Exit Sub End If . . End Sub ...
Way to capture double click to open file?
I tackled this by using 2 instances of Workbook_Open inside an excel Addin. When a file is loaded, the addin starts up, and checks to see if there are any active workbooks. If there is none, then we wait a little bit and check again, looped as many times as...
Get the VBProject of a Database
Look in the VBProjects collection and check each project's FileName property. If a project's FileName is the current database file (CurrentDb.Name), that is the one you want. Public Function ThisProject() As String Dim objVBProject As Object Dim strReturn As String For Each objVBProject In Application.VBE.VBProjects If objVBProject.FileName = CurrentDb.Name Then...
VBA to remove special characters before saving workbook
vba,excel-vba,special-characters
Please, read my comment to the question. This should help you: Dim rng As Range, c As Range Set rng = ThisWorkbook.Worksheets("Arkusz1").Range("B47:L47,B51:L148") For Each c In rng.Cells c.Value = Remove_Characters(c.Value) Next c ...
EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range
If cell.Value2 = "FOUND THE CELL" Then cell.Offset(0, 1).Value2 = "changed the next right side cell" cell.Offset(0, 2).Value2 = "changed the second right side cell" End If ...
Interface Controls for DoEvent in Excel
excel,vba,excel-vba,loops,doevents
How about changing your 'do until' loop to a 'for next' loop? Something like?... Sub rowinput() Dim lngInputStartRow As Long Dim lngInputEndRow As Long Dim row_number As Long lngInputStartRow = Range("A1").Value 'specify your input cell here lngInputEndRow = Range("A2").Value For row_number = lngInputStartRow To lngInputEndRow DoEvents Next row_number 'Then a...
Copying a Range from Excel and Pasting it into Powerpoint NOT as a metafile but as a table which you can edit in PPP
There appears to be no corresponding method in the PowerPoint object model. The only way to do this is to call the ribbon button itself: ActiveSheet.Range("d51:d57").Copy newPowerPoint.CommandBars.ExecuteMso("PasteExcelTableSourceFormatting") BTW: To find the list of ribbon buttons, search for "Office 2010 Control IDs"....
Excel VBA code for multiple vlookup
excel,vba,excel-vba,match,vlookup
In fact, you never ReDim your Result() so it is just an empty array with no actual cell (not even an empty cell), you first need to ReDim it. Here is my version, I didn't use the function Match but that should work anyway : Function Conduitt(ManHole As String) As...
Excel 2013 Add a Connector Between Arbitrary Points on Two Different Groups
You explained everything very well, and the images you uploaded helped What your code is doing seems to be correct, but the error is complaining about one of the parameters, and it could be the 2nd one: .BeginConnect ConnectedShape:=firstRect, ConnectionSite:=1 ConnectionSite: "A connection site on the shape specified by ConnectedShape....
How to fill the rest of line with space() / string()?
Your question is not very clear. This would likely help you anyway. If you have a String a, which you want to pad with blanks up to 255 characters, you would do Dim apad As String apad = a & space(255 - len(a)) ' or 'apad = a & String(255...
Run various queries based on a combo box selection
vba,ms-access,drop-down-menu,access-vba
Create a query which retrieves the QryName values from rows whose SubscriptionID matches the dropdown selection ... a query something like this: SELECT QryName FROM tbl_subcription WHERE SubscriptionID = [dropdown] ORDER BY QrySequence; Then you can open a DAO.Recordset based on that query, move through the recordset rows, and execute...
Using VLOOKUP formula or other function to compare two columns
If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....
Concatenate N number of columns between 2 specific column name in VBA Excel
@nbayly is correct, you can't concatenate an entire range like this, and even if you could you are not assigning the result to anything. Here is one way to do it using a different technique. The test data looks like this: Make sure you have either the column or header...
Copying sheet to last row of a sheet from another workbook
This is what I mentioned in my comment Note: in future, you can using for loop to go through the column index. Option Explicit Dim WB1 As Workbook Dim ws1 As Worksheet Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long Dim i As Integer...
Creating a Range in VBA
if you asking about Cells multiple range then you can use this: Sub test1() Dim nStart&, nEnd& Dim Rng As Range nStart = 5: nEnd = 9 Set Rng = Range("A" & nStart) While nStart <> nEnd Set Rng = Union(Rng, Range("A" & nStart + 1)) nStart = nStart +...
How to use each value in a range to autofilter data in another sheet?
Take a look at this (there is comments in the code to help you) : Sub MKDev1() Dim WsSrc As Worksheet, _ WsFilt As Worksheet, _ RgSrc As Range, _ RgFilt As Range, _ rCell As Range, _ ColumnToFilter As Integer, _ OutPutWs As Worksheet 'Define the name of your...
Hiding #DIV/0! Errors Using IF and COUNTIF in Excel 2010
Please consider the following formula to resolve your issue: =IFERROR((COUNTIF('Sheet 3'!B4:F4,"N"))/(COUNTIF('Sheet 3'!B4:F4,"Y")+(COUNTIF('Sheet3'!B4:F4,"N"))),0) Regards,...
Excel-VBA: create named Range in row until end of cell content
Sure you can use this snippet to find the last filled cell in a column and use that row number to set your range.name - just replace the "A" with whatever column you'd like. Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Range("A2:A" & lastrow).Name = "RangeColA" End...
Replace reference with its value in Excel VBA workbook
Re-assign a cell's value to itself in VBA to overwrite the formula/link with the actual value. If NameExists(newSheet, "DelAddress") Then With newSheet.Range("DelAddress") .Value = .Value End With End If ...
Excel VBA Program Code by Using Randomize Timer
Your second ElseIf statement can never become true. First you check if num1 is bigger or equal than 50: If num1 >= 50 Then grade = "B" Cells(1, 2).Value = grade Imagine if num1 equals 49, then the next ElseIf will get executed. This checks if num1 is smaller or...
excel search engine using vba and filters?
In order to filter for "any" column, you could combine a Find result and Filter like this: Sub DateFilter() Dim nRow As Range Dim toSearch As Range 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 Set toSearch = Range("A1:C4") 'detect row that...
Label linked to a text box value
put your code under the textbox_change event Following works fine Private Sub TextBox1_Change() If Me.TextBox1.Value < 0 Then Me.Label1.Visible = True Else Me.Label1.Visible = False End If End Sub ...
VBA how to initialize vCPath
In your original code you've got this block: ' Open the file dialog With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show ' Display paths of each file selected For lngCount = 1 To .SelectedItems.Count Next lngCount For Each strFilename In .SelectedItems MsgBox strFilename Next End With Which already does what you want....
Type Mismatch Error in DLOOKUP Function
Try this code, you need to concatenate the String values. They need to be enclosed within single quotes. I have also removed .Value as this becomes redundant. One final suggestion is that you add an Else part to the AfterUpdate event. When the value is Complete you need something, but...
How to manipulate a Word 2013 document opened in read only mode
c#,vba,ms-word,word-vba,readonly
In order to turn off ReadingMode your need to put this at the beginning of your code Globals.ThisAddIn.Application.Options.AllowReadingMode = false; You may get errors because of the direction of your loop. Your first page is page 1 once you delete this then page 2 becomes page 1 etc If you...
Match Function in Specific Column Excel VBA
excel,vba,excel-vba,match,worksheet-function
What you mean to do is better served with Range.Find. Dim rngtrg As Range, rngsrc As Range Dim ws As Worksheet Set ws = ActiveSheet Set rngsrc = ws.Range(ws.Cells(1,colnumvar),ws.Cells(1000,colnumvar)) Set rngtrg = rngsrc.Find(1,...) rowvar = rngtrg.Row ...
Userform is not unloading once command button is pressed
There are a couple problems with the code you posted. After the If ComboBox1 = "ROW" Then ... Else block of code you've got an End Sub but no End If. You definitely need to add the End If and I suspect you should remove the End Sub. You've got...
Excel Search VBA macro
I hope this can help more. This code may not work 100% but it should be good enough to guide you. Let me know if you have questions. Dim WS As Worksheet Dim Results(7, 1000000) As String ''Didn't know what is a good data type or how many possible results...
Right function vba
try with Mid instead: Mid(enclosedValue, InStr(1, enclosedValue, "*")) ...
VBA Return Carriage and Fill Code
excel,vba,excel-vba,return,carriage-return
I just added a loop at the end looking for blanks - Sub InString() Dim rColumn As Range 'Set this to the column which needs to be worked through Dim lFirstRow As Long Dim lLastRow As Long Dim lRow As Long 'Difference between first and last row Dim lLFs As...
Get access to MS Word macros from PHP
A .docm document can be more or less be considered to be a normal .zip file. You can therefore open your document with some PHP unzipping function/library, thereby gaining access to the included file word/vbaProject.bin. You then need find or create a PHP library that can modify this vbaProject.bin file....
VBA data type to store Range().Characters()
You need to set the variable and the index is 1 based not 0 Dim chars As Characters Set chars = Range("A2").Characters(1, 4) chars.Font.Color = vbRed ...
Check if excel file is open, if yes close file,if no convert csv file to excel Visual Basic [duplicate]
Try with this solution which works for current instance of Excel: On Error Resume Next Dim tmpWB As Workbook Set tmpWB = Workbooks("Acc_FR044_SAP.xls") On Error GoTo 0 If tmpWB Is Nothing Then WbOpen = False Else tmpWB .Close SaveChanges:=False End If ...
VBA Macro to convert OLE to text results in “Word has encountered a prob.” error
I Googled for "Word undo" and found the following: "be sure to save once in a while since the undo stack will be cleared" So, for example, safe the document [to a temp name] after processing every 10 objects....
INDEX MATCH VBA (return a value based on two criteria)
excel,vba,excel-vba,lookup,formulas
Imho this is a classic case of "use a database instead of Excel", especially if you want to make these kind of queries regularly. However, something like this should achieve what you want in VBA: Dim customer As String Dim region As String Dim price as Double For r =...
Count amount of differences and return column name with count
Have difference as an Array, and increment the component corresponding to the current Column. You can do something similar with matches if you want. Plus, you can abbreviate your two Ifs. Dim difference(1 To 5) As Long Dim matches(1 To 5) As Long For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange Dim col...
Delete the Range of Columns in Excel VBA
Declare your variables. Always. dim colStart as long dim colEnd as long dim wks as Worksheet It should work this way....
Removing Alert When Using DeleteFile API
There are several SHFILEOPSTRUCT.fFlags options you'll want to consider. You are asking for FOF_NOCONFIRMATION, &H10. You probably want some more, like FOF_ALLOWUNDO, FOF_SILENT, FOF_NOERRORUI, it isn't clear from the question. Check the docs.
Compare 2 sheets with different headers
Matching up data between data sets requires that you give the program some help. In this case, the help needed is which columns are related to each other. You have identified a small table of how headers are related. With this, you can do the various translations from data source...
How do you delete favorite folders in outlook using VBA
You have to use the Remove method of the NavigationFolders collection. It takes a NavigationFolder as the argument. There is no Delete method. Sub RemoveAllFavorites() Dim favGroup As NavigationGroup Dim favFldrs As NavigationFolders Set favGroup = Application.ActiveExplorer.NavigationPane.Modules.GetNavigationModule(olModuleMail).NavigationGroups.GetDefaultNavigationGroup(olFavoriteFoldersGroup) Set favFldrs = favGroup.NavigationFolders Do While favFldrs.Count > 0 favFldrs.Remove favFldrs.Item(1) Loop End...
Syntax for DAO 'Like' Select Query
You need to concatenate the string variable to the LIKE clause, Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSQL As String Set db = CurrentDb Dim strRes As String strRes = Me.comboRes Set qdf = db.QueryDefs("qryInst") strSQL = "SELECT tblInst.*, tblInst.ResList " & _ "FROM tblInst " &...
Extract All Named Ranges Into A Class
Get Named Range by String Why not a simple procedure like so: Function GetNR(namedRange as String) as Range Set GetNR = ActiveWorkbook.Names(namedRange).RefersToRange End Function Then simply get the named range like so: Sub Example() Debug.Print GetNR("NAME").Value End Sub Named Range Suggestion in VBA Project Alternatively if you want the names...
VBA - Unable to pass value from Private to Public Sub
In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...
Slow VBA macro writing in cells
excel,vba,excel-vba,ms-project
Would it be possible, to write in a whole row, instead of the single cells? Would that be faster? Yes and yes. This is exactly where you can improve performance. Reading/writing to cells is notoriously slow. It matters very little how many cells you are reading/writing, but rather how...
Excel 2010 VBA Can't get Sub Menus to display
vba,excel-vba,excel-2010,submenu
I will show you a very simple example. Please amend it to suit your needs :) Private Sub Sample() Dim cb As CommandBar Dim cbc As CommandBarControl Dim newitem As CommandBarControl Dim newSubItem As CommandBarControl Set cb = Application.CommandBars(1) '~~> Delete Existing command bar control On Error Resume Next cb.Controls("Menu...
Formatting specific part of text string in VBA
excel,vba,excel-vba,outlook,format
HTML tags do work. I don't know why you say they don't. sBody = "All,<br /><br />Please Approve attached request for " & rType & ".<br /><br /><strong>Customer:</strong> " & customer & "<br />" then instead of the .Body property, use .HTMLBody .HTMLBody = sBody ...
Changing the active cell
excel,vba,excel-vba,excel-2007
The comments made by @Paradox and @Tim are all valid. To specifically answer your question, you cannot change the ActiveCell from code but instead use the Range or Cells to set a reference to the range: Public Sub PriceSearch(SaSh As Worksheet) Dim StartNumber As Integer Dim EndNumber As Integer Dim...
ADODB Connection is empty, Unable to read CSV file
sql,vba,connection-string,adodb,recordset
The connection string is blank because a wrong parameter is being sent to GetData. Take a look at these code snips: SourceSheet is the second parameter in the call, but you pass it as the first parameter here: ' Here's the call to GetData GetData("export.csv" ,"A1:BE", ... Filename goes in...
Find column with unique values move to the first column and sort worksheets
To find and move the "ID" column like 1th column Sub movecolumn() Dim sht As Worksheet Dim keySrc As String Dim lastcol As Long, cutCol As Long Dim arrcol As Variant Set sht = ThisWorkbook.Worksheets("Sheet1") keySrc = "ID" lastcol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column 'find the last Headers columns arrcol = Range(Cells(1,...
Converting ADODB Loop into DAO
DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once. Sub test() Dim vaIds As Variant Dim sSql As String vaIds = Split("1 2 4 7 200 205 654", Space(1)) sSql = "SELECT [Sales]...
VBA - printing empty cells
Fix: Had it print empty " " down the range equal to whatever is occupied in column 3. Section that fixed the code: (4) ... Else 'if no items are under the HOLDER header StartSht.Range(StartSht.Cells(i, 2), StartSht.Cells(GetLastRowInColumn(StartSht, "C"), 1)) = " " ...
I need help setting the RecordSource of a Report within a VBA Function
You need to shuffle the order: Function PrintMod() Dim Source As String Select Case Forms![Search Form]!Subform1.SourceObject Case "Query.SearchQuery" Source = "SELECT * FROM SearchQuery" Case "Query.Part Number Query" Source = "SELECT * FROM [Part Number Query]" Case "Query.Keyword Query" Source = "SELECT * FROM [Keyword Query]" Case "Query.ROP Query" Source...
Outlook VBA - set sentonbehalf and Refresh ActiveInspector
To make the From label show the right value, you need to set the PR_SENT_REPRESENTING_EMAIL_ADDRESS property (DASL name http://schemas.microsoft.com/mapi/proptag/0x0065001F) using MailItem.PropertyAccessor.SetProperty. UPDATE: As a test, you can try to run the following script from OutlookSpy - create a new message in Outlook, click "Script" button on the OutlookSpy Ribbon in...
Activecell not in Array
You have fallen victim to the odd behavior of WorksheetFunction.Match when it cannot find a match. Instead of returning the error, it throws a run time error which gums up the works. Since the premise of this question is searching for whether or not something is in a list, you...
VBA crashes when instatiating 'Match' object
FoundItems.Item(i) is zero-based. This means the first item is i = 0, second is i = 1 etc. The problem in your code comes when FoundItems.Count = 1. You will have to use Set FoundItem = FoundItems.Item(0) to return the first item. More in this tutorial....
MACRO that organises, groups, outlines (Need to adjust the area that the macro applies to)
I added a new variable StartFrom so that you'll only have to change the value once to make it work on a different range. Also, I changed the definition of lastRow, take a look at Error in finding last used cell in VBA Give this a try : Sub formatresults()...
adding variables into another variable vba
I can't see anything wrong with the code, as long as your text is in column C, and the values are in column H I've also taken the liberty of rewriting the code to make it clearer: Sub test() Dim x As Long Dim y As Long Dim TotalValue As...
How do I do to count rows in a sheets with filters? With a suppress lines
Try the following which uses the SpecialCells() method to select only cells that are currently visible on screen (i.e. not filtered out). count = Application.WorksheetFunction.CountA(Range("A:A").SpecialCells(xlCellTypeVisible)) ...
Using date in CreateQueryDef
Dates in Access needs to be surrounded by the # tags so that it recognizes the date you have passed. The other important factor to consider is that JET requires the date format to be mm/dd/yyyy as opposed to the normal dd/mm/yyyy. So your problem is because you are using...
Sort multiple columns of Excel in VBA given the top-left and lowest-right cell
I got the solution after going through many tutorials and hence posting here for reference of any one who needs help. Sub testSort() Dim CurrentSheet As Worksheet Set CurrentSheet = ActiveSheet lastRows = CurrentSheet.Cells(Rows.Count, 1).End(xlUp).Row lastCols = CurrentSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set sortA = CurrentSheet.Range(Cells(2, 1), Cells(lastRows, lastCols)) CurrentSheet.Sort.SortFields.Clear CurrentSheet.Sort.SortFields.Add Key:=Range(Cells(2, 2),...
Range, Select, Change contents, Allignment or Offset?
This can be simplified pretty easily to affect the cells from 2 to the current row, six columns to the right- Sub tester2() Dim col As Integer col = ActiveCell.Column + 6 Dim row As Integer row = ActiveCell.row Dim rng As Range Set rng = Range(Cells(2, col), Cells(row, col))...
VBA “Compile Error: Statement invalid outside Type Block”
excel,vba,excel-vba,excel-2010
You have: Dim RangeNOut as Double Dim RangeNOut as Integer While the IF statements in there are a nice idea, VBA will not allow you to do that. It doesn't do conditional 'compilation' since it isn't a compiled language. When VBA runs your code, all your variables are declared (no...
GetWindowsDirectory() API returns wrong (vba\vb6)
The behavior you are observing is documented. See the Remarks section for GetWindowsDirectory: Terminal Services: If the application is running in a Terminal Services environment, each user has a private Windows directory. There is also a shared Windows directory for the system. If the application is Terminal-Services-aware (has the IMAGE_DLLCHARACTERISTICS_TERMINAL_SERVER_AWARE...