VBA Auto Sort when formula result changes

tahazameel

New Member
Joined
Oct 28, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm a beginner at VBA. I'm trying to create a function where I calculate the averages of some scores, and then transfer the scores to a 'sheet' adjacent to it.
In this sheet, I transferred over the Adjacent Ratings calculated in Row C to Row H simply by giving Row H formulas, such as selecting H2 and giving it the formula of =C12 etc to correspond to the category.

I then used this code to sort Out the AVERAGE RATINGS sheet by ascending order.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("F2:H4").Sort Key1:=Range("H2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

However, when I do this, and the ratings under Column H get sorted, I find that the formulas of each average rating doesn't match what I had originally input.

For example, I input the average rating formula for Ease of Finding Way Through Airport's as =C12 under column H.
However, when the ratings get sorted, I find that the average rating formula for Ease of Finding Way Through Airport automatically changes to something like =C13 or =C14 or =C12 instead. This in turn changes the value as well.

I apologise for my poor explanation. I'm not a native English speaker.
How do I solve this issue?




1603865207041.png
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

tahazameel

New Member
Joined
Oct 28, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
ENGLISH EDIT:
I'm a beginner at VBA. I'm trying to create a function where I calculate the averages of some scores, and then transfer the scores to a 'sheet' adjacent to it.
In this sheet, I transferred over the Adjacent Ratings calculated in Column C to Column H simply by giving Column H formulas, such as selecting H2 and giving it the formula of =C12 etc to correspond to the category.

I mixed up columns with rows. My bad!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,126
Messages
5,570,331
Members
412,319
Latest member
akshat1231
Top