H
hce
dear all
I was wondering is it possible to add in a vb code to allow for
automatic range resizing? I was recording a macro for this particular
file (File A) and it worked well but when I run the macro in another
file (File B) with the same structure but the number of records are
different ie. File A has 10 records (rows) while File B has 20 records
(rows). In one of the columns, I need to input some data ie. AP and
this AP should be in all the rows. So in my macro, I typed "AP" into
the second row and in the desired column (first row is my column
headings) and after that, I double clicked on the cell box so that "AP"
is pasted onto the other rows that contains data (when double clicking
the box, it takes reference from the "neighbour" column). In this case,
the macro is only taking the range where the formula started and
stopped when I was recording the macro so if the next file has more
records, the macro would not be successful. I can specify a named range
but if the data is over this named range, then I would be in trouble as
the formula will stop at where I specified or if I have less records
that what i have specified, then i would have many "useless" records as
they would only contain "AP". Is there anyway you experts out there can
help me on how to create a code that can allow for automatic resizing
of the range just like what we can do in pivot table - using OFFSET. I
would really appreciate any assistance provided.
Cheers
I was wondering is it possible to add in a vb code to allow for
automatic range resizing? I was recording a macro for this particular
file (File A) and it worked well but when I run the macro in another
file (File B) with the same structure but the number of records are
different ie. File A has 10 records (rows) while File B has 20 records
(rows). In one of the columns, I need to input some data ie. AP and
this AP should be in all the rows. So in my macro, I typed "AP" into
the second row and in the desired column (first row is my column
headings) and after that, I double clicked on the cell box so that "AP"
is pasted onto the other rows that contains data (when double clicking
the box, it takes reference from the "neighbour" column). In this case,
the macro is only taking the range where the formula started and
stopped when I was recording the macro so if the next file has more
records, the macro would not be successful. I can specify a named range
but if the data is over this named range, then I would be in trouble as
the formula will stop at where I specified or if I have less records
that what i have specified, then i would have many "useless" records as
they would only contain "AP". Is there anyway you experts out there can
help me on how to create a code that can allow for automatic resizing
of the range just like what we can do in pivot table - using OFFSET. I
would really appreciate any assistance provided.
Cheers