VBA Column Search Employee ID Paste Value

ipon70

New Member
Joined
May 8, 2013
Messages
28
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,769
Office Version
  1. 365
  2. 2010
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

New Member
Joined
May 8, 2013
Messages
28
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    10.6 KB · Views: 4

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,769
Office Version
  1. 365
  2. 2010
What happens when there's a record in row 5 or 6 or...?

Does it look like this?

Book1
ABCDEFGHIJKL
255555PASSFAILPASS
3Record IdEmployee IdNamePositionBandSupervisorHire DateHR1HR2HR3HR4HR5
465289754Brown, MarywhateverPJones, Susan3/17/2019     
5123456789     
655555PASSFAIL  PASS
Sheet2
Cell Formulas
RangeFormula
H4:L6H4=IF(AND($B$2=$B4,NOT(ISBLANK(H$2))),H$2,"")
 
Last edited:

ipon70

New Member
Joined
May 8, 2013
Messages
28

ADVERTISEMENT

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

Does it look like this?

Book1
ABCDEFGHIJKL
255555PASSFAILPASS
3Record IdEmployee IdNamePositionBandSupervisorHire DateHR1HR2HR3HR4HR5
465289754Brown, MarywhateverPJones, Susan3/17/2019     
5123456789     
655555PASSFAIL  PASS
Sheet2
Cell Formulas
RangeFormula
H4:L6H4=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

Well-known Member
Joined
May 8, 2018
Messages
1,769
Office Version
  1. 365
  2. 2010
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

Well-known Member
Joined
May 8, 2018
Messages
1,769
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

Maybe something like this:

Code:
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

New Member
Joined
May 8, 2013
Messages
28
Maybe something like this:

Code:
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

New Member
Joined
May 8, 2013
Messages
28
Maybe something like this:

Code:
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

Well-known Member
Joined
May 8, 2018
Messages
1,769
Office Version
  1. 365
  2. 2010
Afraid I can't explain that as it works for me.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,717
Messages
5,637,947
Members
416,994
Latest member
cappiccolo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top