Cell Referencing - Recognizing Data from Row

[carrieebacon]

Okay A) I would like to apologize for constantly posting on here, but I am learning. B) I have one sheet in a workbook that contains asbuilt data (ASBUILT), and one sheet that will be used to format the text that I will place into Trimble Business Center (TBC TEXT). I would like to pull (or reference) from column J in ASBUILT to place into either column D, F, I, or L. The thing is, it needs to align with the correct ID number in column B in ASBUILT. If there is a better way to do what I am trying to do, feel free to tell me. I have been working on putting this together for a while and I think I have lost my sanity.

Here is what I am working with.

This is the ASBUILT sheet

And this is the TBC TEXT sheet

 

[carrieebacon]

IT could be a different text value.

 

[mumps]

Try:

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, fnd As Range
    Set srcWS = Sheets("AS-BUILT")
    Set desWS = Sheets("TBC TEXT")
    v1 = desWS.Range("A5", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 13).Value
    v2 = srcWS.Range("G4", srcWS.Range("G" & Rows.Count).End(xlUp)).Resize(, 6).Value
    For i = LBound(v1) To UBound(v1)
        If WorksheetFunction.CountIf(srcWS.Range("G4", srcWS.Range("G" & Rows.Count).End(xlUp)), v1(i, 1)) > 0 Then
            Val1 = v1(i, 1) & "|" & v1(i, 5)
            Val2 = v1(i, 1) & "|" & v1(i, 9)
            Val3 = v1(i, 1) & "|" & v1(i, 13)
            For ii = LBound(v2) To UBound(v2)
                If v2(ii, 1) & "|" & v2(ii, 3) = Val1 Then
                    desWS.Range("F" & i + 4) = v2(ii, 6)
                ElseIf v2(ii, 1) & "|" & v2(ii, 3) = Val2 Then
                    desWS.Range("J" & i + 4) = v2(ii, 6)
                ElseIf v2(ii, 1) & "|" & v2(ii, 3) = Val3 Then
                    desWS.Range("N" & i + 4) = v2(ii, 6)
                End If
            Next ii
        End If
    Next i
    For ii = LBound(v2) To UBound(v2)
        If 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) = v2(ii, 6)
            End If
        End If
    Next ii
    Application.ScreenUpdating = True
End Sub

 

[carrieebacon]

Did this work for you in the copy in box?

 

[mumps]

Yes it did.

 

[carrieebacon]

If I want to change the columns its searching etc., what would I need to change?

 

[mumps]

Please describe in detail which columns. If you added or deleted columns or changed the organization of the data in any way, then upload the new file.

 

[carrieebacon]

Final Template 02.21.2024.xlsx | Powered by Box

It's the same column headers just in different columns because others were deleted that were not part of the code.

 

[mumps]

In order for this to work we have to make some changes for consistency. In the Design sheet, the ID's in column A do not match the ID's in column A of the TBC Text sheet because they contain the character "#". That character has to be removed in all the ID's in column A of the Design sheet. Also in the Design sheet, all rows in columns F, I and L must have a direction in them even if they are redundant. The cells in those three columns cannot be left blank otherwise the code thinks that the blank character is a actually a direction. Please upload a revised file with the changes.

 

[carrieebacon]

I removed the "#' character and inserted (N/A) in cells with no direction in the design sheet. Before I upload again, should I insert (N/A) in cells with no direction in the as-built sheet?

 

[mumps]

should I insert (N/A) in cells with no direction in the as-built sheet?

The AS-BUILT sheet is fine as it stands.