VBA Column Search Employee ID Paste Value

[ipon70]

Here is what I need the Search Loop to do.

In Column B cell "B2" is a changing value depending on the person selected. This is basically a EmployeeID column.
Starring In Column H cell "H2:JZ2" are changing values. These values could be nothing (empty), or contain data.

Here is what I need it to do.
I need it to hunt across the 2nd row again starting at "H2" for the first cell with data, if it finds it, copy that value, and then move down to the employee record that matches the value in "B2". Paste it into whatever cell is at that location.
Then move onto the next cell and hunt for the next value, and repeat. If the cell is blank it should skip it, and not copy and paste as to not overwrite previous data.


EXAMPLE: "B2" cell has a value of 652065489 in it, indicating the EmployeeID of that person. It starts looking at "H2" and finds information at cell "J2" copies that information, moves down to row with the employee record and pastes it in the J column, in the row that matches the EmployeeID (652065489). Then starts over at "K2" and looks for data, then "L2" and looks for data, so on and so forth until it reaches "JZ2" and then stops.
This should eliminate if the employee row changes over time as new employee's come in and leave. Yes? Am I thinking correctly?

This will also be triggered manually by a person pushing an button to "save or update" the record.

Thank you so so much in advance, and if you have questions please ask.

I hope that all makes sense.

 

[kweaver]

It almost makes sense to me but showing a sample of data and expected results would be very helpful...please use XL2BB to show the data.

Didn't you have a similar (or exact) post earlier that was solved?

 

[ipon70]

It almost makes sense to me but showing a sample of data and expected results would be very helpful...please use XL2BB to show the data.

Didn't you have a similar (or exact) post earlier that was solved?

Yes I did and this dovetails into that same project.
I am not allowed to install anything on the computers we work on, so I just have a screen shot and hopefully that is enough.
I am always up for answering questions.

 

[kweaver]

What happens when there's a record in row 5 or 6 or...?

Does it look like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2		55555						PASS	FAIL			PASS
3	Record Id	Employee Id	Name	Position	Band	Supervisor	Hire Date	HR1	HR2	HR3	HR4	HR5
4		65289754	Brown, Mary	whatever	P	Jones, Susan	3/17/2019
5		123456789
6		55555						PASS	FAIL			PASS
Sheet2

Cell Formulas
Range	Formula
H4:L6	H4	=IF(AND($B$2=$B4,NOT(ISBLANK(H$2))),H$2,"")

 

[ipon70]

What happens when there's a record in row 5 or 6 or...?

Does it look like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2		55555						PASS	FAIL			PASS
3	Record Id	Employee Id	Name	Position	Band	Supervisor	Hire Date	HR1	HR2	HR3	HR4	HR5
4		65289754	Brown, Mary	whatever	P	Jones, Susan	3/17/2019
5		123456789
6		55555						PASS	FAIL			PASS
Sheet2

Cell Formulas
Range	Formula
H4:L6	H4	=IF(AND($B$2=$B4,NOT(ISBLANK(H$2))),H$2,"")

There will not be any blank rows if that is what you are asking. All rows will be filled in once starting at row 4. But will eventually run out at around 2500 and will be empty after that point.
And yes some could be pass fail, some could be yes no, and some could be just comments.

 

[kweaver]

My question is actually are you trying to fill in the Hn info wherever that Employee ID appears with the info from row 2 based on B2?
If so, does my suggestion work? It will NOT retain the info in the subsequent rows when B2, etc. changes. To do that, you'd have to use VBA.

 

[kweaver]

Maybe something like this:

Sub FillIn()
Dim lc As Long, lr As Long, i As Long, iRow As Variant
lr = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
lc = Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Column
Set iRow = Range("B4:B" & lr).Find(Range("B2"))
iRow = iRow.Address
iRow = Range(iRow).Row
 For i = 8 To lc
   If Not IsEmpty(Cells(2, i)) Then
     Cells(iRow, i) = Cells(2, i)
     Else
    End If
 Next
End Sub[code]

 

[ipon70]

Maybe something like this:

Sub FillIn()
Dim lc As Long, lr As Long, i As Long, iRow As Variant
lr = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
lc = Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Column
Set iRow = Range("B4:B" & lr).Find(Range("B2"))
iRow = iRow.Address
iRow = Range(iRow).Row
For i = 8 To lc
   If Not IsEmpty(Cells(2, i)) Then
     Cells(iRow, i) = Cells(2, i)
     Else
    End If
Next
End Sub[code]

Let me try your code, but yes. the EmployeeID could travel up and down the form following the employee, and any new entry would need to follow that EmployeeId and not the row it was on.
The previously mentioned row thing, is almost exactly the same as this issue, only with ID's that can move.

Thanks again for the response and let me give this a try.

 

[ipon70]

Maybe something like this:

Sub FillIn()
Dim lc As Long, lr As Long, i As Long, iRow As Variant
lr = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
lc = Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Column
Set iRow = Range("B4:B" & lr).Find(Range("B2"))
iRow = iRow.Address
iRow = Range(iRow).Row
For i = 8 To lc
   If Not IsEmpty(Cells(2, i)) Then
     Cells(iRow, i) = Cells(2, i)
     Else
    End If
Next
End Sub[code]

So I get a VB Run-Time error '91':
Object variable or With block variable not set.

 

[kweaver]

Afraid I can't explain that as it works for me.