Menu
  • HOME
  • TAGS

If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba

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

vba,powerpoint,powerpoint-vba

Replace Type:= with Orientation:=

Referencing a new inserted column Excel VBA

excel,vba,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?

vba,ms-word,word-vba

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?

vba,switch-statement,case

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

excel,vba,excel-vba

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

excel,vba,excel-vba,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?

excel,vba,excel-vba

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

excel,vba,excel-vba,range

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

excel,vba,excel-vba

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

excel,vba

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

vba,excel-vba,excel-2003

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

vba,access-vba

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

vba,byref

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

excel,vba,excel-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?

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

excel,vba

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)

vba,scripting

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

excel,vba,excel-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

vba,excel-vba,call

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?

excel,vba,excel-vba,add-in

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

vba,ms-access,access-vba,vbe

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

excel,vba,excel-vba

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

vba,excel-vba,powerpoint-vba

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

excel,vba,excel-vba

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()?

excel,vba,ms-word,word-vba

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

mysql,excel,vba,date

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

excel,vba,excel-vba

@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

excel,vba,excel-vba

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

vba,range

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?

excel,vba,excel-vba

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

excel,vba,if-statement

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

vba,excel-vba,range

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

vba,excel-vba

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

vba

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?

excel,vba

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

excel,vba,textbox,label

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

vba,excel-vba,excel-2010

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

excel,vba

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

vba,excel-vba

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

excel,vba,excel-vba

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

vba,excel-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

php,vba,ms-word,word-vba

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()

vba

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]

excel,vba,excel-vba

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

vba,popup,word-vba

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

excel,vba,excel-vba

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

excel,vba,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

vb.net,vba,api,delete

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

excel,vba,excel-vba,compare

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

vba,outlook-2007

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

sql,vba,like,dao

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

vba,excel-vba

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

excel,vba,excel-vba

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

excel,vba,excel-vba,sorting

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

excel,vba,ms-access,ado,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

excel,vba,excel-vba

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

vba,ms-access,access-vba

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

vba,email,outlook,outlook-vba

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

vba

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

vba

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....

Verify If New Record Access 2010

vba,ms-access

Use the Before- or AfterInsert event of the form.

MACRO that organises, groups, outlines (Need to adjust the area that the macro applies to)

excel,vba,excel-vba

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

excel,vba,excel-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

excel,vba,filter

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

vba,date,ms-access

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

excel,vba,excel-vba,sorting

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?

excel,vba,excel-vba

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)

vba,winapi,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...