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

 

[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("A7", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 20).Value
    v2 = srcWS.Range("J7", srcWS.Range("J" & Rows.Count).End(xlUp)).Resize(, 7).Value
    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)
            For ii = LBound(v2) To UBound(v2)
                If v2(ii, 1) & "|" & v2(ii, 3) = Val1 Then
                    desWS.Range("E" & i + 6) = v2(ii, 7)
                ElseIf v2(ii, 1) & "|" & v2(ii, 3) = Val2 Then
                    desWS.Range("H" & i + 6) = v2(ii, 7)
                ElseIf v2(ii, 1) & "|" & v2(ii, 3) = Val3 Then
                    desWS.Range("K" & i + 6) = v2(ii, 7)
                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) = v2(ii, 7)
            End If
        End If
    Next ii
    Application.ScreenUpdating = True
End Sub

 

[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("A7", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 20).Value
    v2 = srcWS.Range("J7", srcWS.Range("J" & Rows.Count).End(xlUp)).Resize(, 7).Value
    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)
            For ii = LBound(v2) To UBound(v2)
                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")
                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

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, you have multiple occurrences of each ID in ASBUILT but only one occurrence of each ID in TBC TEXT. How do you decide which of the multiple values in column J in ASBUILT to pull and how do you decide into which column (D, F, I, or L) to place it? It would be helpful if you could post what your end result should look like based on the data you posted.

 

[carrieebacon]

Here is the link:
TRIAL Storm Asbuilt - Copy.xlsm | Powered by Box

Okay SO. The ultimate goal is for it to look like the DESIGN sheet, but containing ASBUILT data. So I referenced Columns A, B, C, E, G, H, J, K, AND M from DESIGN to TBC TEXT for the correct text, ID number, and direction. Essentially, only the elevation (ELV.) needs to be put into the TBC TEXT sheet. I am going to use CI 11 as an example. In the design, CI 11 has three inverts (INV.). Those elevation points were recorded on the jobsite and I import them into the spreadsheet as a csv file and then run a macro to organize and clean up. So in the ASBUILT sheet, there are three rows with the same ID number of 11 because there are three inverts. I guess one way to ensure they are placed correctly into TBC TEXT is by the direction (Dir.).

 

[carrieebacon]

Let me correct myself... I meant to say "Essentially, only the Invert Elevation (Inv. Elv.) needs to be put into the TBC TEXT sheet."

 

[mumps]

I'm a little confused. In the DESIGN sheet, for ID 11 you have 6.08 for W, 9.08 for N and 6.08 for E. Looking at ID 11 in the ASBUILT sheet, you have -19.5 for W, -19.3 for N and -18.2 for E. The values for each direction don't match. Please clarify in detail.

 

[carrieebacon]

Design elevation is going to be different from asbuilt. Design is what the engineers say the elevation should be, asbuilt is what the elevation actually is after utilities have been installed. The design details are only in there for when I eventually compare data in the TOLERANCE sheet.

 

[mumps]

The ultimate goal is for it to look like the DESIGN sheet, but containing ASBUILT data.

I guess that this is what confused me. Are you saying that you want the values -19.5 for W, -19.3 for N and -18.2 for E. copied to the TBC TEXT sheet?

 

[carrieebacon]

That is correct. I would like to find a way to do this without having to copy and paste each one.