IF column C contains value, then populate column G

[hutch27]

Hi all -

As the title reads, if column C contains certain text, then populate column G with specific text. Additionally, move the newly created text in column G up 1 cell. I'm getting the code to populate column G, however, i'm having trouble with the offset portion of the code.

Here is what I have so far:

Sub info()
Dim i As Long
For i = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If ActiveSheet.Cells(i, 3) = "Bank Account" Then
ActiveSheet.Range("G" & i) = "'Result"
End If
Next i
End Sub


Sub tested()


Dim rng As Range
Dim lrow As Integer


With ActiveSheet
lrow = .Range("G" & Rows.Count).End(xlUp).Row


For Each rng In .Range("G:G" & lrow)


If InStr(rng.Value, "'Result") > 0 Then


rng.Offset(1, 0).Value = rng.Value
rng.Value = ""
End If


Next rng


End With


End Sub

 

[Joe4]

If you are talking about this line:

[COLOR=#333333]rng.Offset(1, 0).Value = rng.Value[/COLOR]

that is offsetting one row down.

To offset one row up, use:

[COLOR=#333333]rng.Offset([/COLOR][COLOR=#ff0000][B]-1[/B][/COLOR][COLOR=#333333], 0).Value = rng.Value[/COLOR]

 

[hutch27]

Got it - thank you.

The first portion of the code is running fine, however, the second portion (moving the cell 1 up) is getting the Application defined or object defined error. Any insight on where I went wrong?

 

[Joe4]

You are missing your starting row number in this line:

[COLOR=#333333]For Each rng In .Range([/COLOR][COLOR=#ff0000][B]"G:G" & lrow[/B][/COLOR][COLOR=#333333])[/COLOR]

You want to make it something like:
"G1:G" & lrow
or
"G2:G" & lrow

Note that is you use G1, you may need to check if you are on row 1 for your Offset line, as you cannot move up to G0, as that does not exist!