Hello everyone, I don´t have any particular VBA skills, but I have attempted to do a macro to change a variable. It however does not seem to be working.
The variable in question is a String from within the Macro, and it should be easy. I was following some tips from this post, but that does not seem to be working in my case, since I have changed a bunch of codes which I assumed would not matter, and for this case no information from within the workbook is being used.
To better explain the problem, this is one of those cases when a variable (which in this case is the filename) is compared to an array, and the correspoding value from another array has to be used to replace this variable. Here is the code I have attempted to use:
My code is broken, I am sure - but due to my lack of knowledge of VBA, I simply cannot seem to elaborate a logic for this to work. Can someone help me out on this?
The variable in question is a String from within the Macro, and it should be easy. I was following some tips from this post, but that does not seem to be working in my case, since I have changed a bunch of codes which I assumed would not matter, and for this case no information from within the workbook is being used.
To better explain the problem, this is one of those cases when a variable (which in this case is the filename) is compared to an array, and the correspoding value from another array has to be used to replace this variable. Here is the code I have attempted to use:
VBA Code:
Sub NameChange()
Dim SourceFile As String
Dim Original_Name As Variant
Dim Final_Name As Variant
Dim i As Long
SourceFile = Application.ActiveWorkbook.Name
Original_Name = Array("Apples", "Oranges", "Bananas")
Final_Name = Array("Red", "Orange", "Yellow")
For i = 0 To UBound(Original_Name)
Set SourceFile = .Find(Final_Name(i), , , xlWhole, , , False, , False)
Next
MsgBox "File is " & SourceFile
End Sub
My code is broken, I am sure - but due to my lack of knowledge of VBA, I simply cannot seem to elaborate a logic for this to work. Can someone help me out on this?