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!
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)) = " " ...
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...
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...
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...
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....
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...
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...
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...
Use String Sub dural() Dim lastdaylastmonth As String lastdaylastmonth = Format(DateSerial(Year(Date), Month(Date), 0), "dd/mm/yyyy") MsgBox lastdaylastmonth End Sub ...
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...
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 ...
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,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...
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,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...
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...
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,...
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...
In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...
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...
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...
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....
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....
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,...
but you're so close! sub1 . . If x=y Then Call sub2 Exit Sub End If . . End Sub ...
If you are trying to get the blanks to display as zero the code is ActiveChart.DisplayBlanksAs = xlZero ...
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),"") ...
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,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 ...
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...
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...
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...
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)...
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"....
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 ...
try with Mid instead: Mid(enclosedValue, InStr(1, enclosedValue, "*")) ...
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 ...
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...
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...
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)...
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),...
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...
@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...
Declare your variables. Always. dim colStart as long dim colEnd as long dim wks as Worksheet It should work this way....
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 ...
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...
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)...
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...
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...
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...
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) <>...
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...
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:...
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...
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...
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...
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...
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...
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...
javascript,excel,vba,excel-vba
This should do it: newSheet.Move(null, iWorkbook.Sheets("Payable")) ...
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...
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,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,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...
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 ...
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...
excel,osx,vba,excel-vba,excel-vba-mac
The Mac does not support the SearchFormat argument. Just omit and the code will run.
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...
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...
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 <>...
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 =...
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,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)...
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...
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,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 ...
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,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...
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()...
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 =...
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...
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...
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....
It would just be With ProgressBar as opposed to With UserForms("ProgressBar") wouldn't it?
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,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.
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...
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.
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...