EXCEL非规整数据无法通过拖动而自动填充公式的脚本方法

首先，在做EEPROM，FLASH等check工作时，会遇到数据结构块不是16的整数倍，这时候在EXCEL中无法通过其提供的下拉拖动式方法自动填充公式，那只能手动编写。对于三五十行还好，但对于成千上万行的数据转换公式来说，无疑效率大打折扣。现备份一份脚本代码，供以后再遇到此类问题时的参考。

dim row
dim colum, col
dim index
dim str, calStr, colLabel
dim colName
dim resRow, resCol
dim srcRow, srcCol
dim rngStr, rngStr1, rngStr2
dim indexArray, i
dim mainStr(407)


colName = "CDEFGHIJKLMNOPQR"

REM str = ""
REM for row = 14 to 246 step 1
    REM for colum = 1 to 16 step 2
        REM str = str&"="&mid(colName, colum, 1)&row&"+"&mid(colName, (colum+1),1)&row&"*256"&vbCrlf
    REM next
REM next


REM calStr = ""
REM '=if((),(),()) & 
REM for row = 900 to 949 step 1
    REM for colum = 1 to 14 step 1
        REM colLabel = mid(colName, colum, 1)& row
        REM if(colum = 1)then
            REM calStr = calStr & "="
        REM End If
        REM calStr = calStr & "if((HEX2DEC(" & colLabel & ")<>0), CHAR(HEX2DEC(" & colLabel & ")), " & chr(34) & chr(34) & ")" & " & "
    REM next
    REM calStr = mid(calStr, 1, Len(calStr)-3)
    REM calStr = calStr & vbCrlf
REM next


calStr = ""
col = 13 ' "O"
srcRow = 218
indexArray = 0
For row = 1 To 400 Step 1
    If col > 16 Then
        col = 1
        srcRow = srcRow + 1
    End If
    rngStr = Mid(colName, col, 1) & srcRow
    mainStr(indexArray) = rngStr
    indexArray = indexArray + 1
    col = col + 1
Next

calStr = ""
colName = " YAGAO"
i = 0
for row = 219 to 251 step 2
    calStr = calStr & "=HEX2DEC(" & mainStr(i) & ")+Hex2Dec(" & mainStr(i+1) & ")*256" & space(136-33) & _
                      "=Hex2Dec(" & mainStr(i+8) & ")+Hex2Dec(" & mainStr(i+9) & ")*256" & space(136-33) & _
                      "=Hex2Dec(" & mainStr(i+16) & ")+Hex2Dec(" & mainStr(i+17) & ")*256" & vbCrlf
                      
    calStr = calStr & "=" & chr(34) & "20" & chr(34) & " & HEX2DEC(" & mainStr(i+2) & ") & " & chr(34) & "-" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+3) & ") & " & chr(34) & "-" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+4) & ") & " & chr(34) & " " & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+5) & ") & " & chr(34) & ":" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+6) & ") & " & chr(34) & ":" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+7) & ")    " & _                     
                      "=" & chr(34) & "20" & chr(34) & " & HEX2DEC(" & mainStr(i+10) & ") & " & chr(34) & "-" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+11) & ") & " & chr(34) & "-" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+12) & ") & " & chr(34) & " " & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+13) & ") & " & chr(34) & ":" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+14) & ") & " & chr(34) & ":" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+15) & ")    " & _
                      "=" & chr(34) & "20" & chr(34) & " & HEX2DEC(" & mainStr(i+18) & ") & " & chr(34) & "-" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+19) & ") & " & chr(34) & "-" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+20) & ") & " & chr(34) & " " & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+21) & ") & " & chr(34) & ":" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+22) & ") & " & chr(34) & ":" & chr(34) & _
                                                       " & Hex2dec(" & mainStr(i+23) & ")" & vbCrlf
    i = i + 24
next

set fs=createobject("scripting.filesystemobject")
set f =fs.opentextfile("e:\text.txt",8)
f.writeblanklines(1)
f.writeline calStr
f.close
set f =nothing
set fs=nothing