good on you , vba

i should spend some time reading the vba in excel. it really can do anything and becomes efficient (honestly i was not efficient today, learning how to use vba and want to develop a perfect program. NO never do that again, just let it running)

here is some useful reference:
——————————————–

用VBA得到EXCEL表格中的行数和列数

  每种方法中上面的是Excel的行数,下面的是Excel的列数。
方法1:
  ActiveSheet.UsedRange.Rows.Count
  ActiveSheet.UsedRange.Columns.Count
缺点:有时可能会比实际数大一些,原因是如果你把最后几行(列)数据清除后(非整行或整列删除),用这个命令仍返回未清除前的值。就是说现在虽然是空的,但是你曾经用过也算你的。

方法2:
  ActiveSheet.Range(“A65535”).End(xlUp).Row
  ActiveSheet.Range(“IV1”).End(xlToLeft).Column
  可以简写为:
  ActiveSheet.[A65536].End(xlUp).Row
  ActiveSheet.[IV1].End(xlToLeft).Column
缺点:只能计算出一列(行)的最后一个单元格所在的行(列)数。本例是只返回A列最后一个单元格所占的行数。

方法3:
  ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
  ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
  缺点:在工作表进行对删除或清除操作时也会变得比实际情况大。

方法4:
  ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
  ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
  缺点:在工作表进行对删除或清除操作时也会变得比实际情况大。

方法5:
  Application.CountA(ActiveSheet.Range(“A:A”))
  Application.CountA(ActiveSheet.Range(“1:1”))
  只能统计一列(行)的实际使用情况,得到的不一定是最后一行(列)的位置。方法2的数值比此方法大时,说明在A列的数据间有空白未填写的单元格

方法6:
  ActiveSheet.Cells.Find(What:=”*”, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  ActiveSheet.Cells.Find(What:=”*”, After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  效果同方法2

以上方法中比较常用的是方法1和方法2。
from: http://www.okexcel.com.cn/bbs/viewthread.php?tid=26
———————————————–

the following is my code:
1. generate Ncase sheets and import
2.generate equations and combine

Option Explicit
Sub Button1_Click()
Dim nsheet As Integer, ncase As Integer, i As Integer, nrwst As Integer, nsheet As Integer
Dim connstring As String, fpath As String, exnm As String

nsheet = Sheets.Count
Worksheets(“Instruction”).Select
ncase = Cells(6, 3).Value
fpath = Cells(6, 7).Value

If fpath = “” Then
fpath = Application.Path & “\Result_Case”
End If
exnm = Cells(6, 9).Value
nrwst = Cells(10, 3).Value

If nsheet < ncase + 1 Then
Sheets.Add after:=Sheets(Sheets.Count), Count:=(ncase – nsheet + 1)
End If

For i = 1 To ncase
Worksheets(i + 1).Name = “Case” & i
Worksheets(i + 1).Select
connstring = “TEXT;” & fpath & i & exnm
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range(“$A$1”))
.Name = “Result_Case” & i
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = nrwst
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileFixedColumnWidths = Array(27)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Next i

End Sub
Sub Button2_Click()
Dim nrow As Long, i As Integer, ncase As Integer
Dim eqstr As String
Dim ws As Worksheet

Worksheets(“Instruction”).Select
ncase = Cells(6, 3).Value
eqstr = Cells(23, 7).Value

For i = 1 To ncase
Worksheets(i + 1).Select
nrow = ActiveSheet.Range(“a65536”).End(xlUp).Row
Range(“c2”).Select
ActiveCell.FormulaR1C1 = eqstr
Range(“C2”).Select
Selection.AutoFill Destination:=Range(“C2”, Range(“c2”).Offset(nrow – 2, 0))
Next i

Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = “Combined”
Set ws = Worksheets(“Combined”)
For i = 1 To 24
ws.Range(ws.Range(“a1”).Offset(0, (i – 1) * 3), ws.Range(“a1”).Offset(65535, i * 3 – 1)).Value = Worksheets(“Case” & i).Range(“a1:c65536”).Value
Next i
End Sub

Sub deleteblank()
Dim i As Long, j As Long

Worksheets(“CombinedDeletedMasked”).Select
j = 1
For i = 3 To 72 Step 3
Worksheets(“result”).Cells(j, 1) = Cells(1048576, i)
j = j + 1
Next i
End Sub

Advertisements

About yhtian

I am an academic researcher working on Offshore Geotechnical Engineering. My blog aims to write down some work related trivial things and tricks about software, programming. It is basically a memo for me to back up some thoughts and small details. But I am more than happy if someone would visit and discuss.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s