How to specify a range using cell references?

**kandiyohiv** · 05-25-2021, 03:27 PM

In this mockup, the blue columns are text, the orange column is a formula.
excelforum1.jpg

I have working code that adds a new row to the bottom of a table, copies over some cells from a different tab, and then copies the formula from the bottom cell (D6) into the new row's formula cell (D7). I need this to be flexible, so I don't hard-code any cells, but use .Range(1,4) of the last row to specify my formula cell.

Please Login or Register  to view this content.

I now have multiple formula columns instead of just one (below I show 3 orange formula columns), so I now need to copy the formulas from D6:F6 into D7:F7.
excelforum3.jpg

I tried a few different ways of updating the "Set formulaCells" and "newRow.Range" statements using Range and Cells but none of my trials worked.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

How do I specify this range of cells without having to hard-code the cell names?

**LeoTaxi** · 05-25-2021, 03:57 PM

in a table you dont have to copy formulas, in your case, when you put data
in first 3 columns, columns with formulas get automatic populated.

Kind regards
Leo

**kandiyohiv** · 05-25-2021, 04:02 PM

Leo, I wish that were the case, but that's not what's happening. When I copy my data from another tab, it's just the 3 blue columns A:C. Columns D:F show up as #N/A after the copy operation (the formulas do not auto-fill). Which is why I'm having to find them from the previous row and paste them in.

**LeoTaxi** · 05-25-2021, 04:53 PM

Is it a table table, or is it range you call table ?

**LeoTaxi** · 05-25-2021, 04:54 PM

maybe show us the file

**kandiyohiv** · 05-25-2021, 05:18 PM

It's a table table (not a range). I discovered why my formula columns were filling with #N/A. It's because of how I populate the new row. I'm copying something 1row, 3columns wide and pasting it into something 1row, 6columns wide. So columns 4,5,6 (D:F) are filling with #N/A.

So I still need to know how to specify a range based on relative cell references. When I copy my source range (which is 1row by 3columns) I need to paste it into just the first 3 columns of my new row so that I don't fill the formula columns with #N/A.

I continue to try all sorts of different combinations of .Range(Cell1,Cell2) but I can't get anything to work.

**kandiyohiv** · 05-25-2021, 05:27 PM

Another way to ask would be:

The following will fill the first cell of my new row with "abc". This works.

Please Login or Register  to view this content.

How can I fill the first 3 columns of my new row with "abc". I can't get this to work (without doing it long-hand like this):

Please Login or Register  to view this content.

**LeoTaxi** · 05-25-2021, 05:31 PM

so dont copy row but only the 3 columns

**LeoTaxi** · 05-25-2021, 05:33 PM

Please Login or Register  to view this content.

**LeoTaxi** · 05-25-2021, 05:35 PM

or

Please Login or Register  to view this content.

**kandiyohiv** · 05-25-2021, 08:10 PM

.Resize solved my problem. Thanks! I ended up with the following for those specific problem lines.

Please Login or Register  to view this content.

How to specify a range using cell references?

LinkBack

Thread Tools

Rate This Thread

Display

How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Re: How to specify a range using cell references?

Thread Information

Users Browsing this Thread

Similar Threads

Copying range to another range using constructed cell references

Change cell references/text in whole marked cell range from K10 to $K$10 ?

[SOLVED] Have a Range reference and need to get two cell references.

Make a range out of two cell references

Range that references to cell for the Row

[SOLVED] How to rename references from range names to cell references

[SOLVED] Replace range names with cell references?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions