macro question :)

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

hello, folks =) i have a question about macros.

i will explain what i'm trying/hoping to be able to do.

i want to write a macro that will search for a particular value in a cell.
when it finds that cell, i want to be able to copy a cell range with that
cell as the upper left and 10 columns and 6 rows below that cell. for
example, if i find the value in cell A6, i want to be able to select and
copy the range A6:J11. then, that will be pasted into a different worksheet
within that file. the last step would be to copy the cell that was found in
that search, and paste that elsewhere.

the problem i have is that i can easily record/write a macro to find and
select cells, but within the macro it always selects a specific cell range
instead of one relative to the search. for example, if i record a macro and
i find the value in cell A6, and i then select a certain cell range, it
always selects the range A6:J11 (the problem lies in the fact that when i
run the macro on a certain file, it may find a value in a cell other than
A6, but it will always copy the range A6:J11).

is there a way to record a macro such that i can find a value in a cell and
copy it relative to the search?

thanks, all!
scott :)
 
one more thing :) how would i also be able to paste to an absolute reference
in the same macro?

thanks :)
 
You cannot record a macro to do that and since you didn't copy/paste your
code here, try this idea
activecell.resize(6,10)
Look in vba HELP for RESIZE to find
Resize Property
See Also Applies To Example Specifics
Resizes the specified range. Returns a Range object that represents the
resized range.

expression.Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be
resized.

RowSize Optional Variant. The number of rows in the new range. If this
argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If
this argument is omitted, the number of columns in the range remains the
same.

Example
This example resizes the selection on Sheet1 to extend it by one row and one
column.

Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).SelectThis example assumes
that you have a table on Sheet1 that has a header row. The example selects
the table, without selecting the header row. The active cell must be
somewhere in the table before you run the example.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select--
Don Guillett
SalesAid Software
(e-mail address removed)
 
Back
Top