Search code examples
excelvbadatatableinputbox

Replace values ​within a column of a table using Table Name and Column name in VBA

I have the next table

header1 header2 header3
v x v
x v x
x x
v v v
x v x

I want to replace "x" for another value for each column, for example "true" for 1st column, "false" for 2nd column and "else" for 3rd column. Finally, this would be the result:

header1 header2 header3
v false v
true v else
false else
v v v
true v else

But the case is that I have to specify the sheet name and the table name (all tables have the same column names). So I came up with something like this, but obviously the code is wrong

Sub Macro1()
'
'
' 

Dim sheet_name As String
Dim table_name As String

sheet_name = InputBox("Sheet Name?", "enter the data")
table_name = InputBox("Table Name?", "enter the data")

With Worksheet(sheet_name).ListObjects(table_name)

.Range("[header1]").Select
Selection.Replace What:="x", Replacement:="true", LookAt:=xlPart _
    , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

.Range("[header2]").Select
Selection.Replace What:="x", Replacement:="false", LookAt:=xlPart _
    , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

.Range("[header3]").Select
Selection.Replace What:="x", Replacement:="else", LookAt:=xlPart _
    , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End With
End Sub

If anyone has any better ideas or ways to correct this code, it would be very helpful.

Solution

  • You can use this code.

    I like to have kind of configuration for such settings - it's much easier to make changes, e.g. if you want to change the replace-values.

    regarding replacement for true/false: you need to put a ' in front of the value while replacing - otherwise Excel turns it into TRUE FALSE (a value, not a string)

    Public Sub replaceXValues()
Dim sheet_name As String
Dim table_name As String

sheet_name = InputBox("Sheet Name?", "enter the data")
table_name = InputBox("Table Name?", "enter the data")

Dim lo As ListObject
Set lo = ThisWorkbook.Worksheets(sheet_name).ListObjects(table_name)

Set lo = ActiveSheet.ListObjects(1)


Dim strFind As String
strFind = "x"

Dim arrReplace(1 To 3) As String
arrReplace(1) = "true"
arrReplace(2) = "false"
arrReplace(3) = "else"


Dim lc As ListColumn, i As Long
For i = 1 To UBound(arrReplace)
    Set lc = lo.ListColumns(i)
    lc.DataBodyRange.replace strFind, "'" & arrReplace(i), LookAt:=xlWhole
Next

End Sub

    Obviously you would need an error handler in case sheet- or table-name do not exist.