Notes

' 각 행에 대해 작업
For i = 2 To lastRow
' 값 가져오기
targetValue = ws.Cells(i, targetCol).Value
If lslCol > 0 Then lslValue = ws.Cells(i, lslCol).Value Else lslValue = Empty
If uslCol > 0 Then uslValue = ws.Cells(i, uslCol).Value Else uslValue = Empty

' 문자가 포함된 값을 숫자로 변환
targetValue = CleanAndConvertToNumber(targetValue)
lslValue = CleanAndConvertToNumber(lslValue)
uslValue = CleanAndConvertToNumber(uslValue)

' Target 값이 비어있거나 0인 경우 대체 값 계산
If IsEmpty(targetValue) Or targetValue = 0 Then
If Not IsEmpty(lslValue) And IsNumeric(lslValue) Then
targetValue = CDbl(lslValue) + 0.001
ElseIf Not IsEmpty(uslValue) And IsNumeric(uslValue) Then
targetValue = CDbl(uslValue) - 0.001
Else
targetValue = 0
End If
Else
targetValue = CDbl(targetValue) + "0.00"
End If

' 값을 0.001 단위로 올림 처리
If IsNumeric(targetValue) Then
targetValue = Application.WorksheetFunction.Ceiling(targetValue, 0.001)

' 정수부 15자리, 소수부 10자리로 제한
If Abs(targetValue) >= 10 ^ 15 Then
targetValue = Application.WorksheetFunction.Sign(targetValue) * (10 ^ 15 - 1)
End If

' 소수부 10자리로 반올림
targetValue = Application.WorksheetFunction.Round(targetValue, 10)
End If

If IsNumeric(lslValue) Then
lslValue = Application.WorksheetFunction.Ceiling(CDbl(lslValue) + 0.001, 0.001)
lslValue = Application.WorksheetFunction.Round(lslValue, 10)
End If

If IsNumeric(uslValue) Then
uslValue = Application.WorksheetFunction.Ceiling(CDbl(uslValue) - 0.001, 0.001)
uslValue = Application.WorksheetFunction.Round(uslValue, 10)
End If

' 샘플 수량 가져오기
sampleQty = 0
If Not IsEmpty(ws.Cells(i, sampleQtyCol).Value) Then
If IsNumeric(ws.Cells(i, sampleQtyCol).Value) Then
sampleQty = CLng(ws.Cells(i, sampleQtyCol).Value)
Else
' 숫자가 아닌 경우 숫자만 추출 시도
Dim tempVal As String, j As Integer
tempVal = ws.Cells(i, sampleQtyCol).Value
Dim numStr As String
numStr = ""

For j = 1 To Len(tempVal)
If IsNumeric(Mid(tempVal, j, 1)) Then
numStr = numStr & Mid(tempVal, j, 1)
End If
Next j

If Len(numStr) > 0 Then sampleQty = CLng(numStr)
End If
End If

' target, lsl, usl 값이 모두 비어있는 경우 값 복사를 하지 않음
If sampleQty > 0 And (Not IsEmpty(targetValue) Or Not IsEmpty(lslValue) Or Not IsEmpty(uslValue)) Then
For colOffset = 0 To sampleQty
If startCol + colOffset <= ws.Columns.Count Then
If startCol + colOffset <> targetCol Then
ws.Cells(i, startCol + colOffset).NumberFormat = "@"
ws.Cells(i, startCol + colOffset).Value = Format(targetValue, "0.000")
End If
End If
Next colOffset

processedCount = processedCount + 1
End If
Next i