Football lines

[speedracer7278]

I would like: If value in power cell are negative to convert them to positive and move up to Visiting team? Example move and change Atlanta Power 3 from -2 into 2 and move up to Carolina cell just above. Same for entire worksheet single function. Thanks for having this site. I have been off excel for 16 years and things have changed.

		Casino line	Power 1	Power2	Power 3
451​	Detroit
452​	Kansas City	4​	11.5​	7.5​	5​
453​	Carolina
454​	Atlanta	3.5​	9​	2.5​	-2​
455​	Houston
456​	Baltimore	10​	10​	7.5​	11​
457​	Cincinnati
458​	Cleveland	-2​	6​	1​	-7​
459​	Jacksonville
460​	Indianapolis	-5​	3​	0​	-13​
461​	Tampa Bay
462​	Minnesota	6​	3​	4.5​	7.5​
463​	Tennessee
464​	New Orleans	3.5​	7.5​	6.5​	8​

 

[Joe4]

Welcome to the Board!

So is this what you are looking for (updated values in red)?

Here is VBA code that will do that:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows and columns
    For r = 2 To lr
        For c = 3 To 6
'           If value is less than zero, copy postitive value to line above
            If Cells(r, c).Value < 0 Then
                Cells(r - 1, c).Value = 0 - Cells(r, c).Value
'               Uncomment following line if you want to remove negative
                'Cells(r, c).ClearContents
            End If
        Next c
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[speedracer7278]

Yes sir but delete the - 2 -7 -13

 

[Joe4]

I anticipated that. Did you see this part of my code?

'               Uncomment following line if you want to remove negative
                'Cells(r, c).ClearContents

Remove the single quote mark so it looks like this:

'               Uncomment following line if you want to remove negative
                Cells(r, c).ClearContents

and it should do what you want.

 

[speedracer7278]

Joe you are the man.

 

[Joe4]

Glad I was able to help!