Assign variables to multiple cells quickly

[gandhiad]

Hello,

I am relatively new to VBA and macros and would like to track changes in values of a certain column of a worksheet. I have the values in column F of Sheet 4 (which updates based on values in another sheet through a formula), and I want it to copy to the next available column to the right of it (column G then H etc etc) if the new value of the cell is different from the old value. Right now the code I have below does the copying part great, but it's doing it for all the cells regardless of if the value has changed or not. If I get rid of the for-loop and just have it work on a single row it does exactly what I want. But how do I make this apply to every row? I'm thinking all I have to do is assign a new variable to each row, but I need to learn how to go about doing that quickly. I have a hunch that "PrevVal_i" doesn't really work, so what should I use instead?

In the "This Workbook" page:

Private Sub Workbook_Open()

Dim i As Integer

For i = 2 To 1000
PrevVal_i = Sheet4.Range("F" & i).Value
Next i

End Sub


In the "Sheet4" code page:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False

Dim i As Integer

For i = 2 To 1000

If Cells(i, "F").Value <> PrevVal_i Then
Cells(i, Columns.Count).End(xlToLeft).Offset(0, 1) = Range("F" & i)
PrevVal_i = Range("F" & i)
End If
Next i
Application.ScreenUpdating = True

End Sub

Thanks!

[JoeFoot]

If I understand your problem correctly you need to update the Preval_i value at every iteration. And no need to have 2 subs for this, I think you can merge them. Try this:

Please Login or Register  to view this content.

if value is different, then it will add a new number to the next right column and then overwrite the existing value in Column F

[gandhiad]

Great! I realized that each row will not actually stay in its place when the spreadsheet updates through my formulas (e.g. the values in columns A through F for row 3 might be displayed now in row 7). I would nee the macro to follow this. I guess now it wouldnt work if I'm assigning variables to each row, is there a better way to do this then?

[JoeFoot]

correct, the macro will not follow. You would basically need to create another macro that would insert rows as needed or have the calculations embedded in the code rather than using formulas. You will need to provide more details on this, maybe an example sheet.

[gandhiad]

Ok, so I reworked the file so the rows will not change, I also changed some data so there's one formula in the odd rows and another in the even rows, returning different values. My code is as below:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False

Dim i As Integer, PrevVal_i As Integer

For i = 3 To 1000 Step 2

If Cells(i, "F").Value <> PrevVal_i Then

Cells(i - 1, Columns.Count).End(xlToLeft).Offset(0, 1) = Range("F" & i - 1)

Cells(i, Columns.Count).End(xlToLeft).Offset(0, 1) = Range("F" & i)

End If


Next i

Application.ScreenUpdating = True

End Sub

The code works great for copying both columns into the next adjacent two columns, but it does it once again for everyrow regardless of if the updated value stays the same or not.

Any suggestions?

[Arkadi]

Please wrap your pasted code in code tags for easier reading, copying, and formatting.
Forum Rule #3

[JoeFoot]

don't you need to reassess the PrevVal_i at each iteration of i? The current code has your PrevVal_i being unchanged for the entire loop of 3 to 1000. I thought PreVal_i was located in another sheet for which then you should have

Please Login or Register  to view this content.

[gandhiad]

Oh woops you are correct I forgot to copy the Workbook code:

Please Login or Register  to view this content.

[JoeFoot]

Allright, if that solves it just mark the thread as solved.

Best of luck