Code to replace specific text
 

Hi

I'm wondering if anyone could advise me on this.

Within a worksheet detailing business credit card purchases, I have 2 columns which list a description of the purchase (col G) and the name of the vendor (col H)

I'm currently working on a macro that sorts the worksheet by various criteria, and I want to include in the same macro an element of tidying the data so that, if the 'description' cell says 'No Description' (this is from the credit card source data), I'd like to replace it with the content of the corresponding vendor cell:

Col G Col H
Laptop PC Store
No Description PC Store
Keyboard Amazon

becomes

Col G Col H
Laptop PC Store
PC Store PC Store
Keyboard Amazon

And I'm completely stumped on how to do it.

Any advice would be greatly appreciated.

Thanks

Steve

Code to replace specific text
 

Hi Steve,

Am Fri, 17 Nov 2017 07:26:27 -0800 (PST) schrieb
:

I'm currently working on a macro that sorts the worksheet by various criteria, and I want to include in the same macro an element of tidying the data so that, if the 'description' cell says 'No Description' (this is from the credit card source data), I'd like to replace it with the content of the corresponding vendor cell:

Col G Col H
Laptop PC Store
No Description PC Store
Keyboard Amazon

becomes

Col G Col H
Laptop PC Store
PC Store PC Store
Keyboard Amazon

try:

Sub ReplaceText()
Dim LRow As Long, i As Long
Dim varData As Variant

With ActiveSheet
LRow = .Cells(.Rows.Count, "G").End(xlUp).Row
varData = .Range("G1:H" & LRow)
For i = LBound(varData) To UBound(varData)
If varData(i, 1) = "No Description" Then
varData(i, 1) = varData(i, 2)
End If
Next
.Range("G1").Resize(UBound(varData), 2) = varData
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Code to replace specific text
 

Hi Claus

Thanks very much for your reply. I have just tried it after making a couple of minor tweaks (we've inserted a couple if new columns and so it's cols I and J now, but obviously I amended the corresponding parts of your code - perhaps incorrectly).

I think I see what it's looking to do, but it's removing the top line and moving everything else up one row...

Thank you

Steve

Code to replace specific text
 

Hi Steve,

Am Fri, 17 Nov 2017 08:30:05 -0800 (PST) schrieb
:

Thanks very much for your reply. I have just tried it after making a couple of minor tweaks (we've inserted a couple if new columns and so it's cols I and J now, but obviously I amended the corresponding parts of your code - perhaps incorrectly).

I think I see what it's looking to do, but it's removing the top line and moving everything else up one row...

I think you have headers in your table. Then try:

Sub ReplaceText()
Dim LRow As Long, i As Long
Dim varData As Variant

With ActiveSheet
LRow = .Cells(.Rows.Count, "I").End(xlUp).Row
varData = .Range("I2:J" & LRow)
For i = LBound(varData) To UBound(varData)
If varData(i, 1) = "No Description" Then
varData(i, 1) = varData(i, 2)
End If
Next
.Range("I2").Resize(UBound(varData), 2) = varData
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Code to replace specific text
 

Claus,
Why not a simple Find/Replace on a user-selected range? This would only replace
the Find text with the desired text and make no other changes to the data
structure!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Code to replace specific text
 

After serious thinking wrote :
Hi

I'm wondering if anyone could advise me on this.

Within a worksheet detailing business credit card purchases, I have 2 columns
which list a description of the purchase (col G) and the name of the vendor
(col H)

I'm currently working on a macro that sorts the worksheet by various
criteria, and I want to include in the same macro an element of tidying the
data so that, if the 'description' cell says 'No Description' (this is from
the credit card source data), I'd like to replace it with the content of the
corresponding vendor cell:

Col G Col H
Laptop PC Store
No Description PC Store
Keyboard Amazon

becomes

Col G Col H
Laptop PC Store
PC Store PC Store
Keyboard Amazon

And I'm completely stumped on how to do it.

======================================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

If Not IsEmpty(Target) And Target(, 0) = "No Description" Then
Target(, 0) = Target
End If

End Sub
=======================================

DoubleClick a non-blank cell to the right of "No Description".

Bruno