VBA code for moving cells based on another cells condition

[skelly90]

Hi,


AS the title suggests I want to move cells based on another cells contents (in the same row) I have trawled the internet in search of the answer but I'm unable to find anything, so sorry if it's on a different post somewhere.


Below is an example of my data, I wish to move the EE+ER column to overwrite the ER column if the scheme = 30


A B C D E F G H

Num Name NINO Scheme EE ER EE+ER Leave date

1 Smith AB123 30 50 80 130 N/A

2 Jones AC123 56 25 45 70 N/A


There will be around 200 lines, so I'll need it to loop down for each line, but there will be other lines that won't contain a 30 which don't need to be moved.


Hope this makes sense to somebody.


Thanks

 

[OleMiss2010]

Is the EE+ER column a formula?

 

[skelly90]

No, just a number.

 

[Luke M]

How's this?

[pre]

Sub ChangeData()
Dim xRow As Integer
'Find last row
xRow = Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To xRow
If Cells(i, "D") = 30 Then
Cells(i, "F") = Cells(i, "G").Value
End If
Next i
Application.ScreenUpdating = False
End Sub

[/pre]

 

[Luke M]

Alternatively, if you just want to use a helper column w/ formula:

=IF(D2=30,G2,F2)

 

[OleMiss2010]

This should do the trick. Also, welcome to the Chandoo forums.

[pre]

Sub MoveValue()
Dim i As Integer
Dim x As Integer

x = ActiveSheet.UsedRange.Rows.Count

With ActiveSheet
For i = 2 To x
If .Cells(i, 4).Value = 30 Then
.Cells(i, 6).Value = .Cells(i, 7).Value
End If
Next i
End With

End Sub

[/pre]

 

[skelly90]

Thank You!!


Both work posts work perfectly.


Thanks for the welcome, loving this forum already!