conditionally adding a cell

[newbie099]

Hello!

I'm new to excel macros and trying to set up a macro that runs through a file row by row and inserts a cell before columnF (shifting cells to the left) if the value in column C="forced". It seems like it should be quite simple, but I can't figure it out. Any help would be fantastic. thanks!

-justine

[mudraker]

justine

you can not insert cells and push items to the left or up.

this code will insert a cell into column f (moving cells to the right) if the cell in column c (same row) has a value of forced - please note that the if statement is case sensative
eg forced does not test the same as Forced

Sum InsertCell
Dim Rng As Range
For Each Rng In Column("c")
If Rng.Value = "forced" Then
Cells(Rng.Row, "f").Insert Shift:=xlToRight
End If
Next Rng
End Sub

[Tom Ogilvy]

to the best of my knowledge, there is no support in VBA or manually for
shifting cells to the left unless you are deleting. But you say you are
inserting.

Sub InsertCells()
lastrow = cells(rows.count,3).End(xlup).row
for i = lastrow to 1 step -1
if cells(i,3) = "forced" then
cells(i,"F").Insert Shift:=xlShiftToRight
end if
Next
end Sub

--
Regards,
Tom Ogilvy


"newbie099" wrote:

>
> Hello!
>
> I'm new to excel macros and trying to set up a macro that runs through
> a file row by row and inserts a cell before columnF (shifting cells to
> the left) if the value in column C="forced". It seems like it should
> be quite simple, but I can't figure it out. Any help would be
> fantastic. thanks!
>
> -justine
>
>
> --
> newbie099
> ------------------------------------------------------------------------
> newbie099's Profile: http://www.excelforum.com/member.php...o&userid=33857
> View this thread: http://www.excelforum.com/showthread...hreadid=536362
>
>

[newbie099]

So sorry! I meant to the right!

I tried this, but got an error when it got to "column("C")


Sum InsertCell
Dim Rng As Range
For Each Rng In Column("c")
If Rng.Value = "forced" Then
Cells(Rng.Row, "f").Insert Shift:=xlToRight
End If
Next Rng
End Sub

It says "Compile error: Sub or Function not defined"