Move to next cell

Can anyone tell me the right code to search for a cell containing certian text then move to the cell to the right of that and then delete it?

I was putting together different things I found on this website. Im not sure this part to find the cell is even right.

Set myrange = Worksheets("Celox Detail New").Range("C1:D200")

If rcell.Value = "Total Closed Strategies" Then
(this is where I'd put the commands to move over to the right one cell then delete that cell)

My problem is the Row for it changes daily. Thats why Im trying this way :)

:( Im gonna pull my hair out! Any help would be great!


-Chrissy

How about you give a name to the cell to the right of the cell that contains "Total Closed Strategies". That way you don't have to search for it and you can remove it's contents with the range object's Clearcontents method. Or, if you really do actually need to delete the cell then give the name to the cell that contains "Total Closed Strategies" and just delete the cell offset one column to the right...;)

there are a few empty columns to the right of "Total Closed Strategies" cell. I do alot of formating to a spreadsheet that gets exported from a web based program . I have a perfect macro to clean it all up but for this one step.

If the "Total Closed Strategies" cell changes its row number daily I have to do the search function.

I was looking into the Active cell idea. If I do a search function for cell containing Total Closed Strategies, then made it the active cell, then I could do a command to move over to the right one and delete.

This is only the 2nd day I ever opened VB.I got no clue! lol sigh~

And I do have to delete not clear contents. im trying to make some numbers further down the row shift over :)

there are a few empty columns to the right of "Total Closed Strategies" cell. I do alot of formating to a spreadsheet that gets exported from a web based program . I have a perfect macro to clean it all up but for this one step.

If the "Total Closed Strategies" cell changes its row number daily I have to do the search function.


Okay... slightly different situation to the one I had imagined. I didn't realise you were importing data each time.

There is only one cell with this text in it and that cell is in the C column, right?
I'm not sure if you're trying to shift other cells up or left so adapt as you need to and use the VBA helpfiles for any further information you may require.

Sub example()
Dim rngToFind As Range

Set rngToFind = Worksheets("Celox Detail New").Range("C:C").Find( _
What:="Total Closed Strategies")

If Not rngToFind Is Nothing Then
rngToFind.Offset(0, 1).Delete shift:=xlShiftUp 'or xlShiftToLeft
End If
End Sub


*untested

This looks great! Thank you so much!

I added this into my pre-existing Macro. The Red is what you wrote.

When I tried to run the macro i get a debug error. Its yelling at me a Compile error. "Expected End Sub". How do I fix that? I read the help file but this is all greek to me :p

Cells.Select
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Sub example()
Dim rngToFind As Range

Set rngToFind = Worksheets("Celox Detail New").Range("C:C").Find( _
What:="Total Closed Strategies")

If Not rngToFind Is Nothing Then
rngToFind.Offset(0, 1).Delete shift:=xlShiftLeft
End If

Range("B2").Select
Selection.Cut
Range("C2").Select
ActiveSheet.Paste
Range("C2:AO2").Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

I feel like such a noobie! grr!

Delete this line (totally remove it from your code):

Sub example()


And amend this line:

rngToFind.Offset(0, 1).Delete shift:=xlShiftLeft

to this:

rngToFind.Offset(0, 1).Delete shift:=xlShiftToLeft

yaaaaaaaaaaaaaaaaa it works!!!!!!!!!! Thank you so very much!!! :D :D

I did a few repeats on it and now my spreadsheet macro is pefect! Took 10 mins of formating for each spreadsheet and now it all takes me less than a minute!

This was my final result on it! Now my Totals line up with the proper columns! Maybe I can get outta work on time at night now :) Thanks again very very much!

Dim rngToFind As Range

Set rngToFind = Worksheets("Celox Detail New").Range("C:C").Find( _
What:="Total Closed Strategies")

If Not rngToFind Is Nothing Then
rngToFind.Offset(0, 1).Delete shift:=xlShiftToLeft
End If
If Not rngToFind Is Nothing Then
rngToFind.Offset(0, 2).Delete shift:=xlShiftToLeft
End If
If Not rngToFind Is Nothing Then
rngToFind.Offset(0, 3).Delete shift:=xlShiftToLeft
End If
If Not rngToFind Is Nothing Then
rngToFind.Offset(2, 1).Delete shift:=xlShiftToLeft
End If
If Not rngToFind Is Nothing Then
rngToFind.Offset(2, 2).Delete shift:=xlShiftToLeft
End If
If Not rngToFind Is Nothing Then
rngToFind.Offset(2, 3).Delete shift:=xlShiftToLeft
End If

Well done cromei... I'm glad you got there!! Getting out of work on time is definitely a bonus! :)

If you decide you want to learn VBA in more detail then let us know.... perhaps we could help you to tidy up your procedure a bit.

Regards
Colin

Im stuck w/ one more problem! I cant have a preset "set print area" since the rows change.

I made the macro command to active the cell for the bottom right corner of the spreadsheet.

How can I make it set print from that cell all the way up and over to cell A1?

I was playing around with something like this

Worksheets("Celox Detail New").PageSetup.PrintArea = ("ActiveCell:A1")


But this didnt work!

I work at Bank of America and Im sure there are techies I can find to do this kinda stuff. But they hired me as a contractor to do this all by hand. I'm just trying to automate this to make my life easier! haha!

Maybe this (without testing...) ?


Worksheets("Celox Detail New").PageSetup.PrintArea = Range(Cells("A1"), Cells(ActiveCell.Address))

mmhm another day of work. fun fun!

Thanks for the code but it gave me a runtime error 13 mismatch. Visual Basics help file is my new friend :P

Worksheets("Celox Detail New").PageSetup.PrintArea = Range("A1", ActiveCell.Address).Address

yaaaa!!!!!! It worked!!! Thank you so so much!!!!

:D :D :D :D :D