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]

Okay, it worked in the copy I made. is there something I need to change in the code for it to work in the original?

 

[mumps]

You would have to upload the original so I can test it.

 

[carrieebacon]

Here is the original. I have made some modifications since uploading the copy, but nothing VBA related.
Trial Storm Asbuilt Template.xlsm | Powered by Box

 

[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
    Set srcWS = Sheets("ASBUILT")
    Set desWS = Sheets("TBC TEXT")
    v1 = desWS.Range("B6", desWS.Range("B" & Rows.Count).End(xlUp)).Resize(, 11).Value
    v2 = srcWS.Range("H6", srcWS.Range("H" & Rows.Count).End(xlUp)).Resize(, 3).Value
    For i = LBound(v1) To UBound(v1)
        If WorksheetFunction.CountIf(srcWS.Range("H6", srcWS.Range("H" & Rows.Count).End(xlUp)), v1(i, 1)) > 0 Then
            Val1 = v1(i, 1) & "|" & v1(i, 5)
            Val2 = v1(i, 1) & "|" & v1(i, 8)
            Val3 = v1(i, 1) & "|" & v1(i, 11)
            For ii = LBound(v2) To UBound(v2)
                If v2(ii, 1) & "|" & v2(ii, 2) = Val1 Then
                    desWS.Range("G" & i + 5) = v2(ii, 3)
                ElseIf v2(ii, 1) & "|" & v2(ii, 2) = Val2 Then
                    desWS.Range("J" & i + 5) = v2(ii, 3)
                ElseIf v2(ii, 1) & "|" & v2(ii, 2) = Val3 Then
                    desWS.Range("M" & i + 5) = v2(ii, 3)
                End If
            Next ii
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[carrieebacon]

It worked! Thank you so much!!!!

 

[mumps]

You are very welcome.

 

[carrieebacon]

Alright...I'm back. I have made MANY modifications to the appearance/layout of this project. Is there a way I can get it to recognize the ID as well as the DIR (direction)? I don't know why I didn't think about this before. One thing at a time, I guess.

 

[mumps]

Is there a way I can get it to recognize the ID as well

Unless I misunderstood, I believe that the macro already does that. If the macro is not doing what you want, please upload the most recent version of your file and clarify in detail how it is not working for you using a few examples from your data.

 

[carrieebacon]

Final Template 02.20.2024 - Copy.xlsx | Powered by Box

Here ^ is the new and improved. I highlighted the cells where the data would need to populate in TBC TEXT based off of the data in AS-BUILT. If there is no direction but there is a shot location titled CL TOP, then it should go in column C in TBC TEXT.

 

[mumps]

If there is no direction, will the shot location always be "CL TOP" or can it be some other value?