excel vba range offset

Problem: As shown, group by Lon,lat, then transpose.Sub admin () Dim conn, xRs, xFd Set conn = CreateObject ("ADODB". Connection ") Conn. Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ "Extended properties= ' Excel 8.0;hdr=yes;imex=1 ';" _ " Data source= " thisworkbook.fullname Set xRs = CreateObject (" ADODB. RecordSet ") sSQL =" Transform Sum ([tas_t]) Select [lon], [lat] from [sheet1$a:d] Group by [LON], [

The following class excellibrary is a very common class that uses VBA to manipulate excel. It is my summary, arrangement, and reference in my work (I have used some examples from Microsoft) I have made many improvements and usage, and I will share them with you here.At first, the program had no comments. For ease of understanding, I just added some comments. 'Manipulate the

Download source file: http://lwl0606.cmszs.com/archives/excel-vba-string-function.html To merge strings in Excel, use the function = CONCATENATE (A4, B4, C4) Of course, it can also be A4 B4 C4 The following function can merge strings by group. The first parameter is the grouping column, the second is the grouping content, according to the grouping, the third pa

Mycolumn=target.column Gets the column number of the active cell Myrow=target.row gets the line number of the active cell The name of the worksheet. UsedRange.Rows.Count Specify the total number of rows used by the worksheet The name of the worksheet. UsedRange.Columns.Count Specify the total number of columns used by the worksheet The name of the worksheet. Cells (line number, column number). Value gets the values of a cell on the specified worksheet Worksheets (m). Cells (i, J). Value use

Write the Excel VBA tool to connect and manipulate the MySQL database.System environment:Os:win7 64-bit English versionOffice 2010 32-bit English version1, VBA before the preparation of the connection to MySQLTools--->references. ----> ReferencesTick Microsoft ActiveX Data Objects 2.8 Librarys and Microsoft ActiveX Data Objects Recordset 2.8 Librarys2. Install My

The Excel worksheet has two numbers in A1 cells and B1 cells. These two numbers are a bit the same, now you want to find the same number and write to cell C1, find the numbers in the A1 that are not in the B1 and write to the cell D1, find the numbers in B1 that are not in A1 and write to cell E1. As in the following worksheet picture: I don't know whether the numbers given are the same rule, that is, the number of the first raw data is

1 Option Explicit2 OptionBase13 4 SubFillsheet ()5 DimI as Long6 DimJ as Long7 DimCol as Long8 DimRow as Long9 DimArr () as LongTenrow = Application.inputbox (prompt:="Input row:", type:=2) OneCol = Application.inputbox (prompt:="input column:", type:=2) A ReDimarr (Row, col) - fori =1 toRow - forj =1 toCol theArr (i, j) = (I * j)Mod - - Next - Next - + 'need to select -Worksheets ("Sheet3"). Activate + DimRng asVariant A SetR

Today, I wrote an Excel macro. When I create an Excel file based on a template, the content of some cells is automatically filled according to the current machine environment. The writing is clumsy, because you are not familiar with VBA and office events, and you are all looking at help. If you can give me some advice, please leave a message.Private sub workbook

Yesterday afternoon began to learn the VBA, exhausted, there must be bugs, to be debuggedThe VBA program is as follows:1 DimAsum as Integer2 DimTSum as Integer3 DimJudge -) as Integer4 DimArrmax as Integer5 Dimarr6 DimLocation -) as Integer7 8 FunctionTest ()9 DimArrwmax as IntegerTen DimRng asRange One DimBeginrow as Integer A DimBeginline as Integer - - SetRng = Application.inputb

Csng Single Cstr String Cvar Variant Instance:Sub type conversion ()Dim funds as DoubleFunds = 80.42454Msgbox "Type:" TypeName (Funds) "value:" Funds ' TypeName () for identifying data typesMsgbox "Type:" TypeName (CBool (funds)) "Value:" CBool (Funds)Msgbox "Type:" TypeName (CByte (funds)) "Value:" cbyte (funds)End Sub2.3 VariablesInstance:Sub Display user name ()user_name = Application.inputbox ("Please ente

#%% Creating a chart Chart = workbook. Charts.add () Chart. ChartType = Xlline Chart. SetSourceData (Sheet1. Range (Rangename)) # Traverse all Series SeriesCollection = chart. SeriesCollection () Seriescollection.count For I in SeriesCollection: Print (i) # Reference Http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html #%% Chart Type description ChartType = {' xlline ': ' Line chart ', ' xllinemarke

The Excel workbook you manage has been modified by many people, many of which are often inconsistent, providing VBA code to implement one-click layout of tabular data format, eliminating the manual frequent adjustment of Excel data format.1 Option Explicit2 OptionBase13 4 SubAutoSet ()5 DimI as Integer6 7 fori =1 toSheets.count8 Sheets (i). Activate

-looking, but fortunately realized,,,, good luck.Figure three Adding controlsFigure Four Control propertiesSummaryBoss is want to let me realize, but, Minister incompetent Ah ~ The first day are looking at two level linkage menu, found that do not need VBA Ah, degree Niang said data validation can be realized, the next day reaction came over, need is multi-box, during debugging code when a face confused force, said I control undefined, later, the boss

There's a friend. The A1 cells and B1 cells in the Excel worksheet have two digits, and the two numbers are the same, now you want to find the same number and write to cell C1, find the numbers in the A1 that are not in the B1 and write to cell D1. Find the numbers in the B1 that are not in the A1 and write to cell E1. As in the following worksheet picture: I don't know whether the numbers given are the same rule, that is, the number of

In Excel VBA, variables can be divided into two categories: value type, such as integer and double. This type of variable assignment operation directly uses the equal sign. For example: Dim A as integer Dim B as integer A = 12 B = a + 2 Another variable type is the object type, such as range and worksheet. This type of variable stores a pointer pointing to t

' Check public Function check () as Variant on Error GoTo erlb dim strdir as String st Rdir = Thisworkbook.worksheets ("Path"). Range ("B2") objfile = Dir (Strdir "\*.xlsx") thisworkbook.worksheets ("Result"). Activate with ActiveSheet . Range ("a2:b400"). Select '. Cells.select selection.clearcontents . Range ("A1"). Select end with application.screen

) " ":" "" Replace (MyRange (I, J), "" "", "" "" "" "" "If J Recently in writing a Web page, need to query from the background of ASP Web pages to the MySQL recordset returned to the foreground ASP page, we know Ajax is not able to return from the background database recordset to the front page. Looking at a lot of data, for the time being, it's a good choice to convert the recordset to a JSON-formatted stream, and then to import the Weboffice control's

I. Properties and Methods　　1. Properties　　　　Click on the object above and the corresponding property is displayed below:　　　　　　2. Methods　　　　Double-click the upper-left object to see the appropriate method:　　　　ii. Types of dataByte 0To255Boolean TrueOrFalseInteger- +,768To +,767Long(Long integer type)-2,147,483,648To2,147,483,647 Single(single-precision floating-point type)Double(double-precision floating-point type) CurrencyDecimal Date -Years1Month1Day to9999Years AMonth toDayObjec

, the regular expression can be written directly into the arguments of the formula, without having to be placed inside the cell and then referenced.FunctionRegexreplace (ByValText as String,ByValMatchpattern as String,ByValReplacePattern as String,Optional ByValIngorecase as Boolean=True) as String'by Jing He 2017-9-1DimRegex as NewRegExp withregex. Global=True . MultiLine=True . IgnoreCase=ingorecase. Pattern=MatchpatternEnd withIfRegex. Test (Text) ThenRegexreplace=Regex.Replace (Text,

Sub Macro2 ()‘' Macro2 Macro‘' Shortcut key: Ctrl+s‘Dim I1 as IntegerI1 = 1For Loop A1-a EndFor I = 2 to Range ("a65536"). End (Xlup). Rows = Worksheets ("Sheet1"). Cells (I, 1)If Len (s) > ThenI1 = I1 + 1Sheet2.cells (I1, 1) = Worksheets ("Sheet1"). Cells (I, 1)Sheet2.cells (I1, 1). HorizontalAlignment = XlcenterSheet2.cells (I1, 1). VerticalAlignment = XlcenterSheet2.cells (I1, 2) = Worksheets ("Sheet1"). Cells (I, 2)Sheet2.cells (I1, 2). Horizontal