VBA Search Loop If Empty Copy Paste Next Help!!

ipon70

New Member
Joined
May 8, 2013
Messages
28
I am in VBA H**L right now.
Here is what I need the Search Loop to do.

In Column A cell "A2" is a changing value depending on the person selected. This is basically a RecordId 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 the amount of rows indicated in "A2". Paste it into whatever cell is at that location.
Then move onto the next cell and hunt for the next value, and repeat.

EXAMPLE: "A2" cell has a value of 954 in it indicating the row of that persons information, again basically a RecordId column. It starts looking at "H2" and finds information at cell "J2" copies that information, moves down to cell "J954" and pastes it there. 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 will 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.
 

Some videos you may like

Excel Facts

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub ipon()
   Dim Rng As Range
   
   For Each Rng In Range("H2:JZ2").SpecialCells(xlFormulas, 7).Areas
      Rng.Offset(Range("A2").Value - 1) = Rng.Value
   Next Rng
End Sub
 

ipon70

New Member
Joined
May 8, 2013
Messages
28
How about
VBA Code:
Sub ipon()
   Dim Rng As Range
 
   For Each Rng In Range("H2:JZ2").SpecialCells(xlFormulas, 7).Areas
      Rng.Offset(Range("A2").Value - 1) = Rng.Value
   Next Rng
End Sub
Ok sir this is an AWESOME start. I had to change the -1 to a -2 to hit the right line but now that works.
The one issue is its doing a blanket copy and overwriting previously filled in cells.
I entered a "No" that was moved into column X and Row 954. Ran the code, "No" is there. I then empty that cell on the original form they fill out, run the code again, and it deletes the "no" from column "X" and Row 954. I need it to leave cells that are blank on the form alone and not copy them again, or skip blanks. Is that possible?

Thanks for the help, you have I think...saved me....lol
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
VBA Code:
Sub ipon()
   Dim Cl As Range
   
   For Each Cl In Range("H2:JZ2").SpecialCells(xlFormulas, 7)
      If Cl <> "" Then Cl.Offset(Range("A2").Value - 2) = Cl.Value
   Next Cl
End Sub
 
Solution

ipon70

New Member
Joined
May 8, 2013
Messages
28
Ok, how about
VBA Code:
Sub ipon()
   Dim Cl As Range
  
   For Each Cl In Range("H2:JZ2").SpecialCells(xlFormulas, 7)
      If Cl <> "" Then Cl.Offset(Range("A2").Value - 2) = Cl.Value
   Next Cl
End Sub
Let me try this version when I get to work. Thank you so so so much.
 

ipon70

New Member
Joined
May 8, 2013
Messages
28

ADVERTISEMENT

IT WORKED YOU ARE A GOD!!!!

Thank you so much.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

ipon70

New Member
Joined
May 8, 2013
Messages
28
Let me try this version when I get to work. Thank you so so so much.
Can this be used to match a code to a code.
Search and match a "Employee Number" instead of a row number and paste values that way. Same area for values, look up changes to "A3" and the "Employee ID" column starts at "B4".
Wondering if we get a 1 off if that will mess up the row counting.....
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
That is a totally different question, so needs a new thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,943
Members
416,143
Latest member
JoyceMB

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