Hello,
I’ve been using the code below to add text to the beginning of a string in Column C. How do I run it after I filter out anything I don’t want?
I tried replacing;
With
But it gave me strange results.
Any help is appreciated.
Here's an example of the spreadsheet (Results are in Column D)
Excel 2010
I’ve been using the code below to add text to the beginning of a string in Column C. How do I run it after I filter out anything I don’t want?
I tried replacing;
Code:
Range(addr) = Evaluate("IF(" & addr & "="""","""",""" & "Inventory" & """&" & addr & ")")
With
Code:
Range(addr).SpecialCells(xlCellTypeVisible) = Evaluate("IF(" & addr & "="""",""""," & TextToPrefix & """&" & addr & ")")
But it gave me strange results.
Any help is appreciated.
Code:
Sub AddToBeginningOfText()
Dim lastRow As Long, addr As String
Const TextToPrefix As String = "Inventory - "
Const ColumnToProcess As String = "C"
Const Startrow As Long = 2
'Filter out unwated text
'Range("A2").AutoFilter Field:=3, Criteria1:="=*(Int)*", Operator:=xlAnd
lastRow = Cells(Rows.Count, ColumnToProcess).End(xlUp).Row
addr = Cells(Startrow, ColumnToProcess).Resize(lastRow - Startrow + 1).Address
'adds to begining of string
Range(addr) = Evaluate("IF(" & addr & "="""","""",""" & "Inventory" & """&" & addr & ")")
End Sub
Here's an example of the spreadsheet (Results are in Column D)
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Date | Reason | Results should be: | |||
2 | 1/4/2017 | 206 (Int) | Inventory - 206 (Int) | |||
3 | 1/5/2017 | 206 (Int) | Inventory - 206 (Int) | |||
4 | 1/4/2017 | 206 (Int) | Inventory - 206 (Int) | |||
5 | 1/5/2017 | 206 (Int) | Inventory - 206 (Int) | |||
6 | 1/5/2017 | Vacation | Vacation | |||
7 | 1/6/2017 | Vacation | Vacation | |||
8 | 1/8/2017 | Vacation | Vacation | |||
9 | 1/4/2017 | 208 (Int) | Inventory - 208 (Int) | |||
10 | 1/5/2017 | 208 (Int) | Inventory - 208 (Int) | |||
11 | 1/4/2017 | 208 (Int) | Inventory - 208 (Int) | |||
12 | 1/5/2017 | 208 (Int) | Inventory - 208 (Int) | |||
13 | 1/4/2017 | 225 (Int) | Inventory - 225 (Int) | |||
14 | 1/5/2017 | Vacation | Vacation | |||
15 | 1/6/2017 | Vacation | Vacation | |||
16 | 1/8/2017 | Vacation | Vacation | |||
17 | 1/5/2017 | 225 (Int) | Inventory - 225 (Int) | |||
18 | 1/4/2017 | 225 (Int) | Inventory - 225 (Int) | |||
19 | 1/5/2017 | 225 (Int) | Inventory - 225 (Int) | |||
20 | 1/4/2017 | 198 (Int) | Inventory - 198 (Int) | |||
21 | 1/5/2017 | 198 (Int) | Inventory - 198 (Int) | |||
22 | 1/4/2017 | 198 (Int) | Inventory - 198 (Int) | |||
23 | 1/5/2017 | 198 (Int) | Inventory - 198 (Int) | |||
Sheet2 |