Replacing and concatenating the contents of a cell

Asked

Viewed 90 times

0

First I filter the contents of a table column to get everything that does not contain /.

ActiveSheet.Range("A1").AutoFilter Field:=3, Criteria1:="<>*/*"

With this result, I would like to replace the contents of the cells with their own contents concatenated with the string /SUP. For that I use the Selection.Replace:

Selection.Replace What:="???", Replacement:="???/SUP", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

But it’s not working...

The expected result would be:

cellCOM/ --- replace --- cellCOM/
cellSEM  --- replace --- cellSEM/SUP

How can I take the cell content when making this substitution??

There’d be a different or better way to do it??

vba excel-vba replace

  • After filtering, you cannot use the Selection, in fact it is always good to avoid the use of Select. But after the filter you need to use the visible cells to use the function replace

  • @danieltakeshi OK. And if there are no visible cells?? how do I proceed?

  • Creates a conditional to check for visible cells (Range.SpecialCells(xlCellTypeVisible)), if it has treats the data, otherwise it leaves the conditional or provides a warning message.

1 answer

1

I would take a different approach. Given the information, from what I understand, you don’t need to make any filters. Maybe you just want to make a filter after, to see what was changed (and for that you would have to change the filter). So, you just need to loop in and replace "everything that doesn’t contain /", right?

Using the Instr function you find out whether or not there is a "/". If it does not, the return is zero, and it enters the "If" to replace the current value of the cell by itself with the "/SUP"

Sub substituir_por_barraSUP()

Dim lngUltimaLinha As Long
Dim i As Integer

'Existem outras formas encontrar a última linha,
'Nesse casom estou considerando que você tem todos os dados na coluna 1, ou A.
lngUltimaLinha = ActiveWorkbook.ActiveSheet.Range("A1048576").End(xlUp).Row

'Loop começando a partir da segunda linha, considerando que a primeira linha é o cabeçalho
For i = 2 To lngUltimaLinha
    If InStr(Cells(i, 1).Value, "/") = 0 Then
     Cells(i, 1).Value = Cells(i, 1).Value & "/SUP"
    End If
Next i

End Sub

Before:

Header
cellCOM/
cellSEM
cellCOM/
cellSEM
cellCOM/
cellSEM

Afterward:

Header
cellCOM/
cellSEM/SUP
cellCOM/
cellSEM/SUP
cellCOM/
cellSEM/SUP

  • Making a loop would work too. I only opted for the filter and replace method because I thought it would be more optimized than sweeping all cells in the spreadsheet.

Browser other questions tagged vba excel-vba replace

You are not signed in. Login or sign up in order to post.