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....
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)...
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 “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...
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...
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"...
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...
How do I resolve the “Enter Parameter value” error in MS-Access
Try this. This will exclude the [DSRT_ERS].[ID] from DSRT_TEMP table and insert only filtered records. INSERT INTO DSRT_ERS SELECT * FROM DSRT_TEMP WHERE [DSRT_TEMP].[ID] NOT IN (Select [DSRT_ERS].[ID] FROM [DSRT_ERS] WHERE [DSRT_ERS].[ID] IS NOT NULL) There are two options, you have NOT IN and NOT EXISTS suggested by @deoeth. Keep...
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...
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.
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 ...
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 ...
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....
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...
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,...
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 =...
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...
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 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...
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...
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 ...
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...
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)) = " " ...
How do I use the Find function with a variable term, for example Run1, Run2, RunX
Yes, you need a variable, and just concatenate it. Use something like this: Dim counter as long counter = 1 Cells.Find(What:="Run:" & counter, After:=Cells(1, 1), _ ...yaddayadda Or use it in a loop: For i=1 to 100 Cells.Find(What:="Run:" & i, After:=Cells(1, 1), _ ...yaddayadda Next i ...
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...
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 +...
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....
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)...
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 ...
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,...
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 ...
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...
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...
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 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...
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 ...
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...
Format Excel worksheets using for each loop VBA
Cells.Select selects the cells of the currently active sheet. This selection doesn't change within the For Each loop You want to work on the cells of the worksheet provided by the For Each construct, i.e. Sub wsLoop() ' Declare Current as a worksheet object variable. Dim ws As Worksheet '...
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]...
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...
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...
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),...
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...
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 - .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...
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...
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 ...
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...
Taking value of a range of merged cells
Sub testnoms() Dim cell As Range For Each cell In ActiveSheet.Range("B20:K23") If cell.Address() = cell.MergeArea.Cells(1).Address() Then Debug.Print cell.Address(), cell.MergeArea.Cells(1, 1).Value End If Next End Sub If you need to capture merged area values which may not be full-contained in your defined range: Sub testnoms() Dim c As Range, d, addr...
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...
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...
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...
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")) ...
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...
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...
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)) ...
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()...
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...
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....
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...
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...
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...
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....
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...
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 ...
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...
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 ...
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...
Specifying a range to be applied to all subs in a module?
Create a special sub to perform the initialization and run it first: Dim DataSheet As Range Sub RunMeFirst() Set DataSheet = ThisWorkbook.Sheets(1).Range("A3:FU5002") End Sub ...
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....
Exit Sub And Call another Sub
but you're so close! sub1 . . If x=y Then Call sub2 Exit Sub End If . . End Sub ...
Excel VBA range after filtering xlCellTypeVisible
excel,vba,excel-vba,filter,range
Something like this should work: Dim newrange As Range, rw as range Set newrange = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible) Dim intValueToFind As Integer intValueToFind = 2 For Each rw in newrange.Rows If rw.cells(3).Value = intValueToFind Then MsgBox ("Found value on row " & rw.cells(1).Row) Exit Sub End If Next rw ...
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....
Recordset.Edit or Update sql vba statement fastest way to update?
sql,vba,ms-access,access-vba,ms-access-2003
Assuming WHERE columnc = 20 selects 1000+ rows, as you mentioned in a comment, executing that UPDATE statement should be noticeably faster than looping through a recordset and updating its rows one at a time. The latter strategy is a RBAR (Row By Agonizing Row) approach. The first strategy, executing...
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....
How do I get a cell's position within a range?
updated using variant provided by lori_m But I wonder if there are any native functions ... use this Sub test() Dim r As Range, c As Range With Sheet1 Set r = .[B2:E10] Set c = .[C2] End With If Not Intersect(r, c) Is Nothing Then Debug.Print "Column in sheet:...
Right function vba
try with Mid instead: Mid(enclosedValue, InStr(1, enclosedValue, "*")) ...
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)...
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 ...
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...
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...
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....
VBA Excel SendKeys Macro
Give this a try: Sub Refresh() Dim lastrow As Long Worksheets("Extract").Activate lastrow = Range("Q" & Rows.Count).End(xlUp).Row For j = 2 To lastrow Range("Q" & j).Select Application.SendKeys "{F2}" Application.SendKeys "{ENTER}" DoEvents Next j End Sub EDIT#1: This version will remove the "chatter:** Sub Refresh() Dim sOLD As Worksheet Set sOLD =...
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...
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...
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 ...
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.
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...
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 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...
VBA - Trapping events on dynamically created Textbox
excel,vba,excel-vba,events,userform
After some quick testing, I am able to reproduce your error if I declare TBox as TextBox. I do not get an error if I declare TBox as MSForms.TextBox. I would recommend declaring all your TextBox variables with the MSForms qualifier. Test code is situated similar to yours. I have...
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...
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...
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 " &...
PowerPoint VBA Macro Gives Run Time Error 448: Named argument not found
Replace Type:= with Orientation:=
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 ...
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)...