Hi I am new to VBA, and I am still learning, I have this sheet that sometimes have a typo or spacing issue.
So what I do is Find and replace multiple via array. However what I wanted to do is Set up a Find array and remove the array that will replace if it does not match, instead the user will assign replacement value for it.
Here is my sample code.
I want to remove the rplc list instead I want to put an input box so that the user itself will replace the value that is not match to the ff array.
So what I do is Find and replace multiple via array. However what I wanted to do is Set up a Find array and remove the array that will replace if it does not match, instead the user will assign replacement value for it.
Here is my sample code.
VBA Code:
Sub upload_data()
Dim WScopy As Worksheet
Dim WSdest As Worksheet
Dim desWB As Workbook
Dim FileToOpen As Variant
Dim cRow As Long
Dim Stat As Range
Set desWB = ThisWorkbook
Set WSdest = desWB.Sheets("AYE")
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen = False Then Exit Sub
Set OpenBook = Application.Workbooks.Open(FileToOpen)
With Sheets(1)
cRow = .Cells(Rows.Count, "A").End(xlUp).row
.Range("A8:AA" & cRow).Copy
WSdest.Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
End With
fndList = Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")
rplcList = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
WSdest.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End If
ActiveWorkbook.Close False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I want to remove the rplc list instead I want to put an input box so that the user itself will replace the value that is not match to the ff array.