Noob question, changing selected cell....Please help!!

[TCollin6]

How do I have VB select the cell 3 down from the active cell for Excel? I can't figure out the code!!!

Thanks in advance for your help!!

Tim

 

[Norie]

Does this work?

Set rng = ActiveCell.Offset(3,0)

 

[Zack Barresse]

Hi Tim,

I imagine that you have some code you are building and need this for a specific reason. It is generally not a good idea to use the Activecell, or use Select for anything - which the Macro Recorder will give you tons of that type of code. It is quite inefficient. Try posting your entire code for a possible better solution. Don't forget to include a good written description of what it is that you are trying to accomplish along with the desired results.

 

[TCollin6]

Here's my full code below. Basically what I am doing is having the macro find a specific font etc, then move the data in that cell down and to the left one cell (already done). The thing is, I need to repeat that task on average bout 150 times, so I am wanting to build the code so it will select a cell below the one that I just moved to avoid moving it again. Hope that's a little clearer!!

Ohh, I couldn't get the first string of code to work.

Sub MoveSupervisor()
'
' MoveSupervisor Macro
' Macro recorded 1/26/2005 by TCollin6
'

'
Range("C4").Select
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Application.FindFormat.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 9.75
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 10
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlTop).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
Application.FindFormat.Interior.ColorIndex = xlNone
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Selection.Cut Destination:=Selection.Offset(1, -1)
End Sub

 

[Zack Barresse]

That's quite a bit of Format criteria. Any way you could narrow down what it is you are looking for?

 

[TCollin6]

I have a list of supervisor and their employees all in one column (raw data given to me like this). the only thing that identifies the supervisors is that they are in bold and green. I've got that piece figured out on how to search and find them, the only problem is when I repeat the macro, it moves the same name unless a cell below it has been selected before the macro is run again. So that what I really need to make sure happens. I have to run this macro many many times (i basically set-up a shortcut key and hold id down for a couple minutes).

 

[Zack Barresse]

And what range will you be running it on? Only one sheet? Only one workbook?

 

[TCollin6]

One sheet, about 20,000 lines

 

[Zack Barresse]

Possibly ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> MoveSupervisor()
    <SPAN style="color:#00007F">Dim</SPAN> lastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rngSearch <SPAN style="color:#00007F">As</SPAN> Range, cel <SPAN style="color:#00007F">As</SPAN> Range
    lastRow = Sheets("Sheet1").Range("C65536").End(xlUp).Row
    <SPAN style="color:#00007F">Set</SPAN> rngSearch = Sheets("Sheet1").Range("C4:C" & lastRow)
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cel <SPAN style="color:#00007F">In</SPAN> rngSearch
        <SPAN style="color:#00007F">With</SPAN> cel
            <SPAN style="color:#00007F">If</SPAN> .Font.Name = "Arial" And _
                .Font.FontStyle = "Bold" And _
                .Font.ColorIndex = 10 <SPAN style="color:#00007F">Then</SPAN>
                .Offset(1, -1).Value = .Value
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> cel
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[TCollin6]

Right now, you just acheived god level!! ray:

Thank you so very much for yoru help!!!!