EXCEL VBA 二维表转换成一维表
Sub 按钮2_Click()Rem 初始化Worksheets(1).ActivateDim sizeArr(5)sizeArr(0) = "XS"sizeArr(1) = "S"sizeArr(2) = "M"sizeArr(3) = "L"sizeArr(4) = "XL"Dim col, row, productNum, refCol, targetRowtargetRow = 1Sheet2.Range("A1:A65536").ClearDim iA As IntegerRem 交叉表转一维表For row = 3 To 65536: Rem 处理行If StrComp(Sheet1.Cells(row, 1), "") = 0 Then GoTo line: Rem 如果为空就中止处理iA = Asc(Left(Sheet1.Cells(row, 1), 1))Rem 如果不是英文字符开头,就跳过If (iA >= 65 And iA <= 90) Or (iA >= 97 And iA <= 122) ThenFor col = 3 To 7: Rem 处理列productNum = Sheet1.Cells(row, 1) & Left(Sheet1.Cells(row, 2), 2)Rem 取尺码对照表列号If StrComp(Left(productNum, 1), "K") = 0 ThenrefCol = 4ElseIf StrComp(Left(productNum, 1), "B") = 0 ThenrefCol = 3ElseIf StrComp(Left(productNum, 1), "C") = 0 ThenrefCol = 3ElserefCol = 2End IfproductNum = productNum & Sheet3.Cells(col - 1, refCol)Rem 取铺货件数，如果<=0，就跳过，否则插入一维表If Sheet1.Cells(row, col) > 0 ThenSheet2.Cells(targetRow, 1) = productNum & "," & Sheet1.Cells(row, col)targetRow = targetRow + 1End IfNextEnd Ifline:NextIf targetRow = 1 ThenWorksheets(1).ActivateMsgBox "二维表没有数据！"ElseSheet2.Range("D4").Value = targetRow - 1Worksheets(2).ActivateMsgBox "生成成功！"End IfEnd Sub
