IF [find value in a column] THEN [copy values from multiple different columns] with VBA

[bobbyj1234]

Hi,

I am new to this Forum and very new to using VBA in excel, so please excuse my lack of proper vocabulary or knowledge.

I am a film editor and we have EDLs that show us all of the cuts in a film with time codes in and out - an excel workbook of the movie. We have a separate document called an ALE, which shows all of the info for each clip - without the time codes in and out. More of an excel key. When an EDL is created we are limited to an 8 character identifier, therefore we create 6-8 character codes to represent clips. These clips are used in multiple locations and can be present in the worksheet in 20 - 30 different rows if the clip is used multiple times in the film:

[TABLE="width: 336"]
<tbody>[TR]
[TD]ES_2232M[/TD]
[/TR]
[TR]
[TD]ES_2235M[/TD]
[/TR]
[TR]
[TD]ES_2240M[/TD]
[/TR]
[TR]
[TD]ES_2309[/TD]
[/TR]
[TR]
[TD]ES_2241[/TD]
[/TR]
[TR]
[TD]ES_2034


These codes then need to be replaced by the clip's full name:


[TABLE="width: 336"]
<tbody>[TR]
[TD]ES_2232M_NationalGeographic_118799_19351111.jpg[/TD]
[/TR]
[TR]
[TD]ES_2235M_NationalGeographic_530520_19351111.jpg[/TD]
[/TR]
[TR]
[TD]ES_2240M_NationalGeographic_581958_19351111.jpg[/TD]
[/TR]
[TR]
[TD]ES_2309_NationalGeographic_259913_19351111.jpg[/TD]
[/TR]
[TR]
[TD]ES_2241_NationalGeographic_1002867_19351111.jpg[/TD]
[/TR]
[TR]
[TD]ES_2034_NationalGeographic_259913_19351111.jpg

I have used this VBA to copy over the clip's full names correctly: (does require making two columns with the code and file name)

Attribute VB_Name = "Module1"Sub MultiFindNReplace()
'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
In addition to the clip name, there are 17 columns of data in the ALE. Is there any way to copy the 17 columns of data while replacing the clip identifier?

I am open to having 2 different VBA as well. I would assume that this is very similar to a VLookup but I am looking for something a little more automated. Any help is much appreciated. And once again, excuse my lack of knowledge on this subject, and happy to try to explain in more detail.

Thanks,
Bobby J

 

[JLGWhiz]

Don't know if this helps but, to copy from one workbook to another:

Workbooks(2).Sheets(1).Range("A1:Q1").Copy Workbooks(1).Range("A1")

Would copy 17 columns on row 1 of workbook 2 to the first row of workbook 1 starting in column A. You only need the anchor cell in the destination workbook when using this copy method. Also the workbook and sheet names should be used instead of the index numbers, since the index numbers are subject to variation depending on several scenarios. But the point is that you could add a line of code to your current macro to copy the data from one workbook to the other.