Search for text and edit neighbouring cell

**graveaj1** · 06-28-2017, 08:12 AM

I've been struggling with this one for a while now, having read several posts on here but can't seem to make it work.

I've got a workbook with several sheets, each of them containing a 'revision number' cell at different locations (e.g. cell X7 contains "Revision:" and cell Y7 the number, e.g. "4"). I'd like to write a macro that searches for the word "Revision:" and adds 1 to the value in the neighbouring cell. So far I've tried the code below, but get the error "Run-time error '13': Type mismatch." What am I doing wrong?

Thanks!

Sub RevNumbers()
    
For Each WS In ActiveWorkbook.Worksheets
    For Each C In WS.Range("A:Z")
        If C.Value = "Revision:" Then
            C.Offset(0, 1).FormulaR1C1 = C.Offset(0, 1).Value + 1
        End If
    Next C
Next WS
    
End Sub

**jindon** · 06-28-2017, 08:20 AM

See if this also errors.

Sub RevNumbers()
    Dim ws As Worksheet, r As Range, ff As String
    For Each ws In ActiveWorkbook.Worksheets
        Set r = ws.Columns("a:z").Find("Revision:", , , 1)
        If Not r Is Nothing Then
            ff = r.Address
            Do
                r(, 2).Value = r(, 2).Value + 1
                Set r = ws.Columns("a:z").FindNext(r)
            Loop While ff <> r.Address
        End If
    Next
End Sub

**graveaj1** · 06-28-2017, 08:23 AM

That works perfectly, thanks so much!!!

**jindon** · 06-28-2017, 08:26 AM

OK 2 things.

1) Edit your first post and add code tags for vba code
e.g.
[code]
vba code here
[/code]

2) Please mark the thread as "SOLVED".

Search for text and edit neighbouring cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Search for text and edit neighbouring cell

Re: Search for text and edit neighbouring cell

Re: Search for text and edit neighbouring cell

Re: Search for text and edit neighbouring cell

Thread Information

Users Browsing this Thread

Similar Threads

Sumproduct of column based on text search of neighbouring column

Auotmatic highlighting of a neighbouring cell

[SOLVED] How to add up cells in a row based on criteria in neighbouring cell

score explanation in the neighbouring cell

macro - search within a cell and edit the text string

transferring comments to a neighbouring cell

Search and Edit cell contents

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions