Edit each cells but only unique one using vba

haroon2015 · Dec 7th 2016

hi, everyone
i am using below code to edit every cell in range.("C10:C300"),
but
now its checking every cell and edit him,
but
i want it edit only unique data,
for example -

cell 10 have data = haroon, then edit it
cell 11 have data = alan, then edit it
cell 12 have data = haroon, the skip it

Code

Sub Haroon2015_RangeEditing()


If WorksheetFunction.CountA(Range("C10:C300")) = 0 Then Exit Sub




Range("C10:C300").SpecialCells(xlCellTypeConstants).Select
Dim rngCell As Range
Dim varAns




For Each rngCell In Selection
  varAns = InputBox(prompt:="Edit cell if wanted", Title:="Edit cell ", Default:=rngCell.Value)
  If Not varAns = "" Then rngCell.Value = varAns
Next rngCell
Range("C10").Select




End Sub

Display More

thanx in advance

**KjBox** · Dec 7th 2016

Re: Edit each cells but only unique one using vba

So all further instances of "haroon" need to be left as "haroon" or changed to whatever the first instance was changed to?

**KjBox** · Dec 7th 2016

Re: Edit each cells but only unique one using vba

If just first instance to be changed then try this

Code

Sub Haroon2015_RangeEditing()
    Dim rCell As Range, vAns As Variant
    ReDim x(1 To 1)
    
    On Error GoTo ErrHndlr 'check there is at least one cell in c10:c300 with a value
    [c10:c300].SpecialCells(2).Select
    For Each rCell In Selection
        If IsError(Application.Match(rCell, x, 0)) Then
            vAns = InputBox("Edit cell if wanted", "Edit cell ", rCell)
            ReDim Preserve x(1 To UBound(x) + 1)
            x(UBound(x)) = rCell
            If Not vAns = "" Then rCell = vAns
        End If
    Next
ErrHndlr:


End Sub

Display More

If all instances to be changed then try this

Code

Sub Haroon2015_RangeEditing2()
    Dim rCell As Range, vAns As Variant, i As Integer
    ReDim x(1 To 1): ReDim y(1 To 1)
    
    On Error GoTo ErrHndlr 'check there is at least one cell in c10:c300 with a value
    [c10:c300].SpecialCells(2).Select
    For Each rCell In Selection
        If IsError(Application.Match(rCell, x, 0)) Then
            vAns = InputBox("Edit cell if wanted", "Edit cell ", rCell)
            ReDim Preserve x(1 To UBound(x) + 1)
            x(UBound(x)) = rCell
            If vAns <> "" Then
                rCell = vAns
                ReDim Preserve y(1 To UBound(y) + 1)
                y(UBound(y)) = vAns
            Else
                ReDim Preserve y(1 To UBound(y) + 1)
                y(UBound(y)) = rCell
            End If
        Else
            rCell = y(Application.Match(rCell, x, 0))
        End If
    Next
    Application.Goto [c10]
ErrHndlr:


End Sub

Display More

haroon2015 · Dec 7th 2016

Re: Edit each cells but only unique one using vba

Thanx a lot,
Mod
you saved my lots of time....

haroon2015 · Dec 7th 2016

Re: Edit each cells but only unique one using vba

mod,
i have a another question to related this thread,

is it possible to edit unique range without inputbox?
i mean when i run the code it edit unique cell without showing inputbox

or should i start a new thread?

**KjBox** · Dec 7th 2016

Re: Edit each cells but only unique one using vba

It could be done if every cell needed to be edited the same.

:-) · Dec 7th 2016

Re: Edit each cells but only unique one using vba

The original question also posted on ExcelForum, no links in either forum.
Can't see the supplementary question yet. Give it time

haroon2015 · Dec 7th 2016

Re: Edit each cells but only unique one using vba

Ok mod
plz do it

**KjBox** · Dec 7th 2016

Re: Edit each cells but only unique one using vba

How do you need the cells edited?

Also which code are you using, the one that changes just the first instance of a particular value or the one that changes all instances of that value?

haroon2015 · Dec 7th 2016

Re: Edit each cells but only unique one using vba

Sub Haroon2015_RangeEditing()
Dim rCell As Range, vAns As Variant
Redim x(1 To 1)

On Error Goto ErrHndlr 'check there is at least one cell in c10:c300 with a value
[c10:c300].SpecialCells(2).Select
For Each rCell In Selection
If IsError(Application.Match(rCell, x, 0)) Then
vAns = InputBox("Edit cell if wanted", "Edit cell ", rCell)
Redim Preserve x(1 To UBound(x) + 1)
x(UBound(x)) = rCell
If Not vAns = "" Then rCell = vAns
End If
Next
ErrHndlr:

End Sub

I AM USING THIS CODE

**KjBox** · Dec 7th 2016

Re: Edit each cells but only unique one using vba

OK, but how do you want each unique value to be edited?

haroon2015 · Dec 7th 2016

Re: Edit each cells but only unique one using vba

one by one c10 to last added data

**KjBox** · Dec 7th 2016

Re: Edit each cells but only unique one using vba

Quote from haroon2015;781948

one by one c10 to last added data

I really do not understand what you mean by that. What is "last added data?

Can you please attach a sample workbook that shows a sample of your data in Column C and your desired result.
[sw]*[/sw]

haroon2015 · Dec 8th 2016

Re: Edit each cells but only unique one using vba

Hi, kjbox
as u know my range is c10:c300, ok

which cells i want to added using macro?

Quote

one by one, c10 to last added data

in above under quote line means
i add data to c10, then c11, then 12, then 13..........to 30
now code only added cells c10 to c30

if i have data in range c10:c50
then edit only c10 to c50

let me know if you want to know more about my requirement

**KjBox** · Dec 8th 2016

Re: Edit each cells but only unique one using vba

Yes I understand all that already.

You said you wanted to do away with the Input Box and I explained that to do so was possible if the same editing was to be used on each unique value.

I need to know what that editing is like.

So, from your example in the opening post, what does "haroon" need to be edited to? What does "alan" need to be edited to?

haroon2015 · Dec 8th 2016

Re: Edit each cells but only unique one using vba

Because i need to edit a cell

it activate a another macro ( Private Sub Worksheet_Change(ByVal Target As Range) )

edit means just like u select a cell and just press f2

haroon2015 · Dec 8th 2016

Re: Edit each cells but only unique one using vba

leave this thread,

i am happy with inputbox code,

thanx for your great help

**KjBox** · Dec 8th 2016

Re: Edit each cells but only unique one using vba

You're welcome.

Edit each cells but only unique one using vba

Participate now!

Share