我剛剛使用 VBA 來自動化計算，只是為了消除測量資料 excel 檔案的人為錯誤因素。雖然我已經使該功能正常作業，但我只能弄清楚如何一次為一行執行此操作。計算特定于每一行的資料，所以我首先認為我可以將 Range("J3") 值更改為 Range("J3:J52") 以進行 50 次計算。
如何使用所述特定行的資料分別計算每一行的函式？如果它對所有 50 行都運行，或者我必須找出一些回圈函式來找到要計算的行數，對我來說并不重要，只要最后按下一個按鈕就會使魔術發生。
我已經包含了一張表格的截圖以供參考，主要計算是在 excel 中完成的，但是這樣做是從幾個不同的校正計算中選擇正確的選項并向用戶解釋原因。
自從我開始學習這個來開發這個工具以來，我一直在嘗試到處尋找方法和除錯解決方案，我認為這會在開始時導致一些不必要的 Dim 行，但如果它運行，我不會去洗掉它們..
我的代碼如下所示：
'The main function, activated by a simple button Sub'
Function ISO16032()
'DeltaL Range'
Dim DeltaL As Range
Set DeltaL = Range("F3")
'Result is the corrected value in G column'
Dim Result As Long
'Note is the calc note in H column'
Dim Note As String
'X is the DeltaL between noise and background noise'
Dim x As Long
x = Range("F3").Value
Select Case Range("F3").Value
'No correction when X = > 10'
Case 10.6 To 200
Result = Range("J3")
Range("G3").Value = Result
Note = "No correction"
Range("H3").Value = Note
'Correction according to ISO16032 when X = between 4 and 10'
Case 3.6 To 10.5
Result = Range("K3")
Range("G3").Value = Result
Note = "Correction per ISO16032"
Range("H3").Value = Note
'Maximal correction value set to 2,2 dB if X < 4'
Case 0.1 To 3.5
Result = Range("L3")
Range("G3").Value = Result
Note = "Correction limit set to 2,2 dB"
Range("H3").Value = Note
'If x = < 0, the measurement is invalid'
Case Else
Note = "Repeat measurement!"
Range("H3").Value = Note
End Select
End Function
uj5u.com熱心網友回復：
嗨，歡迎來到 stackoverflow，我認為一個簡單的回圈，在你的 ISO 函式中添加一個引數，可以讓你像這樣解決你的問題
Sub Looping()
For i = 3 To 52
' Convert i to String because we need to concatenate with the letter F, G, H...
Call ISO16032(CStr(i))
Next
End Sub
Function ISO16032(Cell_X)
'DeltaL Range'
Dim DeltaL As Range
Set DeltaL = Range("F" Cell_X)
'Result is the corrected value in G column'
Dim Result As Long
'Note is the calc note in H column'
Dim Note As String
'X is the DeltaL between noise and background noise'
Dim x As Long
x = Range("F" Cell_X).Value
Select Case Range("F" Cell_X).Value
'No correction when X = > 10'
Case 10.6 To 200
Result = Range("J" Cell_X)
Range("G" Cell_X).Value = Result
Note = "No correction"
Range("H" Cell_X).Value = Note
'Correction according to ISO16032 when X = between 4 and 10'
Case 3.6 To 10.5
Result = Range("K" Cell_X)
Range("G" Cell_X).Value = Result
Note = "Correction per ISO16032"
Range("H" Cell_X).Value = Note
'Maximal correction value set to 2,2 dB if X < 4'
Case 0.1 To 3.5
Result = Range("L" Cell_X)
Range("G" Cell_X).Value = Result
Note = "Correction limit set to 2,2 dB"
Range("H" Cell_X).Value = Note
'If x = < 0, the measurement is invalid'
Case Else
Note = "Repeat measurement!"
Range("H" Cell_X).Value = Note
End Select
End Function
編輯：想像我一樣縮進你的代碼以使其更具可讀性（或者像@Darren Bartrup-Cook 在編輯你的問題時所做的那樣）
uj5u.com熱心網友回復：
這更多是如何計算每一行的示例。
只是讓您的代碼跨多行作業。
帶有...結束陳述句的單元格
'No need for Sub to call function that doesn't return anything.... just write a sub.
Public Sub ISO16032()
Dim LastRow As Long
With ThisWorkbook.Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, 10).End(xlUp).Row
Dim RowCounter As Long
Dim Result As Long
Dim Note As String
'Only calculate if there is data in rows 3 onwards.
If LastRow >= 3 Then
'Reset results on each pass.
'Assuming -1 is an impossible answer so code knows
'not to put anything on Case Else.
Result = -1
Note = ""
'Cycle through each row and calculate.
For RowCounter = 3 To LastRow
Select Case .Cells(RowCounter, 6) 'Look at column F(column 6) on each row.
Case 10.6 To 200
Result = .Cells(RowCounter, 10)
Note = "No corrections."
Case 3.6 To 10.5
Result = .Cells(RowCounter, 11)
Note = "Correction per ISO16032"
Case 0.1 To 3.5
Result = .Cells(RowCounter, 12)
Note = "Correction limit set to 2,2 dB"
Case Else
Note = "Repeat measurement!"
End Select
'Place results on sheet.
.Cells(RowCounter, 7) = IIf(Result >= 0, Result, "")
.Cells(RowCounter, 8) = Note
Next RowCounter
End If
End With
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/405390.html
標籤：