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

TCollin6

New Member
Joined
Jan 25, 2005
Messages
11
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Does this work?

Code:
Set rng = ActiveCell.Offset(3,0)
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Jan 25, 2005
Messages
11
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

TCollin6

New Member
Joined
Jan 25, 2005
Messages
11
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Jan 25, 2005
Messages
11
Right now, you just acheived god level!! :pray:

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

Forum statistics

Threads
1,148,397
Messages
5,746,464
Members
424,021
Latest member
naimathulla

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 MrExcel.com.
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 "mrexcel.com".
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
Top