Find and Replace when needed Code

**ulothar** · 02-09-2017, 05:11 PM

Hello guys, at my project (I send a sample file to see it) I have 2 sheets (sheet 1 and sheet 2) . At the sheet 1 there are 2 headings "Rank" and "Name" and below those headings in cells B4 and C4 you can write the Ranking and the Name of a person (for example Rank 1 , Name George). Then there is a button that is titled "Add". When you press it, the data goes to Sheet2 in two columns (A:B) where column A is the Rank and Column B is the Name. The code I used for the button is :

Sub Button1_Click()
Dim Rank As String, Name As String
Worksheets("Sheet1").Select
Rank = Range("B4")
Name = Range("C4")
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("A1").Select
If Worksheets("Sheet2").Range("A1").Offset(1, 0) <> "" Then
Worksheets("Sheet2").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Rank
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Name
Worksheets("Sheet1").Select
Range("B4").ClearContents
Range("C4").ClearContents
End Sub

Now what I need is that in the same code (when you press the "Add" button) to have a code that will search the range A1:B50 and if it finds a previous entry (for example Rank 1, Name George) it will delete the content of those two cells and will place in that cells (that have been cleared) the new entries for example

Old data

1 George
1 Peter
1 Michael
2 John

New data

3 Peter

Should be Now

1 George
3 Peter
1 Michael
2 John

Thanks guys for your time

**Keebellah** · 02-09-2017, 05:36 PM

Hi,
Is there any special reason why you do not attach the file WITH the macro?

**ulothar** · 02-09-2017, 05:43 PM

hmm the file should have the macro let me check, I am at my laptop now and using different excel maybe it's some setting there. Let me check it

**ulothar** · 02-09-2017, 05:49 PM

Yeah sorry for that, it was a mistake I saved it as a normal book and not with activated macros

I upload it again.

**JBeaucaire** · 02-09-2017, 05:44 PM

This is how I would write that:

Option Explicit

Sub Button1_Click()
Dim Rank As String, Name As String, NmFIND As Range

Rank = ThisWorkbook.Sheets("Sheet1").Range("B4").Value
Name = ThisWorkbook.Sheets("Sheet1").Range("C4").Value

If Rank = "" Or Name = "" Then Exit Sub

With ThisWorkbook.Sheets("Sheet2")
    On Error Resume Next
    Set NmFIND = .Range("B:B").Find(Name, LookIn:=xlValues, LookAt:=xlWhole)
    If Not NmFIND Is Nothing Then
        NmFIND.Offset(, -1).Value = Rank
    Else
        With .Range("A" & .Rows.Count).End(xlUp).Offset(1)
            .Value = Rank
            .Offset(, 1).Value = Name
        End With
    End If
End With

ThisWorkbook.Sheets("Sheet1").Range("B4:C4").ClearContents

End Sub

**Keebellah** · 02-09-2017, 05:47 PM

See attached file returned, I see that my idea is the same

**Keebellah** · 02-09-2017, 05:49 PM

xlsx file is non-macro embedded file, but no problem. You got your answers

**ulothar** · 02-09-2017, 05:55 PM

Nice it works great!!

thanks both of you

rep added

**ulothar** · 02-09-2017, 08:09 PM

hm I did copy the code to my project but for some reason I get the error 1004 when I push the button :/ can you please check it? at the test file it worked perfectly .

The tabs we are interrested in are "talents" and "Charactersheet"

Thanks guys.

Find and Replace when needed Code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find and Replace when needed Code

Re: Find and Replace when needed Code

Re: Find and Replace when needed Code

Re: Find and Replace when needed Code

Re: Find and Replace when needed Code

Re: Find and Replace when needed Code

Re: Find and Replace when needed Code

Re: Find and Replace when needed Code

Re: Find and Replace when needed Code

Thread Information

Users Browsing this Thread

Similar Threads

Find & Replace Loop Help Needed

[SOLVED] Formula to find and replace where needed

Help Needed with Find and Replace Macro

VBA Find and replace loop help needed

[SOLVED] VBA needed to find and replace text using a wildcard

FIND and REPLACE characters needed

[SOLVED] FIND and REPLACE characters needed

Bookmarks

Bookmarks

Posting Permissions