Find a specific String in a range and copy it to another field in VBA

Question

So I want to find a specific string in a range, get its address and copy its value into a new cell, which depends on the old address

Here is what I've tried until now:

Dim c As Range

For Each c In Range("F1:F1500")
    If InStr(1, c.Text, "Overall Result") Then
        Range(c).Select
        Selection.Cut
        Range(newAddress).Select
        ActiveSheet.Paste
    End If
Next c

But I'm not sure about how to get the right address of the cell. The new cell should be the old address.row and the old address.column+1

score 3 · Accepted Answer · answered Jul 24 '18 at 12:29

You can use c.Offset(ColumnOffset:=1) to move one column right outgoing from the range c.

Dim c As Range

For Each c In Range("F1:F1500")
    If InStr(1, c.Text, "Overall Result") Then
        c.Cut Destination:=c.Offset(ColumnOffset:=1)
    End If
Next c

Also have a look at How to avoid using Select in Excel VBA which is a bad practice, slows you down and is not very reliable.

score 1 · Answer 2 · answered Jul 24 '18 at 12:34

1

Dim c As Range

For Each c In Range("F1:F1500")
    If InStr(1, c.Text, "Overall Result") Then
        Range(c.address).offset(0,1).value = c.text
    End If
Next c

answered Jul 24 '18 at 12:34

Ahmed Hassan

100
2
7

1

`Range(c.address)` Making a address out of a range to make a range out of that address seems to be a bit cumbersome if you can use the range directly. – Pᴇʜ Jul 24 '18 at 12:40

Find a specific String in a range and copy it to another field in VBA

2 Answers2