How do I store a SQL statement into a variable
Dim sqlConnection1 As New SqlConnection("Your Connection String") Dim cmd As New SqlCommand Dim reader As SqlDataReader cmd.CommandText = "Select DESC1 FROM Master WHERE '" & TextBox1.Text & "' " cmd.CommandType = CommandType.Text cmd.Connection = sqlConnection1 sqlConnection1.Open() reader = cmd.ExecuteReader() ' Data is accessible through the DataReader object here. If reader.HasRows...
Doing a same action to all the subfolders in a folder
Here's a generic solution where you don't need to know the names of the subfolders. This will find all the subfolders and process the spreadsheets in each of them. You need to reference the Windows Script Host Object Model, which you do by clicking the Tools Menu, References..., and then...
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...
Using a cell's number to insert that many rows (with that row's data)
This will do what you want, it polls through from the bottom up, if it encounters a number in C and it is > 1 then it will insert the number of rows equal to column C number - 1 then copy the data from the host row. This will...
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...
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)...
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)...
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 - How to show the last date different worksheets were changed
You're very close. While the code does need to be on the sheet that is changing, it can reference the Main Menu sheet, like so: Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Main Menu").Range("A1").Value = Now() End Sub ...
Excel VBA 2013 Print Image
One option is to call Windows Print dialog via shell command. Example: (Full code) Option Explicit Private Declare Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hwnd As Long, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String,...
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...
Excel vba code using ThisWorkbook.Sheets(1).Range not working, but Sheet1.Range works fine. Why?
There is no difference between the properties of Sheets(1) and Sheet1 as long as both are the same object - Worksheet Object in your case. You're getting that error because findValue Is Nothing. That is, it couldn't find the ID in the column. When using the Find method, it's best...
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...
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...
How to duplicate values from Cell to Cell in Excel?
Non-volatile option: =INDEX(A:A,INT((ROW(A1)-1)/2)+1) (Offset will cause a recalculation of all sheets whenever any cell changes)...
VBA for duplicate rows
Try this code: . Option Explicit Public Sub showDuplicateRows() Const SHEET_NAME As String = "Sheet1" Const LAST_COL As Long = 3 ' <<<<<<<<<<<<<<<<<< Update last column Const FIRST_ROW As Long = 2 Const FIRST_COL As Long = 1 Const DUPE As String = "Duplicate" Const CASE_SENSITIVE As Byte = 1...
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...
VBA Debugger shows only 256 elements of a collection
excel-vba,debugging,collections
You could use debug.print and write the output into the immediate window and bypass the limit that way. I am almost certain that there is no way to increase that limit, but maybe someone else can give a def. answer on that....
VBA Application.OnKey Procedure error - Cannot run Macro
You need to put DeleteResources into a code Module so that it has full public scope, particular when you use late-binding of procedure names. Further, you need to remove the extra End If you have in that method....
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 ...
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.
Open Connection and Recordset objects to use SQL for sheet to sheet data movement
excel-vba,connection,adodb,recordset
Is the "strConnect" parameter intentionally commented out in objConnection.Open 'strConnect Uncomment that parameter and things will hopefully work edit: also strSQL = "SELECT * FROM [Sheet$3] & ;" is wrong. It should be strSQL = "SELECT * FROM [Sheet3$];" (the $ sign was in the wrong place and there was...
VBA code to sum totals grouped by invoice
Assuming you have headers in row 1 write this formula to D2 and copy it down until the last row of your list: =IF(COUNTIF($A$2:A2,A2)=1,SUMIFS($C:$C,$A:$A,A2),"") ...
Macro or other solution in excel to automate interaction of data in two worksheets to come up with a third worksheet
I found a solution by Spioter at Excel vba to create every possible combination of a Range which proved immensely helpful! Modified the script to fit my needs and sheet which does not reflect the Google Sheet link in the question. Thank you Spioter! Sub sub_CrossJoin() Worksheets("Combinations").Range("A4:B2000").ClearContents Dim rg_Legend As...
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...
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...
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...
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)) = " " ...
copy only one column if criteria is met (Need to adjust my existing code)
Just switch EntireRow to EntireColumn, it is as simple as that! ;) Dim rCell As Range Dim lastRow As Long, i As Long lastRow = Range("B" & Rows.Count).End(xlUp).Row i = 1 For Each rCell In Sheets(1).Range("J1:J" & lastRow) If rcell.Value = 1 Then rcell.EntireColumn.Copy Sheets(5).Cells(1, i) i = i +...
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")) ...
Mapping from One Sheet to Another Fill Unfilled chracter
excel,excel-vba,printing,header,mapping
Here's your code with the extra functionality built in. It essentially just builds an array, adds each character to the correct location then adds it all together, replacing blanks with spaces. You'll have to change the maximum (in this example it's hard-coded at 27) but you could use the same...
Excel Doc macro yields “Named argument not found (Error 448)”
excel,osx,vba,excel-vba,excel-vba-mac
The Mac does not support the SearchFormat argument. Just omit and the code will run.
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...
(VBA Excel) Extract Text and related Letter from String and output result
regex,string,excel-vba,character,number-formatting
This is how I'd do it - Sub test() Dim myFile As String myFile = "C:\reformatted.txt" Open myFile For Output As #1 Dim iPar As Integer Dim sChar As String Dim sBlank As Long Dim cont As Boolean Dim mystring As String For Each c In Range("A:A") If c <>...
Simple Enquiry with Complex Answer - How do I Select RowA6-Row(last non-blank) for a simple formula
excel,excel-vba,cell,calculated-columns,calculated-field
You need to determine the row of last non-blank cell in the column. The method for this would depend on whether there are blank cells in the middle, for instance. Two alternatives are (taken from here*): =SUMPRODUCT(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94)))) =INDEX(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))),0) Then you can use this value with OFFSET to get a reference...
Retaining original table formatting after a 'pagebreak'
excel,excel-vba,table,formatting,page-break
Just change the page margins and the top few rows get excluded, but the manual page break is still required though!
Using a stored integer as a cell reference
You have to find a suitable formula for entering in the target cell. Then you would build such formula with string concatenation, etc., for entering it via VBA. One option for the formula is to use OFFSET, as in =SUM(OFFSET($A$1,D3-1,COLUMN()-1):OFFSET($A$1,ROW()-3-1,COLUMN()-1)) This sums all values from Cell1 to Cell2, in the...
Excel VBA - Add row & make active
you need to count how many rows with the same name there are (or remember the row index of the first one), then something like this should work Sub insertRow_totals() Dim changeRow, counter As Integer counter = 2 FirstRow = 2 While Cells(counter, 1) <> "" If Cells(counter, 1) <>...
VBA Import Data
It was pretty hard to understand your code for several reasons: You just reference the sheets by index. Thus they are hard to find. Better reference them by name you reference ranges only by addresses, it would be better to define named ranges Another point to make it more readable/...
Paste pivot table as bitmap into Body of Outlook Appointment
excel-vba,bitmap,outlook,paste,appointment
There is no HTML support in appointment items and hence no no Oapt.HTMLbody Unfornunately the SendMessage API also doesn't work. So I finally (unwillingly) tested with SendKeys and it works. Note: I am not using .Save as this code is only for testing. To save the appointment, re add the...
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...
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:...
Removing duplicate rows after checking all columns
excel,vba,excel-vba,duplicates
To delete the duplicate you can just click the "Remove duplicate" button at Data Ribbon > Data Tools. Following is the demo: I have the data like this in the worksheet: I would like to have unique data at column A click the "Remove duplicate" button and following screen prompted...
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...
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...
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 =...
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...
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...
Vba changing directory to save
That's because you need to tell Excel if what you say to it should be read as a variable or as string of text. Using "" says it is string of text and should not be evaluated. Use this: Filename:="C:\Users\ee31264\Desktop\Mensile Automat\" & name_month & "\send\TESO1.xlsx" Also remember that in newer...
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....
Excel - Open a named userform
It would just be With ProgressBar as opposed to With UserForms("ProgressBar") wouldn't it?
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()...
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...
Range.offset propety
The .Offset property is used to move a certain position from a specified location. It is used like: ActiveSheet.Cells(1, 1).Offset(Row, Column) Where positive values move the position down (by the stated amount) for the Row value, and to the right (by the stated amount) for the column value. Negative values...
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)...
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)...
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....
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 ...
ReplaceLine method in VBE only replacing part of line
When I run with the debugger, myLine changes value between the two calls. The DimAll becomes Dim on the second time through. This is because you are replacing the value of codeLine once you enter the main If conditional inside the ExpandDim Function. Create a new variable in that function...
Exit Sub And Call another Sub
but you're so close! sub1 . . If x=y Then Call sub2 Exit Sub End If . . End Sub ...
How to insert data by using VBA in excel
Try this out. It has been modified slightly by first removing the contents of Not Logon and then filling one user per row that has not logged in. A counter has been added to increment the next cell to fill if a user has not logged on. A boolean variable...
pick and transfer a column in excel without VBA
Updated to match the cells in your example. Use this formula on your column B3 and drag the handle down to copy to other cells below. =OFFSET(E3,0,MATCH($B$2,$E$2:$G$2,0)-1) you should end up with the following formula in cell B3, B4, B5 =OFFSET(E3,0,MATCH($B$2,$E$2:$G$2,0)-1) =OFFSET(E4,0,MATCH($B$2,$E$2:$G$2,0)-1) =OFFSET(E5,0,MATCH($B$2,$E$2:$G$2,0)-1) Now when you change the value...
VBA dictionary data type
No problem here: Sub Tester() Dim d, k Set d = CreateObject("scripting.dictionary") d.Add 1, 1 d.Add "a", 2 d.Add #1/2/1978#, 3 d.Add CDbl(66), 4 d.Add CSng(99), 5 d.Add True, 6 For Each k In d.keys Debug.Print TypeName(k), k, d(k) Next k End Sub Output: Integer 1 1 String a 2...
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...
How to make excel chart include all blank cells from range specified in VBA?
If you are trying to get the blanks to display as zero the code is ActiveChart.DisplayBlanksAs = xlZero ...
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 ...
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 ...
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 ...
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...
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 ...
Date formatting - making dd/m/yyyy into dd/mm/yyyy
Use String Sub dural() Dim lastdaylastmonth As String lastdaylastmonth = Format(DateSerial(Year(Date), Month(Date), 0), "dd/mm/yyyy") MsgBox lastdaylastmonth End Sub ...
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...
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....
Right function vba
try with Mid instead: Mid(enclosedValue, InStr(1, enclosedValue, "*")) ...
VBA to load very large file in one go (no buffering)
Answer: Yes, using ReadAll from FSO should do the job. Best answer: Just avoid it ! My needs were: Identify a specific string within the file Extract a certain number of lines after this string As far as you precisely know the exact amout of data you want to extract,...
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 =...
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),...
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 ...
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 ...
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....
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,...
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...
Retrieve Number from a website into Excel
Here is one of the possible solutions: Option Explicit Sub RetrieveAvgPrice() Dim sUrl, sContent, sPrice sUrl = "http://www.zoopla.co.uk/home-values/london/bayswater-road/" With CreateObject("MSXML2.XMLHttp") .Open "GET", sUrl, False .Send "" sContent = .ResponseText End With With CreateObject("VBScript.RegExp") .Global = True .MultiLine = True .IgnoreCase = False .Pattern = "Avg\. asking price[\s\S]*?class=""price big"">([\s\S]*?)<" sPrice =...
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...
search compare columns sheet 1 & sheet 2 and change a cell in sheet 1
I was able to make it work by using the following modified code: Sub UpdateTBP() Dim i Dim j Dim k Dim LastRow Dim LastRow2 LastRow = Sheets("Portfolio").Cells(Rows.Count, 1).End(xlUp).Row LastRow2 = Sheets("TBP").Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To (LastRow - 1) For j = 2 To (LastRow2 - 1) If...
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...
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...
Object Variable or With Block Variable Not set in loop using find function
vba,excel-vba,loops,object,find
If the Cells.Find(What:="Run:" & i,... fails to find a match, the Select part of the statement will then cause the error. You should always store the result of a Find in a range variable and then test that for Nothing. Add this declaration to your code: Dim cellsFound As Range...
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...
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...
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))...
Excel VBA Loop Delete row does not start with something
It's because your moving forward through the rows - if you delete row 4 then row 5 becomes row 4 and the code will jump to the new row 5 - which is in fact row 6. Hope that made sense. :) The solution will be to use a For...
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...
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...
VBA Compile error What is the cause and solution?
My best guess is that there is a different in versions of Internet Explorer. When faced with version issues between different computers in VBA you can switch from using Tools>>References to Late binding. To use late binding here change your code from Set myIE = New InternetExplorer To: Set myIE...
Excel VB Listbox losing Value and Text Property
excel,excel-vba,properties,listbox
I saw that one of the listboxes was filled with a named range, I just removed the named range and filled the listbox manually, this seems to work fine and didn't give me more problems.