Loop through cells in range and copy values to corresponding cell in another range 1

[chrisinparra]

I have two named ranges which each contain multiple areas.

read_area ='Read_Sheet'!$I$24:$I$33,'Read_Sheet'!$J$37,'Read_Sheet'!$C$40,'Read_Sheet'!$E$42
edit_area ='Edit_Sheet'!$V$54:$V$63,'Edit_Sheet'!$U$67,'Edit_Sheet'!$T$70,'Edit_Sheet'!$V$72

I'd like to copy the value from each cell in read_area to the corresponding cell in edit_area. Cells.Item(b) is not working. I expected Item(11) to be the 11th cell in the range edit_area i.e. 'Edit_Sheet'!$U$67, but it is not. It returns the 11th cell from the start, which is 'Edit_Sheet'!$V$64, which isn't even in edit_area. I have also tried the For each cell in edit_area method, but I can't figure out how to reference the corresponding cell in read_area.

Sub edit_form(read_area, edit_area)
Dim cell As Range

'copy values from read area to edit area
For b = 1 To Range(edit_area).Cells.Count

Range(edit_area).Cells.Item(b).Value = Range(read_area).Cells.Item(b).Value

Next cell

End Sub

 

[SkipVought]

Hi,

Untested from my iPad...

Dim i as Integer

For i = 1 To Range("read_area").Count
   Range("edit_area")(i) = Range("read_area")(i)
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[chrisinparra]

Same problem as Item.

Range("edit_area")(i) returns the 11th cell from the start, not the 11th cell in the range. Any other thoughts?

 

[SkipVought]

Yea, I don't recall using a split range as you have.

So where does the data from 11th cell from the start go when the coded assignment is executed?

Is there a hint there?

So 11th ought to be 'Read_Sheet'!$J$37 yes?

What cell is actually assigned?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[chrisinparra]

'Read_Sheet'!$I$34 is returned as the 11th cell. This isn't inside the range. It should be 'Read_Sheet'!$J$37 but it's not.

 

[SkipVought]

Are you certain that your split named range is properly defined in the Defined Names Manager?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

BTW, what's the reason for the discontiguous range?

Maybe an actual view of the sheets might help propose a solution.

Can you upload your workbook?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[chrisinparra]

Yes. Below code produces the below results. On the 11th pass it returns $J$37, but calling for Item(11) returns $I$34.

Dim cell_count As Integer

For Each cell In Range(read_area)
    cell_count = cell_count + 1
    Debug.Print cell_count & " | " & Range(read_area).Item(cell_count).Address & " | " & cell.Address
  
Next

1 | $I$24 | $I$24
2 | $I$25 | $I$25
3 | $I$26 | $I$26
4 | $I$27 | $I$27
5 | $I$28 | $I$28
6 | $I$29 | $I$29
7 | $I$30 | $I$30
8 | $I$31 | $I$31
9 | $I$32 | $I$32
10 | $I$33 | $I$33
11 | $I$34 | $J$37
12 | $I$35 | $C$40
13 | $I$36 | $E$42
14 | $I$37 | $E$44
15 | $I$38 | $I$44
16 | $I$39 | $C$47
17 | $I$40 | $E$49
18 | $I$41 | $I$49

 

[chrisinparra]

I can't upload the workbook as it includes sensitive information. Below is a screen shot of the read_area. This contains formulas in the grey boxes which will have results. On clicking the edit button, the idea is for these values to the copied to the green cells in the second screenshot. The user would the type over the values and hit the update button to submit the new record to the data table. read_area is the grey cells in the first image and edit_area is the green cells in the second image.

 

[SkipVought]

I'd lock all cells but those to be changed by the user, and then protect the sheet to enable the locking.

Then it's only a matter of...

1) Select the first cell in both sheets
2) COPY/TAB on sheet 1, PASTE/TAB on sheet 2 looping for the un-locked cell count.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[chrisinparra]

Thanks Skip,

I found a way to do it. I have defined three ranges:
- read_area for the whole read form
- edit_area for the whole edit form
- edit_area_values for just the green cells in the edit sheet

The code then loops through each cell of edit_area, and if it intersects with edit_area_values then copy the corresponding value from read_area and paste. This way Item is consistent between the read_area and edit_area as each only uses a single range. Thanks for your help.

'go each cell in edit_area, if cell is in values area and doesn't contain a formula then paste value from read_area
For b = 1 To Range(edit_area).Cells.Count

        If Intersect(Range(edit_area).Cells.Item(b), Range(edit_area & "_values")) Is Nothing Then
            'do nothing, edit cell is not in values range
        Else
            If Range(edit_area).Cells.Item(b).HasFormula = True Then
                'do nothing, edit cell contains a formula
            Else
                'paste value from read area into edit area
                Range(edit_area).Cells.Item(b).Value = Range(read_area).Cells.Item(b).Value
            End If
        End If
Next b

 

[combo]

Back to your initial problem: for multiple area range you have to refer to areas, otherwise vba assumes you refer to first area, with extending its real size if you refer to a cell outside. In your case:

[pre]Sub edit_form(read_area, edit_area)
' assumed read_area and edit_area have the same structure

'copy values from read area to edit area
For a = 1 To Range(edit_area).Areas.Count
For b = 1 to Range(edit_area).Areas(a).Cells.Count
Range(edit_area).Areas(a).Cells.Item(b).Value = Range(read_area).Areas(a).Cells.Item(b).Value
Next b
Next a ' How 'cell' here could work for you?

End Sub[/pre]

combo