I have a section of code that I've been attempting to clean up and hopefully speed up. The section of code where I pass values in order to generate a key and update a table on a different sheet I think is my biggest problem. However I would like any suggestions/modifications to clean this section up first.
An overview is my worksheet has a header section where the user will update the monthly sales,units or avg. cost field. Once this is done I need to recalculate all values in that month. This is anywhere from 10 to 1200 rows. As the update executes the following occurs:
- Cell is updated with the impact of the header change.
- A new unique key is built.
- The new key is found in a table and the change value is inserted.
I was stuck manually changing the "ColumnCount" variable to maintain the offset in the proper position. Any help would be great.
'Table update afet net units header update Dim i As Integer 'LastRow used to determine final used row on worksheet LastSourceRow = LastRow(destsheet, "B") 'Column Count initially set to -2 to offset target to desired cell ColumnCount = -2 RowCount = 1 'Runs from row 1 to the last row on the sheet For RowCount = 1 To LastSourceRow Step 5 i = 5 'After header change offset 5 rows and 2 columns back to capture cell change Set target = target.Offset(i, ColumnCount) 'Running Status Application.StatusBar = "Target value change : " & target.Row & " of " & LastSourceRow & " " & Format(target.Row / LastSourceRow, "Percent") 'If change cell is 0 then skip and sel column count to 0 to keep offset aligned If target = 0 Then 'Do Nothing if 0 value ColumnCount = 0 Else 'Generate unique key and pass to table update module modTableUpdate.TableUpdate target, destsheet, sourcesheet, ColumnHeader, ColumnFormula, Celladdress, Key, cell, result 'offset target by 1 to capture change ColumnCount = 1 'Set target offset to new cell Set target = target.Offset(, ColumnCount) 'Generate key and pass target offset value to table update module modTableUpdate.TableUpdate target, destsheet, sourcesheet, ColumnHeader, ColumnFormula, Celladdress, Key, cell, result 'Set target offset back to original cell ColumnCount = -1 Set target = target.Offset(, ColumnCount) 'Set column count to 0 to maintain alignment when loop repeats ColumnCount = 0 End If Next RowCount