Convert this loop to array

[mahmed1]

Hi

Hope you are well - is there any chance someone can help me convert this code from loop to an array as its taking so long to update

I have a sheet with multiple formulas therefore when looping through and pasting into sheet its taking ages so i just need to speed this code up by putting into an array and then pasting 1 time into the sheet
Hoping someone can help me please

Thank you

For i = 5 To Lrow

If Queue_Split_Sh.Cells(i, 13).Value = 1 Then

First_1 = Queue_Split_Sh.Cells(i, 11).Value + 13

Last_1 = Queue_Split_Sh.Cells(i, 12).Value + 13

A = 1

For c = First_1 To Last_1

Queue_Split_Sh.Cells(i, c).Value = A

A = A + 1

Next c

End If

Next i



Application.ScreenUpdating = True

MsgBox "Updated", vbOKOnly, "Updated"

 

[mahmed1]

Hey - any luck

The code works fine but too slow so was hoping someone can help me with an array solution to speed it up

 

[Scott Huish]

Hey - any luck

The code works fine but too slow so was hoping someone can help me with an array solution to speed it up

It would help if you could explain what it's supposed to do.

 

[mahmed1]

Hi apologies

i have a range of cells where in each row there could be consecutive 1s

What im trying to do is look at each row and check to see there are any consecutive 1s in there..if there is then replace those cells with sequence 1,2,3 etc

So for example

Say in the first row being 5 has all 0s then go to next Row

If the first row only has 1 once then go to next row

If it has say consecutive 1s ie 1,1,1,1 then replace with 1,2,3,4 (so replacing the 1s to numbers from 1 to the count of 1s in that sequence)

There wont be multiple sets of 1s in a row so can move to next row until last row

The loop works fine but it tries and pastes at every time it finds a consecutive 1

What i was hoping to do is store the location and values into an array and then paste into cell in 1 go at the end

Hopefully im making sense

Thats why i have a formula first_1 and Last_1 to give me position of the cells to loop through and add 1

 

[mahmed1]

The row data is from column N to AF so the match formula just get the position of first 1 and last 1 and then i have a condition to check to see if there is a consecutive 1 match but dont think thats the issue - i may be wrong but its this part i think is causing the issue

Queue_Split_Sh.Cells(i, c).Value = A
A = A + 1