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

[rlucas91]

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.

 

[Peter_SSs]

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.

 

[rlucas91]

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

 

[Peter_SSs]

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.

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

 

[rlucas91]

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.

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!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.