Looping a function along columns

[Frey]

Hello everybody,

I am working on a very big excel sheet, which has more than 8000 columns. I managed to write a macro that copies a function on a column (skipping a protected cell).


Sub CopyToUnlocked()

Dim cell As Range, MyRange As Range, SelRange As Range

Set MyRange = Range("MY5:MY8539")

For Each cell In MyRange.Cells
If Not cell.Locked Then
If Not SelRange Is Nothing Then
Set SelRange = Union(cell, SelRange)
Else
Set SelRange = cell
End If
End If
Next cell

Range("MY4").Copy SelRange

End Sub




This macro is ok for me, however I would like to create a loop that repeats the function from colmumn MY to column LPJ keeping the row numbers constant.

Could someone help me? Thank you!

[mrice]

You are better to use an alternative syntax

Please Login or Register  to view this content.

[Frey]

Hello mrice, thank you for the help.

I tried to integrate my code in your code in the following way:

Sub CopyToUnlocked()
For X = 363 To 8538
Set MyRange = Range(Cells(5, X), Cells(8539, X))
Dim cell As Range, MyRange As Range, SelRange As Range

Set MyRange = Range("MY5:MY8539")

For Each cell In MyRange.Cells
If Not cell.Locked Then
If Not SelRange Is Nothing Then
Set SelRange = Union(cell, SelRange)
Else
Set SelRange = cell
End If
End If
Next cell

Range("MY4").Copy SelRange
Next X
End Sub

However, when I run it I receive the message "double declaration" and the words in red are selected. I understand that MyRange is defined as Range twice, so I canceled the red selected words and the code starts but it does not seem to work properly. Thank you for the help, my knowledge of visual basic is very basic ;-)

[Frey]

Maybe I made it a bit confusing, I thought to a new strategy.

To make it more simple, I am thinking now to copy the function contained in the cell MY4 and copy it down until there is protected cell, at that point the macro should stop copying along the column MY and start in cell MZ4 and repeating the same action until column LPJ, i.e. I would like to repeat this 8176 times.

Would it be possible to write this type of macro? I hope you understand I am a beginner in visual basic, thank you for the help.

[Frey]

Can someone help? Thank you..

[mrice]

You are correct to delete the Dim statement.

What do you mean by the statement 'the code starts but it does not seem to work properly'? Unexpected results, error messages, crash ?

Is is possible to post a workbook to that members of the Forum can see what the layout of your sheet is. I cut down version will be fine as long as it is typical of the real thing.

[Frey]

It actually works if I eliminate that sentence, but the colmuns show all the same values. This means that the function is copying the first column in the following ones and not the formula...

[Frey]

I think that probably I need something like this but that works only along columns

1. Sub Macro2()
2. Dim i As Long
3. Dim j As Long
4. i = 3
5. j = 3
6. Application.EnableEvents = False
7. With ThisWorkbook.Sheets(1)
8. For i = 3 To 13
9. .Range(.Cells(i, j), .Cells(i, 13)).Copy
10. .Cells(i + 1, j - 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
11. j = j + 1
12. Next
13. End With
14. Application.EnableEvents = True
End Sub

I add also an example of the file I am working on. I hope it could be useful. Thank you for the help!

[Frey]

Is there someone that can help with this code? Help!!