Copy value in cell AX to DX based on value in A(X-1)

rlucas91

New Member
Joined
Oct 5, 2020
Messages
3
Hi everyone,

Long time Excel user, first time posting as it has been some time since I have done anything with VBA and I have pretty much forgotten everything I once knew.

I currently have a working macro that filters a list of copied data but because of some formatting issues in the source file, there is one particular piece of information that cannot be copied to the correct column and therefore filtered properly. The information I need once copied and filtered appears in Column A but I need it to appear in Column D, however this only applies if the cell above it contains the value "Manufacture name" as all other names in Column A don't have this problem. I am assuming I need some kind of loop as the phrase "Manufacture name" may appear more than once. I've tried using an IF formula within the VBA code and using some offset functions but am not getting anywhere.

Many thanks in advance for your help.


1601882612790.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Welcome to the MrExcel board!

Your description talks about filtered data but your image does not indicate any filtering. Can you shed any light on that?

I can see that you want xxxx and yyyy copied to column D but why is zzzz in column D in row 18?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
 
Upvote 0
Welcome to the MrExcel board!

Your description talks about filtered data but your image does not indicate any filtering. Can you shed any light on that?

I can see that you want xxxx and yyyy copied to column D but why is zzzz in column D in row 18?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Hi Peter,

Thanks for your reply. The data that I am working with is confidential so I cannot provide the actual filtered data so I instead provided an example. The image was just an example of how the copied information appears once it has been filtered and the irrelevant rows hidden, which is done using a macro and is currently working fine except for when Column A has the phrase "Manufacture name". I don't want to mess with the macro, rather just add a few lines to the end for what I need.

Where column A has any other name (for example "Processing name"), the filtering macro works fine and the contents of, for example, A18 (zzzz) are automatically moved across to D18. When "Manufacture name" appears in Column A, the xxxx or yyyy are not moved across. Specifically what I want the code to achieve is:

- Searching in Column A for the phrase "Manufacture name"
-If "Manufacture name" is found, then the cell directly below is selected and copied
-The copied cell is pasted in the same row but in Column D
 
Upvote 0
The data that I am working with is confidential so I cannot provide the actual filtered data so I instead provided an example.
Sample data is fine so long as it is representative of your actual data. Still best with XL2BB though so helpers don't have to manually type out data to test.

See if this would be any use. It only searches for "Manufacture name" in the visible cells in column A.

VBA Code:
Dim rFound As Range
Dim FirstAddr As String

With Range("A1", Range("A" & Rows.Count).End(xlUp))
  Set rFound = .Find(What:="Manufacture name", LookAt:=xlWhole, MatchCase:=False)
  If Not rFound Is Nothing Then
    FirstAddr = rFound.Address
    Do
      rFound.Offset(1).Copy Destination:=rFound.Offset(1, 3)
      Set rFound = .Find(What:="Manufacture name", After:=rFound, LookAt:=xlWhole, MatchCase:=False)
    Loop Until rFound.Address = FirstAddr
  End If
End With
 
Upvote 0
Sample data is fine so long as it is representative of your actual data. Still best with XL2BB though so helpers don't have to manually type out data to test.

See if this would be any use. It only searches for "Manufacture name" in the visible cells in column A.

VBA Code:
Dim rFound As Range
Dim FirstAddr As String

With Range("A1", Range("A" & Rows.Count).End(xlUp))
  Set rFound = .Find(What:="Manufacture name", LookAt:=xlWhole, MatchCase:=False)
  If Not rFound Is Nothing Then
    FirstAddr = rFound.Address
    Do
      rFound.Offset(1).Copy Destination:=rFound.Offset(1, 3)
      Set rFound = .Find(What:="Manufacture name", After:=rFound, LookAt:=xlWhole, MatchCase:=False)
    Loop Until rFound.Address = FirstAddr
  End If
End With
Works like a dream, thanks so much!
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,566
Members
452,652
Latest member
eduedu

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
Back
Top