how do i tell a macro to begin in the current cell?
 

I want to use the search function to find specific data and then use a macro
to select the entire row and transpose it into a column and then print the
data.

The macro works but it selects the same row every time. I want to know how
to tell the macro to start in the current cell.

I use the record function to create the macro, so a simple solution would be
appreciated.

how do i tell a macro to begin in the current cell?
 

You can try creating a name;
While recording your macro go to INSERT-NAME-DEFINE- and place the cell
reference, make sure you do not use the $ Dollar sign, because it will fix
your cell reference.
Then record the rest of your macro.

"marwildfw" wrote:

I want to use the search function to find specific data and then use a macro
to select the entire row and transpose it into a column and then print the
data.

The macro works but it selects the same row every time. I want to know how
to tell the macro to start in the current cell.

I use the record function to create the macro, so a simple solution would be
appreciated.

how do i tell a macro to begin in the current cell?
 

Showing your macro would help a lot...

Absent that, try working with "ActiveCell" or "Selection"

--
Tim Williams
Palo Alto, CA


"marwildfw" wrote in message ...
I want to use the search function to find specific data and then use a macro
to select the entire row and transpose it into a column and then print the
data.

The macro works but it selects the same row every time. I want to know how
to tell the macro to start in the current cell.

I use the record function to create the macro, so a simple solution would be
appreciated.

how do i tell a macro to begin in the current cell?
 

Maybe something similar to this? You will need to change the Criteria to
what you are looking for. Also, review the parameters of the Find method. I
made assumptions about how you want to perform the search (refer to help in
VBA for the Find Method and its parameters). Also, you may need to change
the destination for the transposed data. I put it in Sheet2, cell A1.

Sub Test()
Const Criteria As String = "Joe"
Dim rngFound As Range

Set rngFound = Cells.Find(what:=criteria, _
after:=Cells(1, 1), LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, _
matchbyte:=False)

rngFound.EntireRow.Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlAll, _
Operation:=xlPasteSpecialOperationNone, skipblanks:=false, _
Transpose:=True
Application.CutCopyMode = False

Worksheets("Sheet2").PrintOut
End Sub

"marwildfw" wrote:

I want to use the search function to find specific data and then use a macro
to select the entire row and transpose it into a column and then print the
data.

The macro works but it selects the same row every time. I want to know how
to tell the macro to start in the current cell.

I use the record function to create the macro, so a simple solution would be
appreciated.

how do i tell a macro to begin in the current cell?
 

The "current cell" changes after each search because the critera is different
for each search. Naming would always point to the same cell. I found that
the "relative reference" button in the record macro box attaches the start of
the macro to the current cell.

Thanks for your help.



"Michael" wrote:

You can try creating a name;
While recording your macro go to INSERT-NAME-DEFINE- and place the cell
reference, make sure you do not use the $ Dollar sign, because it will fix
your cell reference.
Then record the rest of your macro.

"marwildfw" wrote:

I want to use the search function to find specific data and then use a macro
to select the entire row and transpose it into a column and then print the
data.

The macro works but it selects the same row every time. I want to know how
to tell the macro to start in the current cell.

I use the record function to create the macro, so a simple solution would be
appreciated.

how do i tell a macro to begin in the current cell?
 

When using the Record Macro function, the "Relative Reference" button applies
the Active Cell command to the macro and achieves the desired results. Sorry
about not showing the macro, this is my first post on here and I was unaware
of the process.

Thanks for the help.

"Tim Williams" wrote:

Showing your macro would help a lot...

Absent that, try working with "ActiveCell" or "Selection"

--
Tim Williams
Palo Alto, CA


"marwildfw" wrote in message ...
I want to use the search function to find specific data and then use a macro
to select the entire row and transpose it into a column and then print the
data.

The macro works but it selects the same row every time. I want to know how
to tell the macro to start in the current cell.

I use the record function to create the macro, so a simple solution would be
appreciated.

how do i tell a macro to begin in the current cell?
 

VBA is over my head. I use the cheat method and let the macro copy what I
want to do. Here is the macro I ended up with and it works! The search is
not part of the macro. I am searching through 30,000 addresses for a
specific one and then printing the results based on the cell the address is
found in.

ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Copy
Sheets("PRINT").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, -1).Range("A1:B44").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True
ActiveCell.Offset(0, 1).Range("A1:A44").Select
Selection.ClearContents
ActiveCell.Select
Sheets("LBE_Mailfile_May_2006_PRINT").Select
Application.Goto Reference:="R2C4"
End Sub


"JMB" wrote:

Maybe something similar to this? You will need to change the Criteria to
what you are looking for. Also, review the parameters of the Find method. I
made assumptions about how you want to perform the search (refer to help in
VBA for the Find Method and its parameters). Also, you may need to change
the destination for the transposed data. I put it in Sheet2, cell A1.

Sub Test()
Const Criteria As String = "Joe"
Dim rngFound As Range

Set rngFound = Cells.Find(what:=criteria, _
after:=Cells(1, 1), LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, _
matchbyte:=False)

rngFound.EntireRow.Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlAll, _
Operation:=xlPasteSpecialOperationNone, skipblanks:=false, _
Transpose:=True
Application.CutCopyMode = False

Worksheets("Sheet2").PrintOut
End Sub

"marwildfw" wrote:

I want to use the search function to find specific data and then use a macro
to select the entire row and transpose it into a column and then print the
data.

The macro works but it selects the same row every time. I want to know how
to tell the macro to start in the current cell.

I use the record function to create the macro, so a simple solution would be
appreciated.