ubh8n2
09-05-2011, 07:54 AM
All,
I have been trying to get this vba to work all weekend that I found online and I need help. When I fill the the "User defined" values in the variables tab and I click the replace button it is suppose to change these values in the refence tab.
This is the Code:
Public Sub ReplaceValues(OriginalSheetName As String, VariableSheetName As String, NewSheetName As String)
Dim iVariableRowCounter As String
Dim sSearchValue As String
Dim sReplacementValue As String
Dim iControlCounter As Integer
ThisWorkbook.Sheets(OriginalSheetName).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = NewSheetName
For iControlCounter = ThisWorkbook.Sheets(NewSheetName).Shapes.Count To 1 Step -1
If ThisWorkbook.Sheets(NewSheetName).Shapes(iControlCounter).Type = msoOLEControlObject Then
ThisWorkbook.Sheets(NewSheetName).Shapes(iControlCounter).Delete
End If
Next
iVariableRowCounter = 2
While ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 1).Value <> ""
sSearchValue = ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 1).Value
sReplacementValue = ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 2).Value
ThisWorkbook.Sheets(NewSheetName).UsedRange.Replace what:=sSearchValue, replacement:=sReplacementValue, searchorder:=xlByColumns, MatchCase:=False, lookat:=xlPart
iVariableRowCounter = iVariableRowCounter + 1
Wend
End Sub
Public Function GenerateNewWorksheetName(OriginalSheetName As String) As String
Dim sNewSheetName As String
Dim iIncrement As Integer
Dim iSheetCounter As Integer
Dim bGoodName As Boolean
Dim bSheetFound As Boolean
iIncrement = 1
bGoodName = False
While Not bGoodName
sNewSheetName = OriginalSheetName & " - " & iIncrement
bSheetFound = False
For iSheetCounter = 1 To ThisWorkbook.Sheets.Count
If ThisWorkbook.Sheets(iSheetCounter).Name = sNewSheetName Then
bSheetFound = True
Exit For
End If
Next
If Not bSheetFound Then
bGoodName = True
End If
iIncrement = iIncrement + 1
Wend
GenerateNewWorksheetName = sNewSheetName
End Function
I will appreciate any help I can get.
I have been trying to get this vba to work all weekend that I found online and I need help. When I fill the the "User defined" values in the variables tab and I click the replace button it is suppose to change these values in the refence tab.
This is the Code:
Public Sub ReplaceValues(OriginalSheetName As String, VariableSheetName As String, NewSheetName As String)
Dim iVariableRowCounter As String
Dim sSearchValue As String
Dim sReplacementValue As String
Dim iControlCounter As Integer
ThisWorkbook.Sheets(OriginalSheetName).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = NewSheetName
For iControlCounter = ThisWorkbook.Sheets(NewSheetName).Shapes.Count To 1 Step -1
If ThisWorkbook.Sheets(NewSheetName).Shapes(iControlCounter).Type = msoOLEControlObject Then
ThisWorkbook.Sheets(NewSheetName).Shapes(iControlCounter).Delete
End If
Next
iVariableRowCounter = 2
While ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 1).Value <> ""
sSearchValue = ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 1).Value
sReplacementValue = ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 2).Value
ThisWorkbook.Sheets(NewSheetName).UsedRange.Replace what:=sSearchValue, replacement:=sReplacementValue, searchorder:=xlByColumns, MatchCase:=False, lookat:=xlPart
iVariableRowCounter = iVariableRowCounter + 1
Wend
End Sub
Public Function GenerateNewWorksheetName(OriginalSheetName As String) As String
Dim sNewSheetName As String
Dim iIncrement As Integer
Dim iSheetCounter As Integer
Dim bGoodName As Boolean
Dim bSheetFound As Boolean
iIncrement = 1
bGoodName = False
While Not bGoodName
sNewSheetName = OriginalSheetName & " - " & iIncrement
bSheetFound = False
For iSheetCounter = 1 To ThisWorkbook.Sheets.Count
If ThisWorkbook.Sheets(iSheetCounter).Name = sNewSheetName Then
bSheetFound = True
Exit For
End If
Next
If Not bSheetFound Then
bGoodName = True
End If
iIncrement = iIncrement + 1
Wend
GenerateNewWorksheetName = sNewSheetName
End Function
I will appreciate any help I can get.