VBA - Arrays and Loops

[carrieebacon]

I have a code I need to adjust, but I am not quite sure how to go about it. The private sub FillElev takes data from the AS-BUILT sheet and processes it into the TBC TEXT sheet based on certain criteria. Is there a way to tell it that if there is an "x" in column Q of the AS-BUILT sheet, to skip over that row during processing?
Here is the link:
As-Built Workbook

 

[StephenCrump]

It looks like you need to change:

'This line
If v1(i, 1) <> "" And WorksheetFunction.CountIf(srcWS.Range("J7", srcWS.Range("J" & Rows.Count).End(xlUp)), Trim(v1(i, 1))) > 0 Then
'To
If v1(i, 1) <> "" And WorksheetFunction.CountIf(srcWS.Range("J7", srcWS.Range("J" & Rows.Count).End(xlUp)), Trim(v1(i, 1))) > 0 _
    And UCase(v1(i, 17)) <> "X" Then

 

[myall_blues]

I think this will do what you need. Comments in changed lines

Sub FillElevation()
    Application.ScreenUpdating = False
    Dim v1 As Variant, v2 As Variant, i As Long, ii As Long, srcWS As Worksheet, desWS As Worksheet
    Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String, Val5 As String, fnd As Range
    Set srcWS = Sheets("AS-BUILT")
    Set desWS = Sheets("TBC TEXT")
    v1 = desWS.Range("A7", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 20).Value
    v2 = srcWS.Range("J7", srcWS.Range("J" & Rows.Count).End(xlUp)).Resize(, 8).Value  '<== Change resize to 8 to include column Q
    For i = LBound(v1) To UBound(v1)
        If v1(i, 1) <> "" And WorksheetFunction.CountIf(srcWS.Range("J7", srcWS.Range("J" & Rows.Count).End(xlUp)), Trim(v1(i, 1))) > 0 Then
            Val1 = Trim(v1(i, 1)) & "|" & v1(i, 6)
            Val2 = Trim(v1(i, 1)) & "|" & v1(i, 9)
            Val3 = Trim(v1(i, 1)) & "|" & v1(i, 12)
            Val4 = Trim(v1(i, 1)) & "|" & v1(i, 15)
            Val5 = Trim(v1(i, 1)) & "|" & v1(i, 18)
            For ii = LBound(v2) To UBound(v2)
                If v2(ii, 8) <> "x" Then    '<== Process this block only if column Q (column 8 of the array) is not equal to "x"
                    If v2(ii, 1) & "|" & v2(ii, 3) = Val1 Then
                        desWS.Range("E" & i + 6) = Format(v2(ii, 7), "0.00")
                    ElseIf v2(ii, 1) & "|" & v2(ii, 3) = Val2 Then
                        desWS.Range("H" & i + 6) = Format(v2(ii, 7), "0.00")
                    ElseIf v2(ii, 1) & "|" & v2(ii, 3) = Val3 Then
                        desWS.Range("K" & i + 6) = Format(v2(ii, 7), "0.00")
                    ElseIf v2(ii, 1) & "|" & v2(ii, 3) = Val4 Then
                        desWS.Range("N" & i + 6) = Format(v2(ii, 7), "0.00")
                    ElseIf v2(ii, 1) & "|" & v2(ii, 3) = Val5 Then
                        desWS.Range("Q" & i + 6) = Format(v2(ii, 7), "0.00")
                    End If
                End If
            Next ii
        End If
    Next i
    For ii = LBound(v2) To UBound(v2)
        If v2(ii, 1) <> "" And v2(ii, 2) <> "" And v2(ii, 3) = "" Then
            Set fnd = desWS.Range("A:A").Find("*" & v2(ii, 1) & "*", LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                desWS.Range("C" & fnd.Row) = Format(v2(ii, 7), "0.00")
            End If
        End If
    Next ii
    Application.ScreenUpdating = True
End Sub