Imported csv causes my headings to be off on the rows as well as the columns.
As part of my code attempting to realign the column headings, I have the
following code to search column B for any instance of a certain word and copy
it to column B of the previous row. I now have 2 rows consecutively that
have that word in it. I need to be able to empty every other instance of the
word beginning at row 3. Is there a way to copy then empty...search again
and copy then empty or does it make more sense to copy all instances and loop
through again and empty every other instance?

Sub MoveGoals()
Dim Cell As Range
For Each Cell In Range("B:B")
If Cell.Value = "GOALS" Then
Cell.Offset(-1, 0) = "GOALS"
End If
Next Cell
End Sub

If I were doing it, I would empty as I go. Assuming you really intend to move
the found data up one row:

Sub MoveGoals()
Dim Cell As Range
For Each Cell In Range("B:B")
If UCase(Cell.Value) = "GOALS" Then
Cell.Cut Cell.Offset(-1, 0)
End If
Next Cell
Application.CutCopyMode = False
End Sub

You might want to run this on a test sheet first.



"jeremiah" wrote:

> Imported csv causes my headings to be off on the rows as well as the columns.
> As part of my code attempting to realign the column headings, I have the
> following code to search column B for any instance of a certain word and copy
> it to column B of the previous row. I now have 2 rows consecutively that
> have that word in it. I need to be able to empty every other instance of the
> word beginning at row 3. Is there a way to copy then empty...search again
> and copy then empty or does it make more sense to copy all instances and loop
> through again and empty every other instance?
>
> Sub MoveGoals()
> Dim Cell As Range
> For Each Cell In Range("B:B")
> If Cell.Value = "GOALS" Then
> Cell.Offset(-1, 0) = "GOALS"
> End If
> Next Cell
> End Sub

Exactly. Thanks again, it works perfectly.

"JLGWhiz" wrote:

> If I were doing it, I would empty as I go. Assuming you really intend to move
> the found data up one row:
>
> Sub MoveGoals()
> Dim Cell As Range
> For Each Cell In Range("B:B")
> If UCase(Cell.Value) = "GOALS" Then
> Cell.Cut Cell.Offset(-1, 0)
> End If
> Next Cell
> Application.CutCopyMode = False
> End Sub
>
> You might want to run this on a test sheet first.
>
>
>
> "jeremiah" wrote:
>
> > Imported csv causes my headings to be off on the rows as well as the columns.
> > As part of my code attempting to realign the column headings, I have the
> > following code to search column B for any instance of a certain word and copy
> > it to column B of the previous row. I now have 2 rows consecutively that
> > have that word in it. I need to be able to empty every other instance of the
> > word beginning at row 3. Is there a way to copy then empty...search again
> > and copy then empty or does it make more sense to copy all instances and loop
> > through again and empty every other instance?
> >
> > Sub MoveGoals()
> > Dim Cell As Range
> > For Each Cell In Range("B:B")
> > If Cell.Value = "GOALS" Then
> > Cell.Offset(-1, 0) = "GOALS"
> > End If
> > Next Cell
> > End Sub