Excel VBA Macro to Move Values to Negative Header Columns
Question details
Create an Excel VBA macro that moves positive cell values to the nearest column with a negative header.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has headers in the first row and values below them. Positive values should be added to a destination in the same row.
- Observed behavior
- The desired result is to choose the nearest negative-header column, use the lower header value when distances are equal, add the positive value to the destination, and set the source cell to zero.
Check that row 1 contains the headers and that the data to process begins in row 2. The macro treats distance as the number of columns between source and destination; it processes the active worksheet through the last used row and the last header column.
Run a VBA macro to move positive values by header distance
Use this macro when negative row 1 headers identify destination columns and equally distant destinations should be resolved by the lower header value.
The macro moves positive values only from columns that do not have a negative header. It leaves values in negative-header columns as destinations, even if they are positive.
An empty destination is treated as zero. If a destination contains text or an error, the corresponding source is left unchanged. The macro writes values directly, so formulas in affected source or destination cells are replaced.
Open the workbook in desktop Excel, select the worksheet to process, and open Developer > Visual Basic. In the editor, choose Insert > Module.
Paste this code into the new standard module: Sub MoveReds() Dim ws As Worksheet, lastCell As Range Dim lastRow As Long, lastCol As Long Dim r As Long, c As Long, d As Long, bestCol As Long Dim distance As Long, bestDistance As Long Dim bestHeader As Double, headerValue As Double Dim sourceValue As Variant, destinationValue As Variant Dim isDestination() As Boolean Set ws = ActiveSheet Set lastCell = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False) If lastCell Is Nothing Then Exit Sub lastRow = lastCell.Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column If lastRow < 2 Then Exit Sub ReDim isDestination(1 To lastCol) For c = 1 To lastCol If Not IsError(ws.Cells(1, c).Value2) Then If IsNumeric(ws.Cells(1, c).Value2) Then If CDbl(ws.Cells(1, c).Value2) < 0 Then isDestination(c) = True End If End If Next c For c = 1 To lastCol If Not isDestination(c) Then bestCol = 0 bestDistance = lastCol + 1 For d = 1 To lastCol If isDestination(d) Then distance = Abs(c - d) headerValue = CDbl(ws.Cells(1, d).Value2) If distance < bestDistance Then bestCol = d bestDistance = distance bestHeader = headerValue ElseIf distance = bestDistance Then If headerValue < bestHeader Then bestCol = d bestHeader = headerValue End If End If End If Next d If bestCol > 0 Then For r = 2 To lastRow sourceValue = ws.Cells(r, c).Value2 If Not IsError(sourceValue) Then If IsNumeric(sourceValue) And Not IsEmpty(sourceValue) Then If CDbl(sourceValue) > 0 Then destinationValue = ws.Cells(r, bestCol).Value2 If Not IsError(destinationValue) Then If IsEmpty(destinationValue) Then ws.Cells(r, bestCol).Value2 = CDbl(sourceValue) ws.Cells(r, c).Value2 = 0 ElseIf IsNumeric(destinationValue) Then ws.Cells(r, bestCol).Value2 = CDbl(destinationValue) + CDbl(sourceValue) ws.Cells(r, c).Value2 = 0 End If End If End If End If End If Next r End If End If Next c End Sub
Return to Excel, confirm the intended worksheet is active, and choose Developer > Macros. Select MoveReds and click Run.
Inspect a few destination cells and their source cells, including an equal-distance case. To keep the macro, use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm).

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files to review and edit your data as you migrate between office suites; it does not fix Excel VBA issues.

Frequently Asked Questions
How does the macro decide which negative header is nearest?
It counts the columns between each source and each negative-header destination. If two destinations are equally distant, it chooses the one with the lower header value, such as -2000 instead of -500.
What happens if a destination already contains a value?
The macro adds the positive source value to a numeric destination in the same row, then sets the source to zero. If the destination contains text or an error, it leaves the source unchanged.
Will the macro move positive values out of negative-header columns?
No. Columns with negative numeric headers are treated as destinations, not sources, so their positive values are not moved to another column.
Why do I need to save the workbook as XLSM?
Excel's XLSX format does not retain VBA macros. Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) to keep MoveReds in the workbook.




