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


New Member
Jan 25, 2005
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 :oops:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Does this work?

Set rng = ActiveCell.Offset(3,0)
Upvote 0
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.
Upvote 0
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

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
Upvote 0
That's quite a bit of Format criteria. Any way you could narrow down what it is you are looking for?
Upvote 0
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).
Upvote 0
And what range will you be running it on? Only one sheet? Only one workbook?
Upvote 0
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>
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back