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]

Was that a one-time mistake or can it happen again? That kind of mistake will cause problems for the code.

 

[carrieebacon]

It won't happen again. While I am working on this, my boss is creating a feature code library for the field techs. With the FXL in their data collectors, they wont be able to just type whatever they want. They'll have to choose from a drop down list.

 

[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(, 12).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, 6)
            Val2 = v1(i, 1) & "|" & v1(i, 9)
            Val3 = v1(i, 1) & "|" & v1(i, 12)
            For ii = LBound(v2) To UBound(v2)
                If v2(ii, 1) & "|" & v2(ii, 2) = Val1 Then
                    desWS.Range("F" & i + 5) = v2(ii, 3)
                ElseIf v2(ii, 1) & "|" & v2(ii, 2) = Val2 Then
                    desWS.Range("I" & i + 5) = v2(ii, 3)
                ElseIf v2(ii, 1) & "|" & v2(ii, 2) = Val3 Then
                    desWS.Range("L" & i + 5) = v2(ii, 3)
                End If
            Next ii
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[carrieebacon]

Nothing happened when I ran the code, not even an error.

 

[mumps]

I tested the macro on the file you uploaded and it worked properly. Are you using the macro on a different file?

 

[carrieebacon]

I am using it on the original file. I made a copy of it which is what I uploaded to box.com

 

[mumps]

This what I got when I ran the macro. Of course, only ID's 11 and 15 were completed.

carrieebacon.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
5	STR.	ID	TOP	ELV.	INV.	ELV.	DIR.	INV.	ELV.	DIR.	INV.	ELV.	DIR.
6	CI	11	FR:		INV. IN	-19.5	W	INV. IN	-19.3	N	INV. OUT	-18.2	E
7	CI	15	FR:		INV. IN	-18.5	N	INV. IN	-18.4	E	INV. OUT	-18.4	S
8	CI	154	FR:		INV. IN		E	INV. OUT		W	0		0
9	CI	155	FR:		INV. IN		E	INV. OUT		W	0		0
10	CI	156	FR:		INV. IN		E	INV. OUT		W	0		0
11	CI	157	FR:		INV. IN		E	INV. OUT		W	0		0
12	CI	164	FR:		INV. OUT		W	0		0	0		0
13	CI	165	FR:		INV. IN		E	INV. OUT		W	0		0
14	CI	167	FR:		INV. OUT		E	0		0	0		0
15	CI	169	FR:		INV. IN		N	INV. OUT		S	0		0
16	CI	172	FR:		INV. OUT		S	0		0	0		0
17	CI	173	FR:		INV. OUT		E	0		0	0		0
18	CI	175	FR:		INV. OUT		NE	0		0	0		0
19	CI	185	FR:		INV. IN		W	INV. OUT		SE	0		0
TBC TEXT

 

[carrieebacon]

So the data will populate in the respective row in TBC TEXT only if all of the required asbuilt data is there? Or am I blind. Either way, it's fine I just want to have my ducks in a row.

 

[mumps]

The macro searches to check if each ID in the TBC TEXT sheet exists in the ASBUILT sheet. If it exists, it will populate in the respective row in TBC TEXT if the data exists in the ASBUILT sheet. Otherwise it will return blank values.

 

[carrieebacon]

I have no idea why it isnt working. let me open up the copy and try it there.