VBA & Macro Problems

Excel VBA Macro to Move Values to Negative Header Columns

Adam DavisAdam Davis Nov 13, 2026 869 views

Question details

Create an Excel VBA macro that moves positive cell values to the nearest column with a negative header.

Move Values to Negative Header Columns with Excel VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

Open the workbook in desktop Excel, select the worksheet to process, and open Developer > Visual Basic. In the editor, choose Insert > Module.

2
Paste the macro

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

3
Run MoveReds

Return to Excel, confirm the intended worksheet is active, and choose Developer > Macros. Select MoveReds and click Run.

4
Check and save the results

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).

Run a VBA macro to move positive values by header distance
If no negative headers exist: The macro leaves the worksheet unchanged when it cannot find a negative numeric header in row 1.
Free Microsoft Office alternative

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.

Review negative headers and spreadsheet values in WPS SpreadsheetsCompatible with common Microsoft Excel formats, including XLSX and XLSFamiliar spreadsheet tools for an easier transition from ExcelFree, lightweight office suite
QA img-9

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.