Macro to copy cell contents number of columns

[Pierre]

Am looking for a method to copy a cells contents a number of rows to
the right, that is defined by a number in an adjacent cell?
For instance:

In cell A1 is the number 5
In cell A2 is ABCD

I'd like ABCD to be copied to A7

If the value in cell A1 happens to be 4, the contents of A2 would be
copied to A6.

Its a head scratcher.

TIA for any thoughts.

Peter

 

[Norman Jones]

Hi Pierre,

Assuming that "rows to the right" is a typo and that your examples represent
your intentions, try:

Set rng = Range("A2")
rng.Offset(rng(0).Value).Value = rng.Value

 

[Tom Ogilvy]

Range(A2).Offset(Range("A1").Value,0).Value = Range("A2").Value

or for a general solution:

Dim rng as Range
Set rng = Range("A2")
rng.offset(rng.offset(-1,0).Value,0).Value = Rng.Value
I

 

[Pierre C]

Thanks Tom and Norman

I feel about this tall. . .
One more time:

The contents of B1 should be copied the number of columns to the right
as specified in A1.

There. Sorry once again.

Thanks for your help.

PC

 

[Pierre C]

You folks were right, it was a typo. I'd like to copy the data in A2,
"X" number of 'columns' to the right. "X" is a number entered in cell
A1.

If A1 is a 4, then the data appearing in A2 will also appear in A6. If
A1 is a 3, the data appearing in A2 aill also appear in A5.

Sorry for the typo, and thank you for the responses.

PC

 

[Tom Ogilvy]

Set rng = Range("A2")
rng.offset(0,rng.offset(-1,0).Value).Value = Rng.Value

Would offset the column.

You example is again about rows rather than columns, so if it is rows, the
see the original answer; If it is columns, then see this answer. If it
comes up one column short, then change it to

Set rng = Range("A2")
rng.offset(0,rng.offset(-1,0).Value+1).Value = Rng.Value

 

[Pierre]

Forgive the erroneous posts.

The macro isn't behaving as intended.


ie: The contents of 'column' B, would be copied X number of columns to
the right of column B. X is the value in the same row in column A.
This would apply to all entries in the entire column B. It may
contain up to 1000 items.

(Column A is a given level on the BOM. Column B is the item number.)
The intent is to create an indented bill of materials.

Thanks again for another look at it.

PC

 

[Tom Ogilvy]

Sub CreatedIndentedBOM()
Dim rng as Range, cell as Range
set rng = Range(Cells(2,2),Cells(rows.count,2).End(xlup))
for each cell in rng
if not isempty(cell) and cell.Offset(0,-1) > 0 then
cell.offset(0,cell.offset(0,-1).Value).Value = cell.value
End if
Next
End Sub

 

[Pierre]

Tom, thank you so much for your reply. 2 thoughts on it:
I modified the code to begin at a particular cell reference. The
modified code follows. It works just fine, however one thing about
this code, is that if the value in the first column on the left is a
"1" (column C), the macro will stop after that cell with a "type
mismatch" error and cease at that point. Any thoughts on how to have
it continue?
Thanks again.
(modified code follows with a starting point in cell C5):

Sub CreatedIndentedBOM()
Dim rng As Range, cell As Range
Set rng = Range(Cells(3, 5), Cells(Rows.Count, 3).End(xlUp))
For Each cell In rng
If Not IsEmpty(cell) And cell.Offset(0, -1) > 0 Then
cell.Offset(0, cell.Offset(0, -1).Value).Value = cell.Value
End If
Next
End Sub

 

[Tom Ogilvy]

Sub CreatedIndentedBOM()
Dim rng As Range, cell As Range
Set rng = Range(Cells(3, 5), Cells(Rows.Count, 3).End(xlUp))
For Each cell In rng
If Not IsEmpty(cell) And cell.Offset(0, -1) > 0 Then
If isnumeric(cell.offset(0,-1)) then
cell.Offset(0, clng(cell.Offset(0, -1).Value)).Value = cell.Value
End if
End If
Next
End Sub

Perhaps.

You are looping though

C3:E20 if 20 is the last row occupied in column C as an example

Is that what you want?

 

[Pierre]

Sub CreatedIndentedBOM()
Dim rng As Range, cell As Range
Set rng = Range(Cells(3, 5), Cells(Rows.Count, 3).End(xlUp))
For Each cell In rng
If Not IsEmpty(cell) And cell.Offset(0, -1) > 0 Then
If isnumeric(cell.offset(0,-1)) then
cell.Offset(0, clng(cell.Offset(0, -1).Value)).Value = cell.Value
End if
End If
Next
End Sub

Perhaps.

You are looping though

C3:E20 if 20 is the last row occupied in column C as an example

Is that what you want?

--
Regards,
Tom Ogilvy

<snip>

Tom, it works perfectly. You are a God!

PC
Thanks again.